Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

how to find jabber user last login

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

Everyone's tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions

I ran into the same issue (my

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.

8 REPLIES
Cisco Employee

i scanned the reporting

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.

New Member

Thanks Amit for your response

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

I ran into the same issue (my

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.

New Member

Hi Paul, this is great !

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

Unfortunately I don't think

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. :)

VIP Purple

Hi Ivan,

Hi Ivan,
you can refer the bug with similar discussion
https://supportforums.cisco.com/discussion/13120466/jabber-report
regds,
aman
New Member

Thanks Aman

Thanks Aman

New Member

Thanks Paul

Thanks Paul

958
Views
15
Helpful
8
Replies
CreatePlease login to create content