JDBC Probe to insert/update records into sql database table from ServiceNow

[Service Manager]

Hi,

I was trying to insert/update the record via JDBC probe into SQL database table from ServiceNow. Below script was working as expected 

var mid = new GlideRecord("ecc_agent");

mid.addQuery('name','MID_Serverxxx');

mid.query();

if(mid.next()){

}

var data = new GlideRecord("sys_data_source");

data.addQuery('name','xxname of datasourcexx');

data.query();

if(data.next()){

}

var r1 = new JDBCProbe(mid.name);

r1.setDataSource(ds.sys_id);

jr1.setFunction("INSERT");

r1.setTable("xxdatabase tablexx");

r1.addParameter("skip_sensor","true");

r1.addField("Number", "INC7845129");

r1.addField("Contact", "david");

r1.create();

 But when I updated to below script to insert the records from target table "Incident", tickets assigned to Service desk group. It throws me an error

r1.addField("Number", current.number);

r1.addField("Contact", current.caller);

r1.addField("opened",current.opened_at);

r1.create();

Thanks

[Service Manager]

I am writing it as Background script and would be putting in scheduled job later

My previous query was working via background script and I wanted to know why isn't working when I wanted to fetch actual data from incident table and insert it into SQL database.

Thanks

[Tanaji Patil · Accepted solution]

Your old script was working because you were not using current object there. You had static values (INC7845129 and david).

Now it's not working because current object is not available on scheduled jobs or background scripts.

current object is a GlideRecord object representing records in table and is available in server side scripts directly acting on tables (business rule, ACL, workflows).
For more information check-
https://community.servicenow.com/community?id=community_question&sys_id=a6d62986db7d1b005322f4621f9619dd#:~:text=Current%20object%20refers%20to%20current%20table.,refers%20to%20an%20incident%20table.

So you need to do a GlideRecord on incident table and then use that gliderecord object to dot walk to incidents fields.

For e.g. following script will give you all active incidents. gr is the gliderecord object-
var gr = new GlideRecord("incident");
gr.addQuery("active","true");
gr.query();
while(gr.next()) {
// Here you have gr as GlideRecord object representing each record
// So gr.caller_id.name will give you callers name on incident
}

For more information of GlideRecord check-
https://www.servicenowguru.com/scripting/gliderecord-query-cheat-sheet/

-Tanaji
Please mark reply correct/helpful if applicable

[Service Manager]

I tried with below code but it didn't help me yet. 
every time I execute via background script it create null records including Number and name

var mid = new GlideRecord("ecc_agent");
mid.addQuery('name','XX-mid-server-');
mid.query();
if(mid.next()){
}
var data = new GlideRecord("sys_data_source");
data.addQuery('name','--datasource--');
data.query();
if(data.next()){
}
var gr = new GlideRecord("incident");
gr.initialize();
gr.addQuery("assignment_group","service desk");
gr.query();
while(gr.next()){
gr.caller_id.name = 'contact'
gr.number = 'number'
}
var r1 = new JDBCProbe(mid.name);
r1.setDataSource(ds.sys_id);
r1.setFunction("INSERT");
r1.setTable("Incidentrecords"); // database table name
r1.addParameter("skip_sensor","true");
r1.addField("Number", gr.number);
r1.addField("Contact", gr.caller_id.name);
r1.create();

[Service Manager]

Tanaji,

It helped but I wasn't able to insert fields such as "cmdb_ci, assignment group, assigned to,close notes, close code and resolved by from Incident(ServiceNow) to SQL database . I would like to update the existing records also.

Thanks

[Tanaji Patil]

Add these line to your existing code before r1.create()-

r1.addField("cmdb_ci", gr.cmdb_ci.getDisplayValue());
r1.addField("assignment_group", gr.assignment_group.getDisplayValue());
r1.addField("assigned_to", gr.assigned_to.getDisplayValue());
r1.addField("resolved_by", gr.resolved_by.getDisplayValue());
r1.addField("close_code", gr.close_code.getDisplayValue());
r1.addField("close_notes", gr.close_notes);

Here I am assuming-

• gr is your gliderecord object of incident
• cmdb_ci, assignment_group, assigned_to, resolved_by, close_code and close_notes are present on the DB table

-Tanaji

Please mark response correct/helpful if applicable