Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Active users who didn't login for X days report


#1

Hi,
Any idea how can I execute a report (or SQL query)of all users who are active but didn’t login to the system in the last x days?

Best Regards
Itsik


#2

See this thread - it is not easy to determine especially since if folks don’t log off TR - they are still active even if the browser is closed:


#3

Hi BGanger,
I realized that it’s not easy, I wonder why is that.
How am I supposed maintain user list ? :disappointed:

Itsik


#4

Hello,

SELECT user_logins.name,FROM_UNIXTIME(user_logins.updated_on) as’last_login’ FROM users INNER JOIN user_logins ON (users.email = user_logins.name) WHERE users.is_active = 1 AND FROM_UNIXTIME(user_logins.updated_on) < ‘2018-03-01 00:00:00’;

will return in my case : lastlogin

I hope this query will help you :grin:


#6

I wish I knew why - maybe a vestige of an old system TR is based on? They do use Unix time so maybe how Unix handles sessions vs web? It is frustrating and any query is going to be… misleading. My production table has my last session as 3/6/2018 yet I have been using TR everyday for the past 2 weeks including today.


#7

Hi Afaeld ,
After days of use, your does bring all users who didn’t login after certain date but the “last_login” date is not correct.
Tried to fix it but with no success.
I used this query to see all login in the system and asked a colleague to preform login.
that didn’t reflect on any attribute

SELECT u.id,u.name,u.is_active,FROM_UNIXTIME(ul.updated_on) ,FROM_UNIXTIME(ul.created_on),FROM_UNIXTIME(ut.expires_on),FROM_UNIXTIME(ut.created_on) FROM testrail.users as u
left join user_tokens as ut
ON u.id = ut.user_id
left join user_logins as ul
ON u.id=ul.id
where u.is_active=1;

BR
Itsik