For a list of all jabber users and their last login time, you can run the following query:


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 date is in something called “epoch” format. To convert to something that makes sense, you’ll need to use the following formula:

=(((B2/60)/60)/24)+DATE(1970;1;1)+(-5/24)

This is for Eastern Standard Time where -5 = UTC -5, you can change this accordingly.

3 Thoughts on “sql: jabber last login time

  1. Broken for SSO…

    • Jeff Levensailor on April 2, 2019 at 1:14 pm said:

      thanks for letting me know. I’ve pretty much switched to teams for everything so I haven’t been using Jabber even at clients. I’ll have to check this out

      • I havent managed to find a cheap workaround. We have multi-cluster setup where this is important for customer reporting, and my colleague resorted to a polling solution. Havent looked deeper myself.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation