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
