SQL Report about Users

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?

selectu.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

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...