Using Script Includes to filter a report

[shazbot79]

I want to be able to filter reports using the service subscriptions for a user (tables: 'service_subscribe_company', 'service_subscribe_location', 'service_subscribe_department', 'service_subscribe_sys_user' and 'service_subscribe_sys_user_grp'.)

I want to be able to use something similar to javascript:getMyGroups() in a filter when building a report. I have found a Script Includes called ServiceSubscriptionsUtils which seems to loop through the 5 tables and builds an array with all of the service offerings. I want to tap into that array and use that to filter my reports but I have no idea how to reference a Script Includes.

Can anyone give me any clues? I've tried javascript:getMySubscriptions() but I'm guessing it doesn't work with Script Includes....I'm a bit new so apologies if the answer is obvious!!

Thanks in advance.

[Marcus Fly]

There is two ways to do this:

Option 1:
Create a database view:
http://wiki.servicenow.com/index.php?title=Database_Views

Option 2:
In my example I used the CMDB and took advantage of the CMDB Hierarchy. I wanted to know which servers (All servers including child tables) did not have a relationship to an application. Not what your trying to accomplish but same principal can be applied.

Here are the steps to accomplish it:
1: Created a script include with same name as your function call that returns an array of sys_id's valid on the table

function yourScriptIncludeHERE() {
   var sa = new Array();
   var sr = new GlideRecord('cmdb_ci_server');
   sr.query();
   // Loop thru all servers
   while(sr.next()){
      var relR = new GlideRecord('cmdb_rel_ci');
      relR.addQuery('parent', sr.sys_id);
      relR.addQuery('child.sys_class_name', 'cmdb_ci_appl');
      relR.query();
      // Add to array only if application NOT found
      if (!relR.next()){
         /// Check for reverse relationships (where server is child)
         var relChild = new GlideRecord('cmdb_rel_ci');
         relChild.addQuery('parent.sys_class_name', 'cmdb_ci_appl');
         relChild.addQuery('child', sr.sys_id);
         relChild.query();
         if (!relChild.next()){
            //gs.print(sr.name + ' added');
            sa.push(sr.sys_id.toString());
         }
      }
   }
   // Return the Array object
   return sa;
}

2: Create report and set filter like this:
Sys ID is javascript:yourScriptIncludeHERE();

3: Sit back relax and wait for the results 🙂
4: Make any additional filters to the results

Please keep in mind this will affect performance and takes a while to run. Hope this helps.

[stuboasman]

Hey Marcus, not sure if you are still out there, but this is exactly what I was looking for in https://community.servicenow.com/message/1118819?et=watches.email.thread#1118819.

Can I ask if there is a way to address the opposite side of the same question?   I am trying to build CMDB verification reports and two reports have been particularly difficult to deal with:

1. Report on servers that do not have a listed relationship to an application/Web Site so we don't know what is running on it.   This one is the one you have solved beautifully above and I now have the working report.
2. Report on servers that do have such relationships that show the related applications in the report.   I have built a script include that returns an array of the related applications, I was going to use the .length array function for the first report where length is zero but I like the way you handled it better.   For this second report I wanted to take the resultant array and have that populate a column in the report or event have the report list one row for each server/application.   Is there a way to accomplish this?

Any advise or help would be greatly appreciated.

Stu Boasman

[Andras2]

Where do you put the function body? Where do you save it?

[Andras2]

Where do you put the function body? Where do you save it?