Group by for affected service

Question

I am looking for a way of grouping by affected service and then displaying on a dashboard.

In SQL i would write

SELECT COUNT(*) , [Affected Service]

FROM tableName

GROUP by [Affected Service]

ORDER BY COUNT(*)

The ideal would be to have a gadget on my teams dashboard with a Two-Dimensional Filter Statistics Gadget with the service and the count which would then allow you to click on the total and drill down.

The reason for this is each service request has 1 or more affected services and I am looking for

1) Hotsports with the product where the most issues are happening

2) A way of the helpdesk team finding related tickets which have  the same affected service. It is possible to write a 1 of query which the team would need to change each time

JQL: project = "Help Desk" AND "Affected services" = "NAME OF THE SERVICE"

Hannes Obweger - JXL for Jira · Answer

Hi @Damian Martin

understood! Just FYI, we just released a new version of JXL that supports the Affected services field - so you can now group by it as shown above, without any further configuration or scripting needed.

Best,

Hannes

Damian Martin_f7mjpr · Answer

Affected services are a list of services created in Opsgenie (similar to a service catalogue) For example you might have a Service of Office Network, Fibre Connection,MS Outlook and it then allows you to add them as dependencies So Outlook depends on the office network and the office network depends on the Fibre connection. If someone creates a service request that they are not receiving emails they would put a description and select the affected service (in this example MS Outlook) The reason I would want to see the services grouped in a widget is to see other tickets raised which also have the service of MS Outlook as it allows you to group tickets and see if there is a problem

Hannes Obweger - JXL for Jira · Answer

Hi @Damian Martin

just to clarify, when you say Affected Services, are these Assets?

I'm not a great dashboard expert, so if anyone is aware of a native solution, please jump in - but if you're open to solutions from the Atlassian Marketplace, you might get pretty close using the app that my team is working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view that allows viewing, inline-editing, sorting, and filtering by almost all of your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of advanced features, including the ability to group your issues by any issue field, and calculate sum-ups across this groups. With this, you can create a view like this in just a few clicks ("Inventory" is an Assets field):

As you can see, you can easily drill down to your issues and start working on them right away. There's also many other advanced features, all perfectly compatible with Assets, such as support for issue hierarchies, or conditional formatting.

Two things to note: First, while you can always see the number of issues per group, you can't easily (i.e., not without some Jira Automation) sort by this number of issues yet. We'll offer this in the future. Second, unfortunately, the built-in Affected Services field is not fully accessible to 3rd party apps yet, but you can easily create an Assets Object custom field that shows the same data; this will then work perfectly fine in JXL.

Any questions just let me know,

Best,

Hannes