Date catalog item was last ordered

[User175049]

Hello

I am looking for a way to find out when items of a particular Service Catalog (we have multiple) were last ordered. This would be used to help us determine whether a SC Item should remain in the catalog or removed due to lack of need.

Thank you,

M

[Kristen Ankeny]

I would run a script against the sc_req_item table to do this. If you want this to be a regular evaluation, you could create a scheduled job that ran once a month that did the query and determined which haven't been submitted in X months, and produce an email or task for follow up.

[karan15]

thanks , can you please help with some screen shots or steps to set up this script ?

[Oliver Stammler · Accepted solution]

Hey M,

as far as I understood you only need the date so that you can manually deactivate the corresponding catalog items or not, right? 

In this case it is not necessary to write a script, a simple report is enough.
Just create a new report with the source type "Table" and the table "Requested Item [sc_req_item]" (see report1.png attached). Now add the "Created" field to the list in the "Configure" menu item using the "Choose columns" button. (see report2.png attached)

Now you can simply use the filter in the generated report and search for your item.
Via the field "Created" you can now see when the item was last ordered, as you can see in the last screenshot attached.

If I got you wrong and you still need a script, you can use this one:

var grItemsOrdered = new GlideRecord('sc_req_item');
grItemsOrdered.addQuery('cat_item', 'ADD YOUR SYS_ID HERE');
grItemsOrdered.orderByDesc('sys_created_on');
grItemsOrdered.setLimit(1);
grItemsOrdered.query();

if(grItemsOrdered.next()){
gs.print(grItemsOrdered.sys_created_on);
}

Just add your sys_id where I marked it and you can run this script as a background script for example.

Best regards
Oli

[User175049]

Thank you!