You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I need to generate a detailed per-project user report from Jira:
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],
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]
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
al.CATEGORY = 'projects'
CROSS APPLY (
SELECT TRIM(CAST(VALUE AS VARCHAR(MAX))) AS [VALUE]
FROM STRING_SPLIT( CAST(TRIM(userdata.DELTA_FROM) AS VARCHAR(MAX)),',')
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;