Export of device and owner user ID

[UrbanPeasant]

Hello,

We're in a position where there is about to be a large removal of EM profiles and the phone those profiles were using from our CUCM 12.5.1 estate. Our business model has always been one user, one phone. But the business is now acting on the hundreds of users that don't use that phone at all, as it's so inefficient for licensing.

We have an in-house program that will make removing the EM profile and the user's CSF device fairly easy and automated once given a list of users to remove. What this program can't do, with similar ease, is remove the logged out phone that the profile was previously logged in to. I need to have this SEP device to owner user ID mapping information ready before we start removing those users.

Can someone help out with an SQL query that will output all of our SEP phones and their associated owner user ID?
Once this list is then filtered for users that have been (or are about to be) deleted, I'll feed that subset of SEP phones into BAT for deletion to free-up licenses.

Thanks very much in advance

Nathan.

[Ritesh Desai · Accepted solution]

Hi,

Follow this process.

Login into your call manager using SSH tool and enable logging.

Type the below command;

#show risdb list – You will get the list of templates which can be used to extract.

#show risdb query phone – You will get the phone details only

#show risdb query phone, phoneextn – You will get the phone details with phone extension details as well.

You will get all phone information.

Open the above output.txt file in MS Excel.

Select Delimited and Comma to segregate the data in MS Excel.

Check for username column if you have fetched data using show risdb query phone command.

If you have multiple CUCM servers in a cluster and phones are load balanced among the CUCM server then you have to individually fetch phone data from each CUCM servers. Follow the above process for each CUCM servers

regards,

Ritesh Desai

Please rate helpfull post.

[UrbanPeasant]

Thanks Ritesh, that's going to get the output we need nicely.

SEP34A84E61D29D, EM Phone, 10.31.199.32, , 3, 0, 34A84E61D29D, reg, SCCP, 496, y es, 0, 383, brownj1, 909, 1589430283, 1, SCCP69xx.9-4-1-3SR3, SCCP69xx.9-4-1-3SR 3, , , , 0, , 1589430283, 2

As an aside we've come up with an SQL command too that looks like it'll also provide the answer when run against the Publisher:

run sql SELECT device.name, enduser.userid FROM device LEFT JOIN enduser ON device.fkenduser = enduser.pkid

and we end up with output like:

name                       userid
=============== ======
clinch                       clinch
CSFclinch                 clinch
SEP0014A86BC293 clinch

So one way or another we can see the SEP to userid map.

Thanks for the steer on risdb

Best

Nathan.

[Ritesh Desai]

@UrbanPeasant thanks for sharing. Glad that our problem is resolved. Cheers.

regards,

Ritesh Desai

Please rate helpful posts.