SQL statement for mobility users

[Stuart Patton]

Hi,

Does anyone have a SQL statement that can show the last login times for all mobility users in a CUCM 8.6 system?  Extra kudos if you can do this for mobility users that are not currently logged in.

Thanks,

Stuart

[Jitender Bhandari]

Hi Stuart,

If you are looking for information on extension mobility

Cisco Unified Reporting-->Unified CM Extension Mobility-->Generate Report.

Unfortunately this doesn't give a history of past logins and events. It only displays current information.

The history of EM login/logouts can be collected from the log files.
Using RTMT tool, we can just get the total number of completed login/logouts but not the details of EM users who performed login/logout.

Also see below.

https://supportforums.cisco.com/discussion/11671126/cucm-86-extension-mobility-logging

JB

[Jitender Bhandari]

Also see 

http://www.ucguerrilla.com/2012/06/using-sql-to-validate-cucm-extension.html

https://supportforums.cisco.com/discussion/11543716/method-discovering-un-used-extensions

JB

[Stuart Patton]

Unfortunately, I'm hitting CSCub16032 for reporting hence why I thought an SQL statement might be a quick win :-S

I had already found that other link, but if I amend the SQL statement to be like the below, I get multiple logins for the same user (including NULLs) and the info doesn't seem right.

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

Thanks,

Stuart

[Jitender Bhandari]

Can you run below and let me know what it returns.

run sql select enduser.userid,DBINFO('utc_to_datetime', extensionmobilitydynamic.logintime) as logintime,extensionmobilitydynamic.loginduration, device.name from extensionmobilitydynamic inner join device on extensionmobilitydynamic.fkdevice = device.pkid inner join enduser on extensionmobilitydynamic.fkenduser_lastlogin = enduser.pkid where device.name == '*'

JB

[Stuart Patton]

I get nothing, just the column headings :(

[Stuart Patton]

Hi JB,

Just modified your SQL as follows, which has given some output that I need to digest:

run sql select enduser.userid, extensionmobilitydynamic.logintime as logintime,extensionmobilitydynamic.loginduration, device.name from extensionmobilitydynamic inner join device on extensionmobilitydynamic.fkdevice = device.pkid inner join enduser on extensionmobilitydynamic.fkenduser_lastlogin = enduser.pkid

Regards,

Stuart

[Jitender Bhandari]

Hi Stuart,

It would be great if you update the post once you are done parsing the data.

JB