how to generate report for users who have not logged in Agile from 2 months
how to generate report for users who have not logged in Agile from 2 months or any specific time duration
Two ways:
1) see my own question from last year about it and create a simple SQL query using the User Usage History table. Search for my “Auto-Inactivate Users” question on this site.
2) run a User Usage Report and compare with a list of your active Agile users. It’s one of the Admin reports.
Try that.
Hi Gurmeet,
To find the users who have not logged in since last two months you can use the following query :
Scenario-1
==========
Select a.loginid,a.email,a.first_name,a.last_name from
agileuser a
where a.loginid not in (Select distinct(u.username) from user_usage_history u where login_time > sysdate – 60)
and a.enabled=1 order by a.loginid
//Just to breakdown-query for your convenience. Query has two parts.
//1. This query gives the details of all the user who has logged in in last two months. i.e. If your current date is 4/20/2018, it should tell you all who have logged in since 2/20/2018
Select distinct(u.username) from user_usage_history u where login_time > sysdate – 60
//2. Query below gives you the details of all the user who are active.
select * from agileusers where a.enabled=1
Scenario-2
============
Finding users have not logged in during particular duration — Here i am finding who logged in between 20th Feb till 20th April
Select a.loginid,a.email,a.first_name,a.last_name from agileuser a
where a.loginid not in (Select distinct(u.username) from user_usage_history u where login_time between to_date(‘2/20/2018′,’mm/dd/yyyy’) and to_date(‘4/20/2018′,’mm/dd/yyyy’))
and a.enabled=1
order by a.loginid
Please let me know in case of any queries.
Regards,
Arif
If the need is to get this information without the use of a database query, then Matt’s way to use the User Usage Report and then setup some filter criteria in excel is the way to go. If for some reason it is necessary to be able to do this via search in the UI, additional work needs to be done outside of the search to maybe set an attribute on the user object to indicate login.
In the case of the front end method, I used to run a User Usage Report for user usage in the past 60 days. Export that. Then separately run an advanced search for all Active Users. Export that list and copy the user IDs over to your other spreadsheet. Use conditional formatting to highlight ‘uniques’ between the list of active users and the list of uses who have log in history in the past 2 months. Any user highlighted means they have no log in history in the past 60 days.