Global Activity - SQL Request

[Jonathan Smith]

Hi everyone,

  I am looking to obtain the SQL behind the scenes which generates the global activity graphs. My goal is to show a year activity view by modifying the date parameters.

Any SQL or note to the correct table would be great.

Thanks!

[Gezim Shehu [Communardo]]

EDIT ->> unrelated to question. This only applies to JIRA DB

 SELECT DISTINCT
 p.pname,p.pkey
 ,i.issuenum,cg.issueid
 ,cg.ID ,cg.AUTHOR,cg.CREATED
 ,ci.FIELDTYPE,ci.FIELD
 ,ci.OLDVALUE,ci.OLDSTRING
 ,ci.NEWVALUE,ci.NEWSTRING
 
 FROM 
 changegroup cg 
 INNER JOIN jiraissue i on cg.issueid = i.id
 INNER JOIN project p on i.project = p.id
 INNER JOIN issuetype t ON i.issuetype=t.id
 INNER JOIN changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='jira' AND ci.FIELD='status' 
 
 WHERE 
 p.pname = 'project name
 AND t.pname = 'issue type name'
 AND ci.oldstring = 'old status name'
 AND ci.newstring = 'new status name'

[Jonathan Smith]

Thanks @Gezim Shehu [Communardo]. I am however looking for Confluence results.

[Gezim Shehu [Communardo]]

LMAO so sorry.

Didn't really see the labels, as I usually have them defined in my feed (not today though :) )