11-09-2010 05:52 AM - edited 03-16-2019 01:49 AM
I have configured a Hunt Pilot with a Hunt List which points to a Line Group with some DNs as Line Group Members. Additionally i gave the affected Users the option to log-in or log-out from the Hunt Pilot by configuring the "Hunt Group Logout" Button in the corresponding Phone Button Template.
Is there a way to find out who is logged-in or logged-out from the Hunt Pilot?
11-09-2010 06:01 AM
Hi Robert,
Sadly, there is no report that will show you this information. This is
one of the reasons people move to the UCCX application if this type
of info becomes a KPI type requirement
Cheers!
Rob
11-09-2010 06:18 AM
Hi Rob,
thanks for your quickly answer. Unfortunately, this is not the answer i expected, but CUCM works a designed and i am not able to install UCCX.
11-09-2010 07:54 AM
Robert,
I am not sure how useful this would be to you, but I have some additional tidbits of info that may or may not help. At a minimum, it may explain the inner workings of the CUCM hunt list. From the db perspective, the following tables store information related to line groups and hunt lists.
LineGroup This table defines the line group name, hunt algorithms, etc.
LineGroupNumPlanMap This will map a line group to a specific numplan entry, such as a phone line directory number.
NumPlan This is the master table for all digit patterns configured in your CUCM cluster. This includes phone lines, Hunt Pilots, translations, route patterns, etc. For your purposes you need this value to get to identify a device and you may want to use it for display purposes.
DeviceNumPlanMap This is another mapping table. It will match up phone line directory numbers in NumPlan to the IP phones in the Device table.
Device This table will contain devices like the IP phone. Since users log into/out of line groups from a device level (instead of the line level), you will need to know which phone you are dealing with. Note, that Hunt Lists are also stored in the Device table.
DeviceHlogDynamic This table simply keeps trag of whether a device has the "HLOG" status toggled. True means that the phone lines are logged into all line groups. False means that the lines are logged out of line groups. I use the term "phone lines" here simply because when a phone use toggles HLog to false, they are logged out of all line groups and this includes all lines on the phone.
So, what can we do this info? We can build a query to pull the status information you seek. Starting with a basic UI on the CUCM ssh console, you can build a query like this:
select lg.name as LineGroup,n.dnorpattern,dhd.hlog
from linegroup as lg
inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid
inner join numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid
inner join device as d on dmap.fkdevice=d.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid
order by lg.name
Yeah, it is ugly but it works. Essentially we are going to display the Line Group name (e.g. "Roberts LG"), directory numbers for all LG members, and whether the HLog status for the device that owns the directory number. It may be worthwhile to display the device name (d.name in our example) just in case you have shared line appearances. You could also display device description if that makes it easier to identify a device (d.description in our example).
If we were to run this from the admin interface you may have something like this:
admin:run sql select lg.name as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name
linegroup dnorpattern hlog
================== =========== ====
DC_Operators_LG 2025553299 t
DC_Operators_LG 2025553801 t
DC_Operators_LG 2025553802 t
DC_Operators_LG 2025553803 t
DC_Operators_LG 2025550207 f
VM_CUC-A-PUB_LG 1091001 t
VM_CUC-A-PUB_LG 1091002 t
VM_CUC-B-Pub_LG 1092001 t
VM_CUC-B-Pub_LG 1092002 t
admin:
You'll notice that 2025550207 is logged out of all hunt groups. More accurately, the device that owns 2025550207 is logged out of the hunt group.
This approach of using the command line on the CUCM is not optimal. But, using the AXL-SOAP API and a small web application, you could actually create a tool that you could use from a PC browser or the phone display. I know, what a pain, but you asked if it were possible. I think that the data is there, you just have to figure out how you want to grab and display.
Some other considerations:
Hopefully this is somewhat helpful.
Regards,
Bill
Please remember to rate helpful responses and identify
11-09-2010 08:42 AM
Hey Bill,
Really excellent post here!
+5 for this great info
Cheers!
Rob
02-04-2016 06:42 AM
Hello there,
I try to get the Line Group out via the AXL SQL API.
I want to specify the device name (SEP00000) and get the Line Group.
Can someone help me with the SQL string?
07-07-2016 04:36 AM
Hi Matthias,
The below request will give you the Line Group name associated to the phone having a name starting with SEP00000 if it has any and if the phone is logged into hunt group.
select lg.name as LineGroupName, n.dnorpattern as LineMember, n.description as LineDescription,dhd.hlog as LoggedIn, d.name as HardPhoneName, dmap.numplanindex as DeviceLineOrder, lgmap.lineselectionorder as LineSelectionOrder, 'Phone' as PhoneOrUDP FROM linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid left join extensionmobilitydynamic as emd on emd.fkdevice_currentloginprofile=dmap.pkid left join device as dp on emd.fkdevice_currentloginprofile=dp.pkid join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid WHERE d.name LIKE 'SEP00000%' UNION SELECT lg.name as LineGroupName, np.dnorpattern as LineMember, np.description as LineDescription, dhd.hlog as LoggedIn, d.name as HardPhoneName, dnpm.numplanindex as DeviceLineOrder, lgmap.lineselectionorder as LineSelectionOrder , 'UDP' as PhoneOrUDP FROM extensionmobilitydynamic emd LEFT JOIN device d ON d.pkid=emd.fkdevice JOIN devicenumplanmap dnpm ON dnpm.fkdevice = emd.fkdevice_currentloginprofile JOIN numplan np ON np.pkid=dnpm.fknumplan JOIN linegroupnumplanmap lgmap ON lgmap.fknumplan = np.pkid JOIN linegroup lg ON lg.pkid = lgmap.fklinegroup LEFT join devicehlogdynamic dhd on d.pkid=dhd.fkdevice WHERE d.name LIKE 'SEP00000%' ORDER BY lg.name, lgmap.lineselectionorder
br
11-10-2010 12:57 AM
Hi Bill,
thanks for your very interesting hint .
I run the query you posted and actually got the following output. But the displayed linegroups are only a subset from my configured 79 linegroups . Is there a possibility to display all linegroups with all corresponding DNs and can i display this information for only one linegroup?
When i know the queery that satisfy my claims, i will write a small web application that uses the AXL-SOAP API.
Regards, Robert
admin:run sql select lg.name as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name
linegroup dnorpattern hlog
============================== =============== ====
LG_A-Ulr4_Augsburg_9965077_235 \+498215075234 f
LG_A-Ulr4_Augsburg_9965077_235 \+498215075209 f
LG_A-Ulr4_Augsburg_9965077_235 \+498215075224 f
LG_A-Ulr4_Augsburg_9965077_235 \+498215075226 f
LG_A-Ulr4_Augsburg_9965077_235 \+498215075227 f
LG_A-Ulr4_Augsburg_9965079_300 \+498215075327 f
LG_A-Ulr4_Augsburg_9965079_300 \+498215075306 f
LG_AB-Fried17_9965006 \+496021391713 f
LG_AB-Fried17_9965006 \+496021391714 f
LG_AB-Fried17_9965006 \+496021391721 f
LG_AB-Fried17_9965006 \+496021391727 f
LG_AM-Mar9_9965004 \+499621474921 f
LG_BT-Sch9_9965010 \+4992189423 f
LG_DD-Fet29_9965014 \+493514459055 t
LG_HO-Bah1_9965020 \+4992818194122 f
LG_KE-Moz31_9965024 \+498315215110 f
LG_LA-Dre11_9965025 \+498714308419 f
LG_LA-Dre12_9965026 \+498719239113 f
LG_Mue-Sta41_9965029 \+498631386227 f
LG_N-KOEN11_9965034 \+4991124039112 f
LG_N-KOEN11_9965034 \+4991124039142 f
LG_N-KOEN11_9965034 \+4991124039110 f
LG_N-Ste6_9965057_400 \+499112428403 f
LG_N-Ste6_9965058_450 \+499112428455 f
LG_NES-Sie2_9965008 \+499771610413 f
LG_NES-Sie2_9965008 \+499771610421 f
LG_NM-Bah12_9965030 \+499181293312 f
LG_PA-Kle13_9965035 \+498519594109 f
LG_PA-Kle13_9965035 \+498519594113 f
LG_PAN-Drb12_9965036 \+498561961225 t
LG_PAN-Drb12_9965036 \+498561961224 f
LG_R-Her2_9965068_400 \+499413783414 f
LG_TS-Bah26_9965040 \+498619887312 f
LG_Voicemail 997005 t
LG_Voicemail 997006 t
LG_Voicemail 997007 t
LG_Voicemail 997008 t
LG_Voicemail 997009 t
LG_Voicemail 997010 t
LG_Voicemail 997011 t
LG_Voicemail 997012 t
LG_Voicemail 997013 t
LG_Voicemail 997014 t
LG_Voicemail 997015 t
LG_Voicemail 997016 t
LG_Voicemail 997017 t
LG_Voicemail 997018 t
LG_Voicemail 997019 t
LG_Voicemail 997020 t
LG_Voicemail 997021 t
LG_Voicemail 997022 t
LG_Voicemail 997023 t
LG_Voicemail 997024 t
LG_Voicemail 997025 t
LG_Voicemail 997026 t
LG_Voicemail 997027 t
LG_Voicemail 997028 t
LG_WEN-Buer16_9965041 \+499614820413 t
LG_WEN-Buer16_9965041 \+499614820415 f
LG_WM-Puet35_9965042 \+49881922927 f
admin:
11-10-2010 05:35 AM
Hi Bill,
i think the problem with the missing linegroups in the sql query is, that some DNs are associated with extension mobility device profiles and not directly on the device. I tried to find out the right Table in the CUCM Database Dictionary, but i don't have enough practial experience to create the query.
Please, can you post a sql query where device profile are considered .
Thanks, Robert
11-11-2010 01:41 PM
Robert,
You are correct. The devicehlogdynamic is still tied to the actual physical IP phone and not the EM profile. An EM profile would inherit the HLog status of the device it logs into (something to keep in mind). The EM device profile is also a record in the device table. However, you will never see the EM device show up in the devicehlogdynamic mapping table.
You would need to get the information by way of the extensionmobilitydynamic table. This can get tricky because you would need to join the device table twice. For example, the following will display phones that have a EM profile logged in:
admin:run sql select d.name as DeviceName, d.description, dd.name as ProfileName from device as d inner join extensionmobilitydynamic as emd on emd.fkdevice = d.pkid inner join device as dd on emd.fkdevice_currentloginprofile = dd.pkid
devicename description profilename
=============== ============ ===================
SEP00258418EE10 Betty Rubble Test Device Profile
Notice that we establish a join with the device table twice. The first time is to get the physical device information and the second time to get the extension mobility device profile information. This can get hairy. The following query is functional. I have not looked at it close enough to see if it can be optimized.
run sql select lg.name as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid inner join device as dp on dmap.fkdevice=dp.pkid inner join extensionmobilitydynamic as emd on emd.fkdevice_currentloginprofile = dp.pkid inner join device as d on emd.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name
It is a beast for sure. Keep in mind that these are just ideas we are discussing here. Running queries on the command line as part of normal operations is not recommended. If you need a regular report (on demand or other) then you will want to think about leveraging AXL/SOAP from a web application server or something similar.
HTH.
Regards,
Bill
Please remember to rate helpful posts.
Please remember to rate helpful responses and identify
08-28-2014 07:05 AM
Hi William,
Old post, but it may help somebody else. A little trick to get the extension mobility logged in users and hard phones in the same request is the UNION sql keyword.
So, to answer Roberts's needs, the sql request could be :
run sql select lg.name as LineGroupName, n.dnorpattern as LineMember, n.description as LineDescription,dhd.hlog as LoggedIn, d.name as HardPhoneName, dmap.numplanindex as DeviceLineOrder, lgmap.lineselectionorder as LineSelectionOrder, 'Phone' as PhoneOrUDP FROM linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid left join extensionmobilitydynamic as emd on emd.fkdevice_currentloginprofile=dmap.pkid left join device as dp on emd.fkdevice_currentloginprofile=dp.pkid join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid WHERE lg.name='XXXXX' UNION SELECT lg.name as LineGroupName, np.dnorpattern as LineMember, np.description as LineDescription, dhd.hlog as LoggedIn, d.name as HardPhoneName, dnpm.numplanindex as DeviceLineOrder, lgmap.lineselectionorder as LineSelectionOrder , 'UDP' as PhoneOrUDP FROM extensionmobilitydynamic emd LEFT JOIN device d ON d.pkid=emd.fkdevice JOIN devicenumplanmap dnpm ON dnpm.fkdevice = emd.fkdevice_currentloginprofile JOIN numplan np ON np.pkid=dnpm.fknumplan JOIN linegroupnumplanmap lgmap ON lgmap.fknumplan = np.pkid JOIN linegroup lg ON lg.pkid = lgmap.fklinegroup LEFT join devicehlogdynamic dhd on d.pkid=dhd.fkdevice WHERE lg.name='XXXXX' ORDER BY lg.name, lgmap.lineselectionorder
Clean the request if needed. Also notice that the User Device Profiles not logged on any phone (as extension mobility) do not appear in the request result.
Hope this helps.
Regards
07-22-2015 02:58 AM
Great post, thanks! works perfectly
08-13-2015 09:48 AM
Thanks for this very useful information great post. +5
06-29-2016 05:54 AM
Hi Guys
Sorry for reviving this very old thread ,just wanted to check if someone could help me with some info here. Firstly thanks for the SQL query ,it works perfectly
Just a question i had ,when i run this query and i filter out the correct Line group i am looking for ,the 2 Line group members show up twice in the table , see below :
AA_LG_312757400_1 872875449 t
AA_LG_312757400_1 872875449 t
AA_LG_312757400_1 872875452 t
AA_LG_312757400_1 872875452 t
Any ideas why this would be ? Currently i have an issue where i change the Distribution algorithm to Broadcast and somehow the following day it stops broadcasting , if i then reset the Line group it works again for a day ,could the above duplication have anything to do with it ?
Regards
07-07-2016 04:47 AM
Hi Rynard,
Duplicate rows : Please double check that directory numbers are not shared between the device profile and the phone. The table is also not containing all the columns which the request should return. Have you modified the SQL request structure ?
Distribution algorithm : No idea... If a number is shared between the phone and the device profile, it could be the issue. What's you CUCM version ?
br
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide