I need to generate a detailed per-project user report from Jira:
- Project Name
- User Role
- User Name
- Date Added
- Date Removed
Trying to get this from the database as it's not feasible from the UI since the audit log is basically a before and after for the whole role. I have a query to get the DELTA_FROM and DELTA_TO for each log id into exploded lists (i.e. one row per user), but reconciling the two is making me insane. Why Jira doesn't just track it per project/role/user in the first place amazes me. Any help is appreciated! Below is an example of separating the DELTA_FROM values, I'm no SQL expert so take mercy on me :)
SELECT ROW_NUMBER() OVER (ORDER BY userdata.Created, userdata.ID, TRIM(a.value)) AS [ROW_NUM],userdata.Created, userdata.AUTHOR_KEY,userdata.Study, userdata.ID, userdata.[Role],
TRIM(a.value) AS [FromValue],
cu.email_address
FROM (
SELECT FORMAT(al.CREATED, 'dd-MMM-yyyy') AS [Created], al.ID, al.AUTHOR_KEY,ai.OBJECT_NAME AS [Study], al.OBJECT_NAME AS [Role] ,ISNULL(CAST(acv.DELTA_FROM AS VARCHAR(MAX)), 'N/A') AS [DELTA_FROM],CAST(acv.DELTA_TO AS VARCHAR(MAX)) AS [DELTA_TO]
FROM
dbo.audit_log al WITH (NOLOCK)
JOIN dbo.audit_item ai WITH (NOLOCK) ON ai.LOG_ID = al.id
JOIN dbo.audit_changed_value acv WITH (NOLOCK) ON acv.LOG_ID = al.id
WHERE
al.CATEGORY = 'projects'
) userdata
CROSS APPLY (
SELECT TRIM(CAST(VALUE AS VARCHAR(MAX))) AS [VALUE]
FROM STRING_SPLIT( CAST(TRIM(userdata.DELTA_FROM) AS VARCHAR(MAX)),',')
) a
LEFT OUTER JOIN dbo.app_user au WITH (NOLOCK) ON a.value = au.user_key
LEFT OUTER JOIN dbo.cwd_user cu WITH (NOLOCK) ON cu.lower_user_name = au.lower_user_name
ORDER BY userdata.Created, userdata.ID, [ROW_NUM],cu.email_address;
