How to run a query to find which Requests are creating RITM's without having managers approval

[johnedwards]

This is my first discussion and I am new to this area of Service now.   We are currently using Helsinki version.

Our process:

All requests submitted by our users must have the Manager's Approval prior to creating the RITM.   If deemed necessary by the application owners, the RITM must then be approved by the application owner prior to creating the SCTASK.

Problem:

We have some Catalog Items that are creating RITM's without the required approvals.   Can anyone provide a method for me to Find all RITMs that have been created without first being approved?

This process was provided to me as a possible solution but with my lack of experience, I do not understand how to follow it.

1. From the Request Item, select all RITM's where the ITEM= "insert service" and created between "insert date range" (or exclude if you want all), and State=Closed Complete.   Then export to Excel.

2. From the Sys Approval Approver Table, select all records where the Approval For.Request_Item.Item= "insert service" and Approval For.Request_Item.Created between "Insert date range" (or exclude if you want all), and Approval For.Request_Item.State=Closed Complete and SysApproval record state = Approved. Then export to excel.

3. Do a VLOOKUP from List 1 (RITM#) against List 2 (Approval For Number) to find a related approval record.   Items in List 1 that have no matching approval record in List 2 are problematic.

I have never attempted to run a query and do not understand how to follow the process provided.   Can someone help me by explaining how this query is ran?   or maybe an even better / more accurate process?

[sethivarun]

Hi John,

Please use the following

var gr = new GlideRecord('sc_request');
gr.query();


while(gr.next()){
var gritem= new GlideAggregate('sc_req_item');
gritem.addQuery('request',gr.sys_id);
gritem.addAggregate('COUNT');
gritem.query();
var totalItems= 0;
if(gritem.next()){
totalItems= gritem.getAggregate('COUNT');
}
if(totalItems>0)
                               {var reqApprover = new GlideRecord('sysapproval_approver');
                                 reqApprover.addQuery('document_id',gr.sys_id);
                                 reqApprover.query();
                             if(!(reqApprover.next())){
                                 gs.print(gr.number);
                                 }}}

Please mark as correct or helpful based on the impact

[sethivarun]

please use the above script in scripts-background