Does anyone know how to get a list of incident or request categories that have not been used?

[trdaniels]

I can get a count of categories that have been used and how many times but I don't know how to get a list of categories that have not been used, does anyone know how to do this?

[She Sull]

You can create a script include to:

1. Query the choice table for the inc and req categories.
2. Add those categories into an array.
3. Next, perform a new query on the inc and req tables to find records that do not have any of those categories (something like "category", "NOT IN", "nameOfFirstArray"), you need to push these category sys_ids to a new array.
4. Return the sys_ids of the category records from the last query.
5. Save the script include.
6. Next, go to Choice Lists.
7. Add a condition where the Sys ID is javascript:nameOfScriptInclude().
8. This should give you the list of categories that have not been used on an inc or req.

[Arnoud Kooi]

If its a one time check, maybe you can just do a group by on category in the incident / request list and compare it manually with the available choices.

[trdaniels]

too many categories for anything that requires a manual process.

[Arnoud Kooi]

Try this (background) script:

//Options
var onlyZero = 1 //set to 0 to display all categorie counts.
var table = 'incident';


//backgroundscript
var gr = new GlideRecord('sys_choice');
gr.addEncodedQuery("name="+ table +"^element=category^language=en^inactive=false");
gr.orderBy('value');
gr.query();
gs.print('Category|Count');
while (gr.next()) {
   var count = new GlideAggregate('incident');
   count.addQuery('category', gr.value);
   count.addAggregate('COUNT');
   count.query();
   var catCount = 0;
   if (count.next())
       catCount = count.getAggregate('COUNT');
   if (catCount == 0 || !onlyZero)
       gs.print(gr.value + '|' + catCount);
}

It querys the choicelist for all active options, and does a glideaggregate for each choice.

If you want it as a report, I would create a databaseview, with this criteria, base on the sys_choices table, left joined to incident.

Then if you use the DB view and set a is empty filter on the number field, it will result in the unused categories