Where can I run queries?

zsquared
Tera Contributor

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!

1 ACCEPTED SOLUTION

drjohnchun
Tera Guru

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 see John's LinkedIn profile


visit snowaid


View solution in original post

9 REPLIES 9

So I did a sample business rule and wanted to insert a value of 2 into a field every time a user displayed my table: u_sample_zz_table


I put the condition as onDisplay assuming that every time a user displays the table, a value of 2 will be inserted into the table column: u_integer_2



I used the following script:





var sampleData = new GlideRecord("u_sample_zz_table");


sampleData.initialize();


sampleData.u_integer_2 = 2;


sampleData.insert();



I have attached a screenshot of the fields.   Am I missing something in my logic of this process?



Please let me know!!




screenshot.PNG


var sampleData = new GlideRecord("u_sample_zz_table");


var views =0;


views = current.getValue('u_integer_2') +2;


current.setValue('u_integer_2',views);


sampleData.update();


Deepak Kumar5
Kilo Sage

You have to query the table from where you want data to retrieve then query on target table for the record and then update that record.


Like below query the incident table for the all active record then put that in a cmdb table record named "access"



var count = new GlideAggregate('incident');


count.addQuery('active', 'true');


count.addAggregate('COUNT');


count.query();


var incidents = 0;


if (count.next())


  incidents = count.getAggregate('COUNT');



var accessupdate = new GlideRecord('cmdb_ci');


accessupdate.addQuery('name', 'access');


accessupdate.query()


while (accessupdate.next()){


                  accessupdate.total_count = incidents;   //update the total_count field of access record of CMDB


                    accessupdate.update();


}



More details about the query function.


https://wiki.servicenow.com/?title=GlideRecord#gsc.tab=0


Thank you so much!! This answer is so helpful and I am forever grateful. My


head has been spinning to try to pick up this tool for a client!!!!!



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


You can use Wiki for more details about Service now funcationlity.


http://wiki.servicenow.com/index.php?title=Main_Page#gsc.tab=0



For your testing/ Learning purpose, You can register and use a new personal service-now instance.


https://developer.servicenow.com/app.do#!/home