A question came up on the ServiceNow Developer Discord recently.
"How to find only Catalog Items created from Catalog Builder"
TL;DR (SCROLL DOWN TO SEE THE R&D JOURNEY)
To test this theory I used two records
1) A Catalog Item I created via Catalog Builder.
2) A Catalog Item I updated (but did NOT create) via Catalog Builder.
Out objective was to generate a list that contains (1) but not (2)
It turns out Catalog Builder forces you to use a Template. These templates are stored in their own table dedicated to Catalog Builder. The solution, astoundingly, is as simple as looking for sc_cat_item records that have a not-null reference on "Associated Template [sc_template]".
BEFORE
A simple list of Catalog Items. The two in the orange box were touched by Catalog Builder. You can tell because they have a visible Status. They also have Catalog Builder Analytics related records (more on that in the "Journey" section)
AFTER
Same list of Catalog Items but filtering out empty Associated Template values.
Unexpectedly, there's at least one OOB Catalog Item that seems to have been created in Catalog Builder.
If you're looking exactly, specifically for your own builds, you may want to filter out created-by/updated-by "admin".
Solution ended up being way way easier than I anticipated. If you want to learn a bit about exploring ServiceNow, theorizing and testing results, read below to see the journey down the rabbit hole.
-----===== GOING DOWN THE RABBIT HOLE =====-----
- Created two Catalog Items, one manually, one through Catalog Builder.
- Inspected XML of both records to see if anything reliably pointed to a Catalog Builder creation. (Future Rob: "but I didn't clue into "Associated Template"
- Asked ChatGPT, learned about sys_catalog_metadata... only to later learn this isn't accessible to mere mortals.
- Used Xplore Table Hierarchy and searched for all tables that contained "Catalog". Check out this video if you've never used Xplore for that purpose. Found a promising one!
- Learned Catalog Builder Analytics stores telemetry data on people's interactions with Catalog Builder. The important properties for our dilemma are Action Type and Catalog Item.
- Learned Catalog Builder Analytics contains the following properties.
- Learned important lesson: "Item Submitted from CB" does NOT mean "Created via CB". It means is that you hit the Submit button in CB which is true for both updates, and creates.
- Looked closely at Catalog Builder Analytics table. "Give me something, now" was not created in CB, but it was modified. "Testing Catalog Builder" was built and had several updates in CB.
- So we can count the Catalog Items created OR updated in Catalog Builder this way, but not pure creations.
- But HEY! If its true that both an insert and an update in Catalog Builder produce Catalog Builder Analytics of "Item submitted from CB", then you should always have 1 more of those type of records than "X edited from CB".
- "Hey chat GPT, build me a script that subtracts update records from insert-or-update records and return me all catalog items that only have 1 entry left in Catalog Builder Analytics.
// Its super complicated AND it works
// Object to track counts per Catalog Item sys_id
var itemCounts = {};
// Step 1: Query relevant action types
var analyticsGR = new GlideRecord('catalog_builder_analytics');
analyticsGR.addQuery('action_type', 'IN', 'item_submitted_cb,item_edited_cb,rp_edited_cb');
analyticsGR.query();
while (analyticsGR.next()) {
var itemId = analyticsGR.getValue('cat_item');
var action = analyticsGR.getValue('action_type');
if (!itemCounts[itemId]) {
itemCounts[itemId] = { submitted: 0, edited: 0 };
}
// Normalize action types for logic
if (action === 'item_submitted_cb') {
itemCounts[itemId].submitted++;
} else if (action === 'item_edited_cb' || action === 'rp_edited_cb') {
itemCounts[itemId].edited++;
}
}
// Step 2: Select items where (submitted - edited == 1)
var qualifyingIds = [];
for (var id in itemCounts) {
var counts = itemCounts[id];
if ((counts.submitted - counts.edited) === 1) {
qualifyingIds.push(id);
}
}
// Step 3: Query Catalog Items with those sys_ids
if (qualifyingIds.length > 0) {
var catItemGR = new GlideRecord('sc_cat_item');
catItemGR.addQuery('sys_id', 'IN', qualifyingIds.join(','));
catItemGR.query();
while (catItemGR.next()) {
gs.info('Catalog Item created from Catalog Builder: ' + catItemGR.getDisplayValue('name'));
}
} else {
gs.info('No qualifying Catalog Items found.');
}
And 15 minutes after that, I discovered it works just as well to filter blank entries on "Associated Template"
