how to find jabber user last login

[Vinod Kembhavi]

Hi All,

I just want to find the Jabber user who never use their jabber account who were also assigned with telephone service. How to find the last login detail of Jabber user..

Call Manager Version - 9.1.1

IM and Presence Version - 9.1.1

Window Jabber Client - 9.7

Thanks,

Vinod K

[Amit Kumar]

i scanned the reporting section of im and presence; however did not find too much of help. In my opinion one of ways to finding this information might be via using the compliance feature.

Using tool like PostgreSQL we can archive all im's sent and received via the cups node; which if analyzed carefully can give us all various types of matrices needed. This may not be the best way though i don't know if there is any other option beyond it.

- please rate if you find this helpful.

[Vinod Kembhavi]

Thanks Amit for your response but is there any other simple option to find out jabber user who never logged in OR last login status..

Thanks,

Vinod K

[Paul Tursan d'Espaignet · Accepted solution]

I ran into the same issue (my client is still running CUPS v8.6.2).

I've lost the link to the Cisco bug ID, but still have the workaround:

1. Run the following command from CLI (all one line)

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

This will give you a complete user list and the last login date in Epoch format. Then you can import into MS Excel as a tab delimited file, split into two columns, then use the following formula (assuming you kept hte top row which shows UserID / Last Login Time): =(((B2/60)/60)/24)+DATE(1970;1;1)+(2/24)

(Note: the last (2/24) should be adjusted according to your timezone - this is for UTC +2:00. For UTC +8:00, make it (8/24), for UTC -5:00 (-5/24), etc.

[ivpere0032]

Hi Paul, this is great ! thanks!

How could we take the last log out too ?, because we have some users logged in for twenty days

This information is very usefull to monitor the use of the software.

Thank you !

Ivan

[Paul Tursan d'Espaignet]

Unfortunately I don't think there is a logout time in the database. You can check though yourself by googling for the CUCM database dictionary that Cisco publishes for each version of CUCM. Your best bet is in the end user, credential and credential dynamic tables.

You might be able find an entry that you can use to determine whether the user is currently logged on, and combine that with the last login information to work out who your active and inactive users are. For getting into some more complicated SQL queries I recommend a quick Google. I found a really helpful blog from a database admin who went though and explains the syntax quite well. :)

[Aman Soi]

Hi Ivan,

you can refer the bug with similar discussion

https://supportforums.cisco.com/discussion/13120466/jabber-report

regds,
aman

[ivpere0032]

Thanks Aman

[ivpere0032]

Thanks Paul