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

[graycarper]

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 Item and 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...

[Jar]

I followed the steps but I don't get the same widget as what you are showing. Did you have to select any particular options within the widget?

[graycarper]

Hi, Jar! So sorry for my delayed reply - I just got back from a 10-day vacation.

You're absolutely right - I didn't describe how to create the workbench widget in my post, and there's definitely some more work to do in order to get the result I showed. First, you'll need to repeat steps 7 and 8 for every interval you're interested in. In my example widget, I show Daily, Weekly, and Monthly intervals, so I had to create Daily, Weekly, and Monthly Automated Indicators (step 7 done three times), then collect all the data for each (step 8). Once all that was done, I added those indicators to my workbench widget. I'm attaching a screenshot to show you exactly what the widget configuration looks like (though this version also includes a Yearly interval).

Does that give you enough information to proceed? If not, please feel free to ask further questions.

-Gray

[Jar]

Cheers for sharing that Gray. I'll give this a go later on today but instructions seem pretty clear.

[Scott Sizer]

Thanks for sharing. Awesome. 

[graycarper]

You're most welcome, Scott! I'm glad you found it useful!

[prashantnagaral]

Hi @graycarper ,

I tried above steps and ran the jobs manually which is collecting the data but unable to get the results in widget and unable to find the breakdown in "breakdowns" tab.

Please find the screenshot and suggest next steps

[graycarper]

Hi, Prashant! Here are a few troubleshooting steps I would try:

1. Under Performance Analytics > Job Logs, check the logs of your manual data collection job. Was data inserted (does the 'Inserts' field have a value greater than zero)? Were there any warnings or errors? If so, what were they?
2. If no data was inserted, then there's definitely a problem with the way the Indicator is configured. I'd double-check steps 1 and 7 to make sure your configuration matches them.
3. If data was inserted, navigate to Performance Analytics > Automated Indicators, open your Active Catalog Item Usage Count indicator, and click Show Analytics Hub (under Related Links). This will let you browse the data without the widget. If you still only see zeroes as you move through the timeline, we'll know for sure that the Indicator is the problem and not the widget.
4. If every piece of data collected is a zero, which is what it sounds like is happening, then I'd be suspicious that the script in step 1 is returning 0 every time it runs. That should only happen if you have zero records, in both the sc_item_produced_record and sc_req_item tables, created by catalog items during the days you ran data collection for. You can manually test this by opening those tables (https://INSTANCENAME.service-now.com/nav_to.do?uri=%2Fsc_item_produced_record_list.do and https://INSTANCENAME.service-now.com/nav_to.do?uri=%2Fsc_req_item_list.do), filtering them for records with a Created date between the days you ran data collection for, and filtering them for records that have producer (in the case of the sc_item_produced_record table) and cat_item (in the case of the sc_req_item table) fields that are not empty. If both of those tables return zero rows, then everything is working correctly and you just need to make sure you run data collection during a time period where rows were created by catalog items in those tables.
5. If either of those tables return a nonzero number of rows, though, then something is going wrong in the script. You can use System Definition > Scripts - Background to test the script (or pieces of it) in realtime without having to run data collection. Use this example...
   

   // CHANGE score_start to the FIRST date in the data collection period
   var score_start = new GlideDateTime("2022-02-01 00:00:00");
   
   // CHANGE score_end to the LAST date in the data collection period
   var score_end = new GlideDateTime("2022-03-07 23:59:59");
   
   // CHANGE to the sys_id of a catalog item that creates records
   // in the sc_item_produced_record table during the time period
   // between the score_start and score_end dates
   var producer_catalog_item = "6ea9ebf1dbc04740a8f33c8f9d96195d";
   
   // CHANGE to the sys_id of a catalog item that creates records
   // in the sc_req_item table during the time period
   // between the score_start and score_end dates
   var ritm_catalog_item = "8b324f981bbbfc9089e7c9d2604bcbc3";
   
   var count_cat_item_usage = function(cat_item_sys_id) {
       var count = 0;
       if(cat_item_sys_id != null) {
           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);
           sipr.addQuery('sys_created_on','<=',score_end);
           sri.addQuery('sys_created_on','>=',score_start);
           sri.addQuery('sys_created_on','<=',score_end);
           sipr.query();
           sri.query();
   
           count = sipr.getRowCount() + sri.getRowCount();
       }
       return count;
   };
   gs.print("sc_item_produced_record count: "+count_cat_item_usage(producer_catalog_item));
   gs.print("sc_req_item_record count: "+count_cat_item_usage(ritm_catalog_item)); 

   ...but update the first four variables to match your scenario. If the two counts that are printed in the output both return zero, but you're expecting them to return non-zero (and you've proven they should return non-zero by testing the queries directly on the tables as described in step 4 above), then you may need to go line-by-line through the script to see how each is behaving.

That's how I would approach troubleshooting the problem. This doesn't cover the empty Breakdown issue, but I'd not worry about that until you've solved the problem of zero records. If you fix that and you still don't see the Breakdown, then we can investigate that.

I hope this helps!

-Gray

[prashantnagaral]

Thank you @graycarper 

Yes, I can see the results from Show Analytics Hub but not in widget. Please help

[graycarper]

Ah! That's good news!

Since the widget isn't showing the data we now know exists, I think the widget should be our next focus. I'm attaching a screenshot of the way my widget is configured so that you can compare with yours. A few things to check...

1. On the Main Widget Indicators tab, is the correct Indicator being used (Active Catalog Item Usage Count)?
2. Are all four Breakdown and Element fields empty (they should be)?
3. Do you have more than one Widget with the same lookup name? If so, is the wrong Widget being used on your dashboard?

I don't see other configuration elements that look like they could cause the problem you're experiencing, so hopefully it's one of those two things. If not, send me screenshots of...

• The Widget configuration
• The Indicator and Indicator Source configuration
• The Breakdown and Breakdown Source configuration

Good hunting!

-Gray

[Nisha31]

I am getting the same thing.

It shows zero 

[graycarper]

Hi, Nisha!

Have you tried the troubleshooting suggestions from earlier in this thread? Like Prashant's, does your Indicator have data but only the widget shows zeroes?

-Gray

[Nisha31]

Hi, It worked once created #6 for each of those data sources. 

So basically i had to create weekly, monthly and yearly data source and then repeat #7 as well 

[graycarper]

Ah! Yes, that makes sense. I have a different Indicator, each with its own separate Indicator Source,  for each time interval (daily, weekly, monthly, etc). I'm so glad you solved the problem!

-Gray

[nizar_tliba]

Hi @graycarper,

 Thanks for your great article. I followed it in a sub-production instance inorder to have the same daily dashboard you've attached as screenshot. However, I've got 0 in the widget in an interval of D-1/D+1.

So, can you please share a screenshot of your Scheduled Data Collection jobs, may be I need to configure the Collection paramaters in stead of using the default one ?

The other steps (1 to 7) are pretty clear and step-by-step well explained. I beleive, I have different results because of the step 7 configuration.

Thanks

[graycarper]

Hi, @nizar_tliba! I'm so glad you found this article helpful! But I'm also so sorry it isn't working yet for you. Hopefully we can find the root of the problem together.

It sounds like you're experiencing something similar to what prashantnagaral and Nisha31 reported in the comments above, though each of their problems were different and I suspect yours isn't exactly like theirs either. If you haven't already, try following the troubleshooting steps I outlined in my comment dated 03-07-2022 10:40 AM.

Below you'll find a screenshot of what my data collection job looks like for both my daily and weekly indicators. If you have any questions about it, or if you make some progress while going through the troubleshooting steps and want to discuss, just let me know.

Good hunting!

-Gray

[nizar_tliba]

Hello @graycarper,

 Thank you for your support. That's fine for the PA reports.

I was wondering whether there is a way to export the PA report usage data ?

For example, the numbers shown under "W2 2021" of your first screenshot showing your PA dashboard.

Thanks

[graycarper]

Hi, @nizar_tliba! Yes, you can export PA indicator data (like what's shown under "W2 2021") by creating a Scheduled Indicator (which you can run on-demand when you need it or set a recurring automated schedule).

-Gray

[James Rostron]

Hi @graycarper ,

This really is a great post but i am struggling a little bit and wondered if you can assist me.

I've setup as described but am not seeing data in Analytics Hub to start with. I thought it might help if i share some screenshots and maybe you can spot where I have gone wrong.

My three indicator sources:

Daily as an example:

My three Automated Indicators:

Daily as an example:

My Scheduled Job:

My Automated Breakdown

Analytics Hub for Daily (shows no records)

Are you able to see what I have mis-configured?

Appreciate any help.

Thanks,

James

[graycarper]

Hi, @James Rostron! I'm so sorry for my delayed reply - I just returned from a vacation - and I'm so sorry you've run into trouble!

The configuration showed in your screenshots looks correct to me, so the problem should be either in the data itself or in the script that fetches the data. To check both, scroll up and take a peek at troubleshooting steps 4 and 5 in the comment I wrote dated 03-07-2022 10:40 AM. That'll walk you through the process of seeing what the number should be (based on what's in the source tables) and comparing that to the number the script is returning. If you get stuck along the way, or want me to look at anything you discover, just let me know.

-Gray

[James Rostron]

Hi @graycarper,

No need to apologise, I'm really grateful you took the time to reply.

The good news is that I can now see records in Analytics Hub. I think I just needed to give some time to the data to get collected but I did have a question. What seemed to throw me was seeing the number of inserts and the number of deletes in the job logs. Why are there deletions?

Also on the indicator sources for weekly/monthly are my conditions correct? Should they match the daily indicator source?

I haven't got to the next step of creating the widget but will give that a try shortly.

Many Thanks Again,

James

[graycarper]

Hi, @James Rostron! Oh good! I'm so glad you're seeing records now! Let's look at each of your questions...

1. My understanding is that deletions happen whenever a data collection job needs to overwrite a cell in the underlying table (existing data in the cell is deleted and then new data is added), and the most common reason for that is the job's start and end dates overlap with a period of already-collected data. It's definitely puzzling that - given that our Relative Start and Relative End periods are '1' - we'd have any overlap, but I can confirm that the inserts and deletes have the same pattern on my end. The resulting data checks out, so I think everything is okay, but if you're concerned about the deletes and want a full explanation you could open a support request with ServiceNow so that they can dig into the guts of the job's execution.
2. My daily/weekly/monthly Indicator Source conditions do resolve to different dates, but I think it's possible that they would resolve to the same date. What's important is how the Indicator Sources are configured, so below I'm going to drop screenshots of my weekly and monthly Indicator Sources for you to check against.

Weekly:

Monthly:

You'll definitely notice one big difference: Mine don't include the Active is true filter condition. This is a change from the original design that we decided to make because, if a Catalog Item becomes inactive during a given week or month, any uses of that Catalog Item prior to it becoming inactive won't be seen by the indicator and therefore won't be reflected in the collected data for that week/month. This is a trade-off between accurate data and reduction in data collection processing time (only counting active Catalog Items takes less time) and, since these jobs run in a matter of seconds for us, we decided that a small performance sacrifice was worth gaining accurate data.

Does this give you the kind of information you're looking for?

-Gray

[James Rostron]

Hi @graycarper ,

Thank you for taking the time to answer my questions, it makes sense and was exactly what I was looking for.

Certainly, removing the active condition makes sense too and I guess that is just a preference. I too like the choice to include both active/inactive so have removed it aswell.

The last thing I need to do is create the widget in Step 9 of your original post but I'm afraid when I attempted this is looked nothing like yours 😂. I wondered if you could share some screenshots of the widget so I can replicate this too?

Many Thanks,

James

[graycarper]

Hi, @James Rostron! Gladly! Here's what my workbench widget configuration looks like...

As you can see, I added four Main Widget Indicators, and all the significant configuration elements of those Main Widget Indicators (Indicator, Label, and Order) are included in the columns displayed on that tab. This should give you something that looks very similar to the widget screenshot I included in my original post (though I've added the Yearly widget in the meantime 😁).

-Gray

[James Rostron]

I'm really grateful for all the help @graycarper 

We're undergoing a huge exercise to review out service catalog so this is such a big help and will get so much use from our stakeholders so many thanks for answering all my queries.

[James Rostron]

HI @graycarper ,

I have the widget working now but spotted the "daily" tab is not populating any data. Values are all 0.

Weekly and Monthly are working fine. I ran the script above in background and was getting data against some cat items and record producers (see below)

I guess the issue is somewhere else, would you have any idea?

Thanks,

James

[graycarper]

Hi, @James Rostron!

D'oh! Very odd indeed. The only possibility that jumps into my mind is that the daily Indicator Source isn't returning any Catalog Items, so the next thing I'd do is simulate the daily Indicator Source by opening the Catalog Items table (https://INSTANCENAME.service-now.com/now/nav/ui/classic/params/target/sc_cat_item_list.do) and adding filter conditions that match the conditions applied to the daily Indicator Source. As of your last screenshot, that was...

• Active is True
• Created at or before Today (or Created at or before a specific date you've already collected data for, like February 26th)

Do those filter conditions return any Catalog Items? If they do, and the list includes the Catalog Items you plugged into the background script, then that's not the problem. If the Catalog Items you plugged into the background script aren't returned (or if no Catalog Items are returned), then you've found the root cause. One or more filter conditions are removing legitimate Catalog Items from the data set, and that can definitely happen if - for example - the Catalog Items don't have Active set to 'true'.

If this doesn't fix the problem, you can also look at troubleshooting step #4 in my comment above at ‎03-07-2022 10:40 AM. Since you've already tried troubleshooting step #5, though, I suspect #4 will return results (and therefore not identify the culprit).

Beyond that, you might need to open a support request with ServiceNow so that they can look inside your instance to determine the cause.

I hope this helps!

-Gray

[James Rostron]

Thanks @graycarper I'll take a look at your points and hopefully get it working.