Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

How to show logged-in Line Group Members in a Hunt Pilot (CUCM V7.1.3)

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?

20 REPLIES
Hall of Fame Super Red

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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

New Member

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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.

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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:

  • Hunt Lists are considered devices and they are stored in the Device table.
  • Hunt Pilots are patterns and are stored in the NumPlan table
  • You can reference different instances of a table (e.g. Device) in a SQL query but it can get pretty hairy and you have to use caution

Hopefully this is somewhat helpful.


Regards,
Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Hall of Fame Super Red

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

Hey Bill,

Really excellent post here!

+5 for this great info

Cheers!

Rob

New Member

Hello there,

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?

New Member

Hi Matthias,

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

New Member

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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:

New Member

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

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.

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Hi William, Old post, but it

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

 

New Member

Great post, thanks!  works

Great post, thanks!  works perfectly

New Member

Thanks for this very useful

Thanks for this very useful information great post. +5

New Member

Hi Guys

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

New Member

Hi Rynard,

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

New Member

Thanks for the reply ,i will

Thanks for the reply ,i will have to check if the number is shared although i doubt it ,as the numbers are provisioned to the CUCM via a frontend portal that provisions all devices the same and the norm is to put a "*" in front of the device directory number. It is CUCM 10.5.

Also i did not alter the SQL query ,i just didn`t copy all output here

New Member

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

.

Re: How to show logged-in Line Group Members in a Hunt Pilot (CU

WOW.  When I see a post like this, I think it deserves solid ratings.

+5 to Bill for always going above and beyond.

If you have not done so, please rate helpful posts!

Silver

How to show logged-in Line Group Members in a Hunt Pilot (CUCM V

Great post!

Thanks!!

Jan

New Member

How to show logged-in Line Group Members in a Hunt Pilot (CUCM V

Great info mate. Thanks fo sharing this.

New Member

Years later and this post is

Years later and this post is still really helpful and informative.

7602
Views
200
Helpful
20
Replies
CreatePlease to create content