Common Components Report in Jira Service Desk?

[Matthew Swint]

I am trying to find how to report on the most common Components used in our Jira Service Desk deployment by our Service Desk agents. I can't seem to find anything to do this in the built-in reporting capability and can't find what links components to Jira Issues in the database other than having a Project in common.

Anyone know what the JQL query would look like that would spit out a list of, say the top 10 most common Components? I'd like to be able to pull this data from the database too as we are doing some external data analytics on our Jira Service Desk data and the database is all over the place with table relationships.

[Derek Fields · Accepted solution]

You can see the top components on a Dashboard by using the Issue Statistics gadget. That is the easiest way. 

The SQL to get this from the database is

SELECT b.cname, count(a.component)
FROM jiraissue a
JOIN component b ON a.component = b.id
GROUP BY b.cname;

This will provide the same information as the Issue Statistics gadget. In this case, it does it for all projects. You can restrict it to a single project using the pkey field in the jiraissue table.

[Tim Peters]

Hi @Derek Fields 

Do you know if the db schema changed for components? I use components extensively. Querying the component table returns results, but the component field on the jiraissue table is NULL on every record. 

This only returns NULL for component. 

SELECT a.component, COUNT(*)
FROM jiraissue a
GROUP BY a.component

I'm running Database version 8.0.26

Thanks,

Tim

[Tim Peters]

I found it. In my schema there is a nodassociation table between jiraissue and component.

SELECT J.issuenum,J.CREATED, C.cname
FROM jiraissue as J
JOIN nodeassociation AS NA ON J.ID = NA.SOURCE_NODE_ID AND NA.SINK_NODE_ENTITY = 'Component'
JOIN component AS C ON NA.SINK_NODE_ID = C.ID

Tim

[Derek Fields]

Glad you found it - I was about to respond with the same.