Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,363,710
Community Members
 
Community Events
168
Community Groups

Compare Jira users in active directory against audit log

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

 

0 answers

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS

Atlassian Community Events