cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1950
Views
2
Helpful
7
Replies
Highlighted
Beginner

AXL query about hunt group,line groups and members

Hi all,

I need a query about  the hunt groups and  line groups  which have no directory numbers associated.  As an example,  think that

Hunt group A     Line Group A    Directory number 1

Hunt Group A    Line Group B    Directory number 1 and DN2

Hunt group B    Line Group C no member

Hunt group B  Line Group D   DN 3

In this case I need Hunt Group B line group C as no members associated, not the others in the query

Everyone's tags (6)
1 ACCEPTED SOLUTION

Accepted Solutions

Re: AXL query about hunt group,line groups and members

Maybe this will get us closer, this should list only LG with no members in the list

select first 500 n.dNOrPattern as HuntPilot, d.name as HuntList, d.description, lg.name as LineGroup ,tda.name as Algorithm,lgmap.lineSelectionOrder as Order, n1.dnorpattern as DN,d2.name as Station, d2.description as station_description

from device as d

inner join routelist as rl on rl.fkDevice=d.pkid

inner join DeviceNumPlanMap as dmap on dmap.fkDevice=d.pkid

inner join NumPlan as n on n.pkid=dmap.fkNumPlan

inner join linegroup lg on rl.fklinegroup = lg.pkid

left join typedistributealgorithm tda on lg.tkdistributealgorithm=tda.enum

left join linegroupnumplanmap lgmap on lgmap.fklinegroup=lg.pkid

left join numplan n1 on lgmap.fknumplan=n1.pkid

left join devicenumplanmap as dmap2 on dmap2.fknumplan=n1.pkid

left join device d2 on dmap2.fkdevice=d2.pkid

where n1.dnorpattern Is Null

order by dn ASC

7 REPLIES 7

Re: AXL query about hunt group,line groups and members

See if this helps.

select first 500 n.dNOrPattern as HuntPilot, d.name as HuntList, d.description,  lg.name as LineGroup  ,tda.name as Algorithm,lgmap.lineSelectionOrder as Order, n1.dnorpattern as DN,d2.name as Station, d2.description as station_description

from device as d

inner join routelist as rl on rl.fkDevice=d.pkid

inner join DeviceNumPlanMap as dmap on dmap.fkDevice=d.pkid

inner join NumPlan as n on n.pkid=dmap.fkNumPlan

inner join linegroup lg on rl.fklinegroup = lg.pkid

inner join typedistributealgorithm tda on lg.tkdistributealgorithm=tda.enum

inner join linegroupnumplanmap lgmap on lgmap.fklinegroup=lg.pkid

inner join numplan n1 on lgmap.fknumplan=n1.pkid

inner join devicenumplanmap as dmap2 on dmap2.fknumplan=n1.pkid

inner join device d2 on dmap2.fkdevice=d2.pkid

Where lg.name LIKE '%%'

order by huntpilot, order

Beginner

Re: AXL query about hunt group,line groups and members

Hi Brian The result is like this:

Your query gives the whole data..  How can we filter it in a query, The output should be such as hunt groups and number groups  without members? I need this because I want to schedule this and need only the groups without members.

   

80020553KADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_NGLongestIdleTime180020222SEP20BBC02043F5KADIKOY SUBESI-HAKAN YESILTEPE
80020553KADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_NGLongestIdleTime280020225SEP20BBC02044A8KADIKOY SUBESI-FILIZ ISIK
80020553KADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_HGKADIKOY_TICARI_MIY_NGLongestIdleTime380020230SEP20BBC0204AA2KADIKOY SUBESI-UGUR EMRE BAYKAL
80020554KADIKOY_BIREBIR_MIY_HGKADIKOY_BIREBIR_MIY_HGKADIKOY_BIREBIR_MIY_NGLongestIdleTime080020138TAB41654KADIKOY SUBESI-HATICE AKER
80020554KADIKOY_BIREBIR_MIY_HGKADIKOY_BIREBIR_MIY_HGKADIKOY_BIREBIR_MIY_NGLongestIdleTime080020138SEP20BBC02049C9
80020555KADIKOY SUBESI_OPRTOR_HGKADIKOY SUBESI_OPRTOR_HGKADIKOY SUBESI_OP_NGLongestIdleTime080020156CSF27563KADIKOY SUBESI-OGUZHAN DELIKTAS
80020555KADIKOY SUBESI_OPRTOR_HGKADIKOY SUBESI_OPRTOR_HGKADIKOY SUBESI_OP_NGLongestIdleTime080020156SEP20BBC02043C6KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556KADIKOY_OPERASYON_HGKADIKOY_OPERASYON_HGKADIKOY_OPERASYON_NGLongestIdleTime080020156CSF27563KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556KADIKOY_OPERASYON_HGKADIKOY_OPERASYON_HGKADIKOY_OPERASYON_NGLongestIdleTime080020156SEP20BBC02043C6KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556KADIKOY_OPERASYON_HGKADIKOY_OPERASYON_HGKADIKOY_OPERASYON_NGLongestIdleTime180020510SEP2401C73D5CA8KADIKOY SUBESI-MUSTAFA TUFAN
80020561KADIKOY_KREDI_IZLEME_HGKADIKOY_KREDI_IZLEME_HGKADIKOY_KREDI_IZLEME_NGLongestIdleTime080020791SEP20BBC0204DB2KADIKOY SUBESI MELIKE KARATAS
80020562KADIKOY_KREDI_TAHSIS_HGKADIKOY_KREDI_TAHSIS_HGKADIKOY_KREDI_TAHSIS_NGLongestIdleTime080020789SEP20BBC0204912KADIKOY SUBESI GULISTAN ARI
80020562KADIKOY_KREDI_TAHSIS_HGKADIKOY_KREDI_TAHSIS_HGKADIKOY_KREDI_TAHSIS_NGLongestIdleTime180020792SEP20BBC0204DD7KADIKOY SUBESI ALPASLAN CEBI
80023551KEMERALTI_BIREYSEL_MIY_NGKEMERALTI_BIREYSEL_MIY_NGKEMERALTI_BIREYSEL_MIY_NGLongestIdleTime080023137SEP20BBC021341CKEMERALTI SUBESI-KADIR KURTULUS KARAKAYA

Re: AXL query about hunt group,line groups and members

Not sure I understand all your parameters but maybe this will work.

select first 500 n.dNOrPattern as HuntPilot, d.name as HuntList, d.description, lg.name as LineGroup ,tda.name as Algorithm

from device as d

inner join routelist as rl on rl.fkDevice=d.pkid

inner join DeviceNumPlanMap as dmap on dmap.fkDevice=d.pkid

inner join NumPlan as n on n.pkid=dmap.fkNumPlan

inner join linegroup lg on rl.fklinegroup = lg.pkid

inner join typedistributealgorithm tda on lg.tkdistributealgorithm=tda.enum

inner join linegroupnumplanmap lgmap on lgmap.fklinegroup=lg.pkid

Where lg.name LIKE '%%'

order by huntpilot

Beginner

Re: AXL query about hunt group,line groups and members

Hi Brian,

I tried that query. It gives some hunt groups, number groups as a list . I checked the line groups on CUCM  and unfortunately it has members on these line groups.

Re: AXL query about hunt group,line groups and members

Maybe this will get us closer, this should list only LG with no members in the list

select first 500 n.dNOrPattern as HuntPilot, d.name as HuntList, d.description, lg.name as LineGroup ,tda.name as Algorithm,lgmap.lineSelectionOrder as Order, n1.dnorpattern as DN,d2.name as Station, d2.description as station_description

from device as d

inner join routelist as rl on rl.fkDevice=d.pkid

inner join DeviceNumPlanMap as dmap on dmap.fkDevice=d.pkid

inner join NumPlan as n on n.pkid=dmap.fkNumPlan

inner join linegroup lg on rl.fklinegroup = lg.pkid

left join typedistributealgorithm tda on lg.tkdistributealgorithm=tda.enum

left join linegroupnumplanmap lgmap on lgmap.fklinegroup=lg.pkid

left join numplan n1 on lgmap.fknumplan=n1.pkid

left join devicenumplanmap as dmap2 on dmap2.fknumplan=n1.pkid

left join device d2 on dmap2.fkdevice=d2.pkid

where n1.dnorpattern Is Null

order by dn ASC

Beginner

Re: AXL query about hunt group,line groups and members

Hi Brian thanks for the query...

What is the easiest way to run this query automatically and save it to  location ( or send mail via smtp) ?

That is, how can I schedule to send the output to someone or location?

Cisco Employee

Re: AXL query about hunt group,line groups and members

I believe you will need to look into creating an external application (or a script triggered by a cron job) to use the AXL API for something like that:

https://developer.cisco.com/site/axl/overview/

See the AXL <executeSqlQuery> request, which allows free-form SQL to be submitted to CUCM (response is in XML format).  Your app/script would need to periodically execute this request, parse the response, and forward the notification (e.g. via email.)

CreatePlease to create content
Content for Community-Ad
July's Community Spotlight Awards