Hi!
I have a query that is currently looking for active users in the user directory that have not been logged on to JIRA for a certain timeframe.
--Active users not logged on for more than 14 months
--Excludes inactive user directories
--Filter on group 'jira-users'
select
d.directory_name AS "Directory",
u.user_name AS "ACF2 ID",
u.display_name As "Fullname",
DATEADD(S, CONVERT(int,left(attribute_value, 10)),'1970-01-01') AS "Last Login"
from [dbo].[cwd_user_attributes] a
join cwd_user u
on u.id = a.user_id
join [dbo].[cwd_membership] m
on m.child_id = a.user_id
join [dbo].[cwd_directory] d
on d.id = u.directory_id
where a.attribute_name = 'login.lastLoginMillis'
and u.directory_id not in ('3','4')
and m.parent_name = 'jira-users'
and u.active = 1
and DATEADD(S, CONVERT(int,left(a.attribute_value, 10)),'1970-01-01') <= DATEADD(month, -14, CONVERT(date, getdate()))
order by "Last Login" DESC;
What I currently do is to use the query above to look up users in the Jira audit log to check if the user was added to the 'jira-users' group
if that date is 1 month or less - no action required
if that date is 1 month + 1 day greater then remove the user from jira-users
However this process is manual looking up in the Jira audit log to check each user.
Is there a way to modify the above query to only show active jira-users who do not appear in the audit log as being added to jira-users in the past 30 days as an example.
Any guidance is much appreciated.
Thank you!
Mary
