Incident count based on Category/Subcategory

[User152938]

Hi All, I am trying to figure out best way to create a report for incidents count. Requirement is to create a report to show all categories and subcategories for incidents and then it need to show incident count and last date when particular Category/Subcategory was used. It is possible that some of the subcategories have never been used so the report cannot be on just incident table. I am thinking I may have to write some script to get list of category/subcategories from sys_choice table and then get count for incidents. Then print data on a ui page. One problem with this approach is, there are hundred of thousands of tickets and around 4000 subcategories so script will take forever to run. Is there a better way to implement this? 

[Adam Stout]

I think a script is the way to go (as we can't use related list conditions here).  However, it doesn't seem to take too long to run.  I ran this in less than 10 seconds on more than a million records.  You can also modify the query to restrict the incidents being looked at to the last 12 months if it is taking a long time.

Here is what I ran

var TABLE = 'incident';
var FIELD = 'subcategory'
var getUsedValues = function (table, field, encodedQuery)
{
    var usedValues = [];
    var gr = new GlideAggregate(TABLE);
    if(encodedQuery)
    {
        gr.addEncodedQuery(encodedQuery);
    }
    gr.groupBy(field);
    gr.query();
    while(gr.next())
    {
        usedValues.push(gr.getValue(field));
    }
    return usedValues;
};

var unusedChoices = function (table, field, usedValues)
{
    var unUsedValues = [];
    var gr = new GlideRecord('sys_choice');
    gr.addQuery('name', '=', table);
    gr.addQuery('element', '=', field);
    gr.addQuery('value', 'NOT IN', usedValues);
    gr.addQuery('inactive', '=', false);
    gr.addNotNullQuery('value');
    gr.query();
    while(gr.next())
    {
        unUsedValues.push(gr.getValue('value'));
    }
    return unUsedValues;
};


var usedValues = getUsedValues(TABLE, FIELD, null);
var unUsedValues = unusedChoices(TABLE, FIELD, usedValues);

gs.info('Used Values: ' + JSON.stringify(usedValues));
gs.info('Unused Values: ' + JSON.stringify(unUsedValues));

This doesn't handle duplicate values with different dependent values but hopefully, that should be minimal.