nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Derek Fields _RightStar_

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.

All comments

Derek Fields _RightStar_

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_zen3ee

Hi @Derek Fields _RightStar_

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_zen3ee

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 _RightStar_

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-service-desk

jira-server

report

component