Report on number of incidents per category

[Droid101]

I can't figure out how to get a report of all incidents by category, but then to filter out any that have more than 10 incidents.

Basically, I need to find out which of my categories aren't being used so that they can be removed or combined into another one.

I can run a Bar Chart, aggregation is Count, and group by Category, but that shows every incident and only the categories that have at least one incident assigned. I don't want to see the categories that have 8000 incidents. And I don't want the categories with ZERO incidents hidden (since this is a COUNT, it doesn't show the ones with zero).

Any ideas on this?

[jfarrer]

I've tried to do several things with sorting or filtering based on aggregates and there isn't much normal functionality that supports it. I submitted an enhancement to ServiceNow about filtering on the aggregate values last year but haven't heard anything back about it.

That said, I think you've got a few options that should work.

Use scripting:
You could write a script against the incident table using the Glide Aggregate functionality that allows for filtering based on the count values. This will show you what is being used infrequently but not categories that haven't been used. Documentation is here: http://wiki.service-now.com/index.php?title=GlideAggregate

If you want all of the categories and their totals you can do a standard GlideRecord query for the categories and then query for the incidents that use it inside the first loop for the categories. This would get everything and is pretty straight forward. Then if you want to eliminate the big ones you can just add an if statement for the count to set the threshold.

If you need this to be repeatable and you're not afraid of scripting then you could stick the code into a UI Page and add a little HTML to format it.

Use Excel:
Depending on how many records you're looking at you could export the data into CSV and use Excel to do the calculations. I've done this on a number of occasions to get quick information and to slice and dice things.

Create a Join table:
I think you may be able to create a join table that joins the categories and the incidents. Depending on how you set it up you should be able to get the information you need. In theory this should work, but it takes a little more work to set up. If it were me this would be my last choice of the three.


Hopefully that at least gives you a couple options. I'd love to hear how you end up doing it and especially if you find a better way.

[Droid101]

That's wild... because I did SOMETHING last year to get results that I was looking for. I don't remember how I did it though. I sent a list to someone that looks like the below... it was just copied and pasted, not an excel sheet in the email. So crazy.


Building Security — Key Card ----- 2
Ceridian — Cybershift ----- 7
COBOL Reports — Report Functionality-Enhancements ----- 1
Data Center — Access Approval ----- 4
Desktop Applications — Mac OS ----- 1
Enterprise Systems — Enterprise Deployment ---- 4
ERP Procurement — Forecasting ----- 7
ERP Procurement — Supplier Material Costing-SMC ----- 11
ERP Procurement — Vendor Performance ---- 3
ERP Procurement — Virtual Buyer ---- 6
ERP Sales — CAMA ----- 9
ERP Sales — E-Doc ----- 4
ERP Sales — e-Win ---- 4
ERP Sales — Pivotal Application Printing Issues ---- 9
ERP Sales — Pivotal client Printing issues ----- 2
(note, there is an ERP Sales — Pivotal Issues category that has over 100 incidents)
ERP Sales — Zilliant --- 13
ERP Warehouse — Kasto ---- 8
ETL Tool — Troubleshooting ---- 0
H & W — Support ----- 0
Hardware — Cyclades ---- 13
Hardware — iPad ----- 13
Hardware — Maintenance ---- 7
Hardware — Purchase Configuratoin --- 12
Insight — New Report ---- 3
Insight — Training ---- 3
Kronos — Application Support ---- 16
Network — ISDN-Wireless Backup ----- 15
Stel — eStel — StelWriter ---- 14
Stel — eSTEL Interfaces — 1099 ---- 1
Stel — eSTEL Interfaces — Bank Rec ----2
Stel — eSTEL Interfaces — Docuware ---- 3
Stel — eSTEL Interfaces — eDocs --- 4
Stel — eSTEL Interfaces — Journal Entries ---- 3
Stel — eSTEL Interfaces — Positive Pay ---- 5
Stel — eSTEL Interfaces — Tax Stream ---- 1
Stel — eSTEL Interfaces — VMI ---- 1
Stel — eSTEL Interfaces — ACL ----- 0
Web Apps — EMJ Website --- 16
Web Apps — RAS ---- 1
Web Apps — Reference Library Portal ---- 4
Web Apps — RSAEROSPACE.COM ---- 1
Web Apps — Websphere ---- 1
Web Apps — Purchasing Forum ---- 0
Web Apps — EMJ Wiki ---- 0
Windows Systems — AD Testing ---- 2