AXL query about hunt group,line groups and members

[cevliyaoglu]

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

[brian.larochelle]

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

[cevliyaoglu]

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.

80020553	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_NG	Longest	Idle	Time	1	80020222	SEP20BBC02043F5	KADIKOY SUBESI-HAKAN YESILTEPE
80020553	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_NG	Longest	Idle	Time	2	80020225	SEP20BBC02044A8	KADIKOY SUBESI-FILIZ ISIK
80020553	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_HG	KADIKOY_TICARI_MIY_NG	Longest	Idle	Time	3	80020230	SEP20BBC0204AA2	KADIKOY SUBESI-UGUR EMRE BAYKAL
80020554	KADIKOY_BIREBIR_MIY_HG	KADIKOY_BIREBIR_MIY_HG	KADIKOY_BIREBIR_MIY_NG	Longest	Idle	Time	0	80020138	TAB41654	KADIKOY SUBESI-HATICE AKER
80020554	KADIKOY_BIREBIR_MIY_HG	KADIKOY_BIREBIR_MIY_HG	KADIKOY_BIREBIR_MIY_NG	Longest	Idle	Time	0	80020138	SEP20BBC02049C9
80020555	KADIKOY SUBESI_OPRTOR_HG	KADIKOY SUBESI_OPRTOR_HG	KADIKOY SUBESI_OP_NG	Longest	Idle	Time	0	80020156	CSF27563	KADIKOY SUBESI-OGUZHAN DELIKTAS
80020555	KADIKOY SUBESI_OPRTOR_HG	KADIKOY SUBESI_OPRTOR_HG	KADIKOY SUBESI_OP_NG	Longest	Idle	Time	0	80020156	SEP20BBC02043C6	KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_NG	Longest	Idle	Time	0	80020156	CSF27563	KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_NG	Longest	Idle	Time	0	80020156	SEP20BBC02043C6	KADIKOY SUBESI-OGUZHAN DELIKTAS
80020556	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_HG	KADIKOY_OPERASYON_NG	Longest	Idle	Time	1	80020510	SEP2401C73D5CA8	KADIKOY SUBESI-MUSTAFA TUFAN
80020561	KADIKOY_KREDI_IZLEME_HG	KADIKOY_KREDI_IZLEME_HG	KADIKOY_KREDI_IZLEME_NG	Longest	Idle	Time	0	80020791	SEP20BBC0204DB2	KADIKOY SUBESI MELIKE KARATAS
80020562	KADIKOY_KREDI_TAHSIS_HG	KADIKOY_KREDI_TAHSIS_HG	KADIKOY_KREDI_TAHSIS_NG	Longest	Idle	Time	0	80020789	SEP20BBC0204912	KADIKOY SUBESI GULISTAN ARI
80020562	KADIKOY_KREDI_TAHSIS_HG	KADIKOY_KREDI_TAHSIS_HG	KADIKOY_KREDI_TAHSIS_NG	Longest	Idle	Time	1	80020792	SEP20BBC0204DD7	KADIKOY SUBESI ALPASLAN CEBI
80023551	KEMERALTI_BIREYSEL_MIY_NG	KEMERALTI_BIREYSEL_MIY_NG	KEMERALTI_BIREYSEL_MIY_NG	Longest	Idle	Time	0	80023137	SEP20BBC021341C	KEMERALTI SUBESI-KADIR KURTULUS KARAKAYA

[brian.larochelle]

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

[cevliyaoglu]

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.

[brian.larochelle · Accepted solution]

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

[cevliyaoglu]

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?

[dstaudt]

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.)

[derekcribbs]

I know this is an old thread, but I have been asked to find a way to list all calls currently in the queue for a given Hunt Group/Line Group, any ideas?

[dstaudt]

This is possible using the TAPI or JTAPI real-time call control APIs, e.g.: JTAPI Features - Hunt List 

However, the mechanism is a bit indirect: you cannot monitor the Hunt List address directly, but rather the Hunt List members.  By observing (at least) one of the Hunt members you get events for any incoming/ringing calls on the Hunt Pilot.