Users consuming licenses that no longer have phones

[Nathan Millward]

Hello,

We seem to have got into a bit of a mess with respect to licenses and actual usage. The license report tells use everything is good, but is overstating our actual usage with respect to users. The problem is that CUCM thinks that <user> should have a license assigned because their name is set against <SEP> phone. The problem is that the phone is not used by <user> anymore, but still exists in CUCM, and <user> has no DN associated to their EM profile. They still exist in AD, hence CUCM, but are no longer phone system users.

Anyone have any  ideas on how I might go about searching for phones associated with a user, but where that user has no DN associated with their EM profile, then setting all those phones to anonymous? This should leave us with an accurate license report where any username associated with any license will have a DN. The phones will then be withdrawn from service.

Thanks

Nathan.

[Kathy N.]

You might be able top find what you need by clicking on Dependency Records in the upper right "Related Links" area after you have pulled up the user's record.  

[Nathan Millward]

Thanks Kathy, it's the volume of users I need to do this for that means I need a report/sql query method, otherwise this is going to be a painful task.

Nathan.

[Jaime Valencia]

Phones, either tied to a user or for an anonymous user, will consume a license, that is a fact.

The reason to tie devices to a user is so that if you are associating more than once device to the same user, the proper license is used, as they vary in the amount of devices you can associate to each user.

If the phone is not associated to a user, each one will consume a license.

If the phone is not used, but is configured, it will consume a license.

If you create a bogus device because you wanted to see the settings or test something, even if you never register such device, it will consume a license.

The problem is that the phone is not used by <user> anymore, but still exists in CUCM, and <user> has no DN associated to their EM profile

That is working as designed, the phone exists in CUCM but is unused, it consumes a license. The only way for it to stop using a license, is to delete it.

[Nathan Millward]

Thanks Jaime,
I understand that it's working as expected and designed, and also that the phone will still take a license even if not assigned to a user. I happy with that. I was hoping there might have been a report one way or another that detail the phones associated with users where the user has no extension - meaning that the user shouldn't have that phone assigned to them any longer. At the moment I can't identify those users that are assigned a phone, but no longer have an extension to use without going into every single entry on the license report ~1000 users, but I'm anticipating that now there are only around 700 actual system users at most.

Nathan.

[Jaime Valencia]

OK, then you can use BAT, export phones -> all details and that will give you a list of all the devices, along with the DN information on them.

Sort the information on the DN and that should show you which ones do not have a value in that field.

[Nithin Eluvathingal]

Try this SQL Query from CLI. It list the devices who has no DN assigned. 

run sql select device.name as Phone , devicenumplanmap.fkdevice as DN from device LEFT JOIN devicenumplanmap on device.pkid = devicenumplanmap.fkdevice where devicenumplanmap.fkdevice IS NULL and  tkclass =1

[Nathan Millward]

Hi Nithin,

This is heading in the direction I had hoped for, but is complicated by the following: All our phones are deployed with an internal only extension. Users have their EM profile and log in to those phones, the phone then understandably displays the user's DN. The phone then gets updates to state the user is the owner, but when the user loses their DN, but is still in the system, the phone falls back to its internal only DN, then sits there connected, but the owner isn't updated. So we have a quantity of phones that are owned by users who no longer use the phone system, so the user is reflected in the license consumption, rather than 'anonymous.' It's the user without a DN but acting as owner of a phone that is key to the output.

Thanks

Nathan. 

[Nithin Eluvathingal]

So you mean you need to see the device profiles which   has no DN's assigned ?

If you need to list the Device profiles without DN assigned, try below SQL.

run sql select device.name as Phone , devicenumplanmap.fkdevice as DN from device LEFT JOIN devicenumplanmap on device.pkid = devicenumplanmap.fkdevice where devicenumplanmap.fkdevice IS NULL and  tkclass =254

[Nathan Millward]

Afraid that doesn't output the right info, and doesn't output users that I know have no DN assigned to their profile. It seems to be the right thinking, but not the expected result. If was the correct result I'd then need to know of the users in the result (with no DN) which of them were assigned as owners of phones that they should no longer be assigned to.

[Nithin Eluvathingal · Accepted solution]

To list the OWNER-ID assigned on the phones, you can use below SQL Query.

run sql select device.name as devicename, enduser.userid as ownerid  from device   INNER JOIN enduser  on enduser.pkid = device.fkenduser  and  requireCERlocation  = 'f' order by userid

If you need to see the devices without Owner, Which means anonymous. Use below SQL Query. 

run sql select name as devicename, fkenduser as enduserpkid from device where fkenduser IS NULL and tkclass =1

[Nathan Millward]

Thanks Nithin,

I'll go with the OWNER-ID assigned to phone query, and use that with a report for users that have a DN, and then use Excel to find the users assigned to phones that don't have a DN. That should do.

Thanks and kind regards

Nathan.