Where can I run queries?

zsquared · ‎10-27-2016

Hello,

I am new to ServiceNow. I understand how GlideRecords work in querying data in Transform Maps but I am trying to run a query off

a table in the CMDB and record those records in a table I created.

What is the process for this? For example, if I wanted to get a count of records of the CMDB and wanted to put the result in Table XYZ.

Thank you in advance for any advice!

drjohnchun · ‎10-27-2016

Hi Zhen - you'll have to run a server-side script like the examples below; where you run the script is up to the requirement and it may be in Business Rules, Scheduled Jobs, etc. Can you describe exactly what you're trying to achieve?

// first get the row count

// for row count, use GlideAggregate instead of GlideRecord

// http://wiki.servicenow.com?title=GlideAggregate

var cmdb = new GlideAggregate('cmdb_ci');   // use the table name of the CI type you want

cmdb.addAggregate('COUNT');

cmdb.query();

var count = 0;

if (cmdb.next()) count = cmdb.getAggregate('COUNT');


// insert the row count to table xyz, column count

// http://wiki.servicenow.com?title=GlideRecord#Insert_Methods

var gr = new GlideRecord('xyz');

gr.initialize(); 

gr.count = count; 

gr.insert();


// or update existing record's column count in table xyz

// http://wiki.servicenow.com?title=GlideRecord#Update_Methods

var gr = new GlideRecord('xyz');

gr.addQuery();   // add a query to filter the row(s) to update

gr.query();   // this may return 0 or more records

while (gr.next()) {   // update all records

   gr.count = count;

   gr.update();

}

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

View solution in original post

drjohnchun · ‎10-27-2016

Hi Zhen - you'll have to run a server-side script like the examples below; where you run the script is up to the requirement and it may be in Business Rules, Scheduled Jobs, etc. Can you describe exactly what you're trying to achieve?

// first get the row count

// for row count, use GlideAggregate instead of GlideRecord

// http://wiki.servicenow.com?title=GlideAggregate

var cmdb = new GlideAggregate('cmdb_ci');   // use the table name of the CI type you want

cmdb.addAggregate('COUNT');

cmdb.query();

var count = 0;

if (cmdb.next()) count = cmdb.getAggregate('COUNT');


// insert the row count to table xyz, column count

// http://wiki.servicenow.com?title=GlideRecord#Insert_Methods

var gr = new GlideRecord('xyz');

gr.initialize(); 

gr.count = count; 

gr.insert();


// or update existing record's column count in table xyz

// http://wiki.servicenow.com?title=GlideRecord#Update_Methods

var gr = new GlideRecord('xyz');

gr.addQuery();   // add a query to filter the row(s) to update

gr.query();   // this may return 0 or more records

while (gr.next()) {   // update all records

   gr.count = count;

   gr.update();

}

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

zsquared · ‎10-27-2016

Thank you John!! I really appreciate the code and the comments on it!!!!!!!

On Thursday, October 27, 2016, drjohnchun <community-no-reply@servicenow.com>

zsquared · ‎10-27-2016

Just one quick question. Where do I put these scripts exactly? Let's say

everytime a new record is added into the cmdb, I want my table xyz to also

update the count calculation.

On Thursday, October 27, 2016, drjohnchun <community-no-reply@servicenow.com>

Deepak Kumar5 · ‎10-27-2016

You can put in any server side script. Mostly we use Business Rule

http://wiki.servicenow.com/index.php?title=Business_Rules

You can check this for server and client side script places.

http://wiki.servicenow.com/index.php?title=Differences_Among_Scripts