Service Now Pivot Report Exports

[pramn]

I am trying to export the ticket count for all ~750 business applications in Company Power from the task table of Service Now. This obviously leads to hundreds of thousands of results (over 140,000 tickets) but I am not interested in each ticket, I am just looking for the count of tickets per app, hence I created a pivot type report with ticket count as a summarizing metric:

but the number of records on the data filter vs actual record numbers are different:

On further inspection, I see that only 501 of the 750 app CI ID's I mentioned in the filters show up, and while that might be explained by those apps not having any tickets in the task table, I spot checked some of these values and when queried individually the tickets show up:

The above screenshot shows that despite the filtered CI's being in the original list, they don't return any tickets in the first run whereas when the same query is used to filter for just these CI's 37 task tickets are returned (despite the filter the same conditions such as creation date, task type etc. in both cases)

All I am looking for is 750 rows of data of CI's with their respective task ticket counts. It would really help if you could provide some suggestions

Thanks .

[Mark Manders]

You can run a script to get the results and export that. Something like below (check the tables to be sure we are talking about the same ones and if all conditions are applied):

(function () {
    var taskCountByBusinessApplicationSysId = {};
    var businessApplicationSysId;
    var taskCount;

    var aggregateTask = new GlideAggregate('task');
    aggregateTask.addNotNullQuery('cmdb_ci');
    aggregateTask.groupBy('cmdb_ci');
    aggregateTask.addAggregate('COUNT');
    aggregateTask.query();

    while (aggregateTask.next()) {
        businessApplicationSysId = aggregateTask.getValue('cmdb_ci');
        taskCount = parseInt(aggregateTask.getAggregate('COUNT'), 10);

        taskCountByBusinessApplicationSysId[businessApplicationSysId] = taskCount;
    }

    var grBusinessApplication = new GlideRecord('cmdb_ci_business_app');
    grBusinessApplication.orderBy('name');
    grBusinessApplication.query();

    while (grBusinessApplication.next()) {
        businessApplicationSysId = grBusinessApplication.getUniqueValue();

        gs.info(
            grBusinessApplication.getDisplayValue('name') + ' | Task count: ' +
            (taskCountByBusinessApplicationSysId[businessApplicationSysId] || 0)
        );
    }
})();