CUCM 10.5 - SQL to find user device profiles set as logged out profiles

[jon-lewis]

Hello everyone. I look after a CUCM 10.5(1) cluster with 17,000 users and 15,000 phones. Pretty much all devices are EM enabled, and users log in to make and receive their calls. The EM service has autologout enabled after 12 hours.

From analysing CDR records, some of the operations guys have come up with a list end user accounts that haven't made or received calls in over 90 days, and want to remove them from CUCM.

The idea is that we'll change the login PIN codes for the people on that list and wait a month. Anyone on that list who doesn't complain they can't login, really isn't using their VoIP account and we can delete them from CUCM (and they probably won't notice).

However, we have a number of accounts, senior staff mostly, where the UDP is set as a Phone log out profile. Changing the PIN codes on those won't have any effect as they don't login each day. However, I'm not having much luck finding a way to quickly identify those UDP used this way, and don't fancy checking each phone individually.

So I'd like to ask the community for their help, to find the SQL command I can run from CUCM CLI, to list all the User Device Profiles on the CUCM that are set as Phone log out profiles.

Apologies if this covered in another post, I did a few searches but didn't find anything.

Thanks in advance

Jonathan

[Maren Mahoney · Accepted solution]

I don't know of a way to ask CUCM if a profile is used as a logout profile, but you can ask CUCM for all of the logout profiles used on all phones via the "Generate Phone Reports" BAT utility. If you set it up as below, you should get a list of all of the phones in your cluster and any phone that has a logout profile assigned will show that information.

I'm sure there is an SQL call that can do the same, and hopefully one of the programming-savvy folks can help with that.

[Marcelo Morais]

Hi @jon-lewis,

 take a look at the following link: List Device Profiles.

Hope this helps,

 Marcelo Morais

[jon-lewis]

Thanks for your suggestions guys, I really appreciate that you took the time to help.

While not a SQL solution, I found the BAT report worked best for me.

[Randall White · Accepted solution]

Here is a SQL query you can run from the CLI, it lists the phones, and their default UDP profile (if any):

run sql SELECT a.name AS phone_name, b.name AS udp_name, b.description AS udp_description FROM device a, device b WHERE a.ikdevice_defaultprofile=b.pkid

Output looks like:

phone_name           udp_name udp_description    
=============== ======== ==================
SEPB000B4BB47E8 Test-UDP My Test EM Profile

[jon-lewis]

Awesome! Thanks

[Joel Jones]

@Randall White

Thanks for your response, it helped us find some issues. This answers how to find devices themselves that have this field set, but how would I find Device Profiles set? See below.

Using SQL (not exporting all user devices and sorting the Login User ID field"

How would you find a Device Profile that has the "Logged Out (Default) Profile Information" field set to someones user ID.

OR in reverse

How would you find any users that have that field associated?

[Randall White]

Try this:

run sql SELECT device.name, device.description, enduser.userid FROM device INNER JOIN enduser ON device.fkenduser=enduser.pkid AND device.tkClass=254

This should list the UDP Profiles, UPD Description, and User ID for all UDP Profiles that have an ID assigned. The User ID is listed under the fkenduser column in the devices table. This is either NULL (login userid = <none>) or a user-unfriendly string like 77a7aa09-945b-219a-4c58-b3d18d391128. This string matches the pkid column in the enduser table. That means I can use the INNER JOIN command to find where device.fkenduser matches enduser.pkid . The device table includes Phones as well as UDP Profiles, so I add "AND device.tkClass=254" to limit output to UDP Profiles.

[Joel Jones]

Thanks for the quick reply! When I run this command i get a "Ambiguous column (pkid)." reply from the command. I'm not a huge SQL guy, but I don't see a where statement in your statement. Isn't that usually after the inner join?

[Randall White]

Sorry, copy error. The correct query is:

run sql SELECT device.name,device.description,enduser.userid FROM device INNER JOIN enduser ON device.fkenduser=enduser.pkid AND device.tkClass=254

My mistake was device.fkenduser=pkid.enduser instead of enduser.pkid. It's always table.column.

It looks like "run sql SELECT device.name,device.description,enduser.userid FROM device INNER JOIN enduser ON device.fkenduser=enduser.pkid WHERE device.tkClass=254" gives the same result.

I'm not an SQL expert, I just try things until it works. I am VERY careful with queries that edit or delete data. Here are some very useful posts:

https://supportforums.cisco.com/t5/collaboration-voice-and-video/shallow-dive-into-call-manager-sql-database/ta-p/3165901

http://www.ucguerrilla.com/search/label/CUCM%20SQL%20Query

Thanks, Randy

[Joel Jones]

Thanks! Yeah, I've read through some of those sites but I never have enough time to sit down and piece these tables together.