Report for Non Accessed UDP

[Carl Ratcliffe]

Hi Support Community

We are using CUCM 8.6.2 and we have 3000 user device profiles. Issue is we only have 2500 staff so clearly some house keeping needs to be done.

You can run a report that shows you logged in extension mobility users and date/time they logged in but what im looking for is some way of getting the information which shows the last date UDP profiles were used, im hoping somebody maybe able to point me in the right direction of a database command i can run, something along the lines of show UDP profiles that have not been accessed for 6 months etc

I have done a bit of reading online and cannot find anything apart from a few similar questions that dont seem to have a solution.

Thanks, Carl Ratcliffe

[Jaime Valencia]

I think the only thing you could do would be to parse thru the EM traces and them crosscheck against the configured UDPs.

Sent from Cisco Technical Support iPad App

[Carl Ratcliffe]

Hi Jaime

Yes i was thinking the same was just hoping somebody was going to have another solution before we settle for this method.

Thanks, Carl

[Carlo Poggiarelli]

Hi Carl.
You can run this query to verify last login per user
run sql select eu.userid, emd.logintime, emd.loginduration, emd.datetimestamp from extensionmobilitydynamic emd inner join enduser eu on emd.fkenduser_lastlogin=eu.pkid


HTH

Regards

Carlo

Sent from Cisco Technical Support iPhone App

[Carl Ratcliffe]

Thanks Carlo

Do you know if there is anyway of running a similar query that would say bring back users who's last login time was over 6 months ago.

Thanks, Carl Ratcliffe

[Carlo Poggiarelli]

Hi Carl.

The query gives you the timestamp of last time that the user logged in, if you add "order by emd.datetimestamp desc" the last entry would be the erliest login seen by CUCM it's in universal format and you can convert here http://www.onlineconversion.com/unix_time.htm

HTH

Regards

Carlo

Please rate all helpful posts

"The more you help the more you learn"

[Carl Ratcliffe]

Hi Carlo and thanks for your response.

Isnt this query per user though ? What i was looking for was a generic query which will bring me back all UDP on the system in 1 query that havent been accessed for over 6 months. Then these users only will be cross referenced against leavers lists etc raher then having to run a uery for each user.

Thanks, Carl Ratcliffe

[Ayodeji Okanlawon]

Carl,

This query shows the login report for the whole system, an example shown below

dmin:run sql select eu.userid, emd.logintime, emd.loginduration, emd.datetimestamp from extensionmobilitydynamic emd inner join enduser eu on emd.fkenduser_lastlogin=eu.pkid
userid               logintime  loginduration datetimestamp
==================== ========== ============= =============
Patthew.Bullins2     NULL       NULL        1344853306
Patthew.Bullins2     1345545975 0           1345545975
Dris.K.Rodger       NULL       NULL         1335772564
Dris.K.Rodger       1336038675 0            1336038675
CO006                1322570852 0           1322570851
Agus.Dallagher     1323181680 0             1323181679
Shetina.Bands           1339434053 0        1339434052

[Carl Ratcliffe]

Hi Ayodeji

Thanks for the response. The query is really good for seeing who is logged into EM and for how long but what i am looking for is who isnt logged in and when the last time they did log in.

So for example we have 3000 user device profiles but only 2500 staff. We have checked with our IPT team and it seems they are not informed about leavers therefore user device profiles of leavers have been left on the system. We want to delete these profiles so what we want to do is narrow the search down by having a query which brings back user device profiles not logged into EM say for at least 3 months. We can then use this narrowed down search to delete rather than go through all 3000 profiles.

Is there also a query that can be run which will tell us any user device profiles that are not associated to a user ?

Thanks, Carl Ratcliffe

[e-avelino]

Hi Carlo Poggiarelli.

You told to run this query:

run sql select eu.userid, emd.logintime, emd.loginduration, emd.datetimestamp from extensionmobilitydynamic emd inner join enduser eu on emd.fkenduser_lastlogin=eu.pkid
 

Where have I run this query?

[Carlo Poggiarelli · Accepted solution]

Hi Carl.
This query reports all users with udp profile in the ststem

HTH

Regards

Carlo

Sent from Cisco Technical Support iPhone App

[Carl Ratcliffe]

Excellent, Thanks Carlo i will give this a try hen im back in the office tomorrow.

Thanks, Carl Ratcliffe