hi,
i need to write a report about the users in the jira database. The report should contain the display_name, the last login date and a check if the user are in the confluence or jira group.
i have wrote the following query, but:
1. i think i have a mistake in the check if jira or confluence
2. in the cwd_user are users doubled, in the directory 10000 and 1, i need both unfortunally, if the user is in 10000 i dont need the 1, but if its not in 10000 i need the 1. I dont know how to write that
3. the date for last login doestn work, any idea why?
select
u.id,
U.display_name as Benutzer,
dateadd(second,cast(cast(a.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000'),
CASE WHEN member.parent_name = 'confluence-users' THEN 'JA' ELSE 'Nein' END as Confluence,
CASE WHEN member2.parent_name = 'jira-software-users' THEN 'JA' ELSE 'Nein' END as Jira
FROM jiradb.cwd_user U
LEFT JOIN
[Jira_RFS].[jiradb].[cwd_membership] member on member.child_id = U.ID and member.parent_name = 'confluence-users'
LEFT JOIN
[Jira_RFS].[jiradb].[cwd_membership] member2 on member2.child_id = U.ID and member2.parent_name = 'jira-software-users'
LEFT JOIN
[Jira_RFS].[jiradb].[cwd_user_attributes] a on a.user_id = u.id
--where u.CREDENTIAL != 'nopass'
group by
u.id,
U.display_name,
dateadd(second,cast(cast(a.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000'),
CASE WHEN member.parent_name = 'confluence-users' THEN 'JA' ELSE 'Nein' END,
CASE WHEN member2.parent_name = 'jira-software-users' THEN 'JA' ELSE 'Nein' END;
