Audit list of Jira users

[Richard]

Hi, I have the below script which tells gives me my active users, when they last logged in and how many days since they last logged in.  What I also want in the same query is the Jira role from the Licenserolesgroup table but I can't see how I'd script it in.

All the scripts I've seen online are individual against the role which means running multiple queries which is not desired end result.

SELECT u.display_name as "User", u.user_name AS "Username", u.Email_address,

convert(varchar(20),u.created_date,107) as [Created],

(CASE WHEN DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) is Null then 'NEVER'

ELSE convert(varchar(24), DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}),100)

END

) as [Last Login],

(CASE WHEN DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) IS NULL THEN CONVERT(int,(DATEDIFF(day, GETDATE(),u.created_date) * -1))

ELSE CONVERT(int,(DATEDIFF(day, GETDATE(),DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'})) * -1))

END

) as [Days Since]

FROM [jiraschema].[cwd_user] u

JOIN (

SELECT DISTINCT child_name

FROM [jiraschema].cwd_membership m

JOIN [jiraschema].licenserolesgroup gp ON m.parent_name = gp.GROUP_ID

) AS m ON m.child_name = u.user_name

FULL OUTER JOIN (

SELECT *

FROM [jiraschema].cwd_user_attributes ca

WHERE attribute_name = 'login.lastLoginMillis'

) AS a ON a.user_id = u.ID

JOIN [jiraschema].cwd_directory d ON u.directory_id = d.ID

WHERE u.Active = 1

GO