Measuring Catalog Item Usage Comprehensively Across All Produced Tasks in Performance Analytics

If your organization uses Catalog Items to only produce Requested Items, then measuring usage is straightforward (group a count of Requested Item records by the Item field). Our organization, though, uses the Service Catalog to produce many other types of Tasks. This is difficult to measure because Tasks other than Requested Items don't contain a field like Item to associate the Task with the Catalog Item that produced it. Instead, non-RITM Tasks produced by Catalog Items are logged in the Item Produced Records [sc_item_produced_record] table. Since RITM production is only logged in the Requested Item [sc_req_item] table and not in Item Produced Records, there isn't a single location in ServiceNow that gives us a complete view of Catalog Item usage. You could create two separate reports to see production across those two tables, but that reveals another problem: you can only see what Catalog Items are producing records, not what Catalog Items are not producing records.

We wanted a way to rank all Catalog Items by the volume of Tasks they produce, regardless of Task type, and also clearly see which ones aren't being used. To do that, we employed Performance Analytics, and the steps below describe how.

1	Login to ServiceNow as a user with the pa_admin role.
2	Click Scripts inside Automation under Performance Analytics. Click New beside Scripts. Set Name to Count Catalog Item Usage, Description to Given the sys_id of a Catalog Item (from the current record being examined in sc_cat_item), this counts the number of times it appears in the Item Produced Record table and the Requested Item table. If it doesn't appear in either, zero is returned. This script is only intended to be run against the Catalog Item [sc_cat_item] table., Facts table to Catalog Item [sc_cat_item], Fields to Sys ID, Script to: var count_cat_item_usage = function(cat_item_sys_id) { var count = 0; if(cat_item_sys_id != null) { // If we have a Catalog Item // Construct queries that search the Item Produced Records and Requested Item tables // for Tasks produced by the Catalog Item between the start and end dates of the // Performance Analytics data collection period. var sipr = new GlideRecord('sc_item_produced_record'); var sri = new GlideRecord('sc_req_item'); sipr.addQuery('producer',cat_item_sys_id); sri.addQuery('cat_item',cat_item_sys_id); sipr.addQuery('sys_created_on','>=',score_start.getGlideObject().getDate()); sipr.addQuery('sys_created_on','<=',score_end.getGlideObject().getDate()); sri.addQuery('sys_created_on','>=',score_start.getGlideObject().getDate()); sri.addQuery('sys_created_on','<=',score_end.getGlideObject().getDate()); sipr.query(); sri.query(); // Count the number of rows that were returned by the queries and add them together. count = sipr.getRowCount() + sri.getRowCount(); } return count; }; count_cat_item_usage(current.sys_id); Click Submit.
3	Click Breakdown Sources inside Sources under Performance Analytics. Click New beside Breakdown Sources. Set Name to Catalog Items.Active, Description to Active Catalog Items., Facts Table to Catalog Item [sc_cat_item], Field to Sys ID, Conditions to Active is true, and Label for unmatched to Unassigned. Click Submit.
4	Click Automated Breakdowns inside Breakdowns under Performance Analytics. Click New beside Breakdowns [Automated View]. Set Name to Catalog Itemand Description to A breakdown for references to Catalog Items. On the Automated tab, set Breakdown Source to Catalog Items.Active. Click Submit.
5	After the Catalog Item breakdown page reloads, click New on the Breakdown Mappings tab. Set the Facts Table to Catalog Item [sc_cat_item] and Field to Sys ID. Click Submit.
6	Click Indicator Sources inside Sources under Performance Analytics. Click New beside Indicator Sources. Set Name to Catalog Items.Active, Description to All active Catalog Items., Valid for frequency to Daily (or any other frequency you want), Facts Table to Catalog Item [sc_cat_item], Conditions to Active is true AND Created at or before Today. Click Submit.
7	Click Automated Indicators inside Indicators under Performance Analytics. Click New beside Indicators [Automated View]. Set Name to Active Catalog Item Usage Count, Description to The number of Tasks produced by active Catalog Items. Intended to be viewed with the Catalog Item breakdown so that the usage of Catalog Items is revealed., Frequency to Daily (or the frequency you chose in the previous step), Direction to None, Unit to #, and Precision to 0. On the Source tab, set Indicator Source to Catalog Items.Active, Collect records to true, Aggregate to Sum, Scripted to true, Script to Count Catalog Item Usage, and Value when Nil to 0. On the Access Control tab, set Publish on Analytics Hub to true. On the Other tab, set Render Continuous Lines to true. Click Submit. After the page reloads, click Edit on the Breakdown tab, add Catalog Item to the Breakdowns List, and click Save.
8	Under Performance Analytics > Jobs, schedule a regular job to collect data for the Active Catalog Item Usage Count indicator going forward at the frequency you want. Execute an ad-hoc job to collect historical records.
9	To display the data, create a widget (Performance Analytics > Widgets) based on the Active Catalog Item Usage Count indicator and then place that widget on a dashboard.

The end result, used in a workbench widget, will look like this...

Labels: