To find out the last logged in times for Jabbers in CUCM required the below shell command. I found the command on the Cisco Support Forum.. I have added the reference link to the bottom of this blog. I though I would extend the the process of getting the info out of CUCM and into a spreadsheet to make some sense of the data.

SSH to the CUCM Publisher Server and execute the below SQL command.

run sql select e.userid, cd.timelastaccessed from enduser as e, credentialdynamic as cd, credential as cr where e.pkid=cr.fkenduser and e.tkuserprofile=1 and e.primarynodeid is not null and cr.tkcredential=3 and cr.pkid=cd.fkcredential order by cd.timelastaccessed

Snippet of the results.

I had logging enabled in Secure Shell as the results will span past the shell buffer. Open Excel and import the log file generated. I use ‘Delimitated’ and separated via ‘space’.

After the log file has been imported into excel, I use the Unix to excel time formula to make sense of the date.

Formula is =CELL/(60*60*24)+”1/1/1970″

*Note: ensure the format of the cell is ‘Date’.

Save the spreadsheet, and you now have a full list of all users and their last logged in date for Jabber.

References:

Cisco Support Forum: https://supportforums.cisco.com/t5/unified-communications/jabber-report/td-p/2957556