Query data from external database for use in catalog item form.

[kyleanderson]

Hello Everyone, 

I have been doing a bit of reading on this but cannot come up with a solid answer on how this can be done. I am looking to query a SQL server that exists outside of my service now instance, but lives on premise to my data center. What i am looking to do is use a script include somehow with JDBC, and grab data from a table on that SQL server, and return it to my client script so i can load a select box with that information. 

I've been going through the following post: 
http://www.john-james-andersen.com/blog/service-now/live-jdbc-queries-displayed-in-servicenow.html

i've copied pretty much all of the script include, and all of the client script, but for some reason it just keeps passing back null as the answer. 

Script Include:

var SQLUtil = Class.create();
SQLUtil.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	
	getAAgPrimaryServer:function(){
		var timeout = 60;
		var userName = "test";
		var dbpassword = "test";
		var midServer = "midservername";
		var driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		var serverOne = this.getParameter('sysparm_serverone');
		
		var sqlQuery = "IF SERVERPROPERTY ('IsHadrEnabled') = 1 BEGIN SELECT AGC.name,RCS.replica_server_name,ARS.role_desc,AGL.dns_name FROM sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id WHERE ARS.role_desc = 'PRIMARY' END";
		var connectionString = "jdbc:sqlserver://"+serverOne+";user="+userName+";password="+dbpassword+";";
		var xml = this._getQueryXML(driver, connectionString, sqlQuery);
		var eccQueueId = this._postQueryToEccQueue(midServer, xml);
		var eccResponse = this._waitForQueryResponse(eccQueueId, timeout);
		
		gs.log('determining if sysid ws found for eccresponse.');
		if(!eccResponse.sys_id){
			eccQueueId = 0;
		}
		else {
			var eccResultRecord = this._getResultRecordFromQueue(eccResponse, eccQueueId);
			this._insertResultsIntoCacheTable(eccResultRecord, eccQueueId);
		}
		return eccQueueId;
	},
	
	_getQueryXML:function(driver, connectionString, sqlQuery){
		gs.log('got into getQueryXML Function');
		var xml = '<?xml version="1.0" encoding="UTF-8"?>';
		xml = '<parameters>';
		xml += '<parameter name="jdbc_driver" value="'+driver+'"/>';
		xml += '<parameter name="connection_string" value="'+connectionString+'"/>';
		xml += '<parameter name="query" value="Specific SQL"/>';
		xml += '<parameter name="sql_statement" value="'+sqlQuery+'"/>';
		xml += '</parameters>';
		gs.log(xml);
		return xml;
	},
	_postQueryToEccQueue:function(midServer, xml){
		gs.log('got into postQueryToEccQueue Function');
		var ecc = new GlideRecord("ecc_queue");
		ecc.initialize();
		ecc.agent="mid.server." + midServer;
		ecc.topic="JDBCProbe";
		ecc.payload=xml;
		ecc.queue="output";
		ecc.state="ready";
		ecc.sequence = Packages.com.glide.util.Counter.next('ecc:sequence');
		ecc.insert();
		
		gs.log("JDBC Query ECC Queue New Sysid: " + ecc.sys_id);
		gs.log(ecc.sys_id);
		return ecc.sys_id;
	},
	
	_waitForQueryResponse:function(eccQueueId, timeout){
		gs.log('got into waitForQueryResponse Function');
		var resp = new GlideRecord("ecc_queue");
		resp.addQuery("response_to", eccQueueId);
		resp.addQuery("queue", "input");
		
		var counter = 0;
		do{
			resp.query();
			resp.next();
			gs.sleep(1000); //wait a second before trying again
			counter ++;
		} while(!resp.sys_id && counter < timeout);
		gs.log(resp);
		return resp;
	},
	
	_getResultRecordFromQueue:function(eccResponse, eccQueueId){
		gs.log('got into getResultRecordFromQueue Function');
		gs.log("Resp Sys ID: " + eccResponse.sys_id);
		gs.log("Response Payload: " + eccResponse.payload.replace(/\/</g, "&" + "lt;"));
		
		var eccRes = new GlideRecord("ecc_queue");
		eccRes.addQuery("agent", "JDBCProbeResult");
		eccRes.addQuery("topic", "JDBCProbe");
		eccRes.addQuery("queue", "input");
		eccRes.addQuery("state", "ready");
		eccRes.orderByDesc("sys_created_on");
		eccRes.query();
		while(eccRes.next()){
			var payload = new XMLDocument(eccRes.payload);
			var eccQuery = payload.getNodeText("//results/parameters/parameter[@name='ecc_queue']/@value");
			gs.log("eccQuery: " + eccQuery);
			gs.log(eccQueueId + "==" + eccQuery);
			if( eccQueueId == eccQuery ){
				break;
			}
		}
		gs.log(eccRes);
		return eccRes;
	},
	
	_insertResultsIntoCacheTable:function(eccResultRecord, eccQueueId){
		gs.log('got into insertResultsIntoCacheTable Function');
		gs.log("Inserting " + eccResultRecord.name + " SQL Results into Cache Table");
		var payload = new XMLDocument(eccResultRecord.payload);
		gs.log("Payload to Insert: " + payload);
		for(var i=1; i<=eccResultRecord.name; i++){
			var rec = new GlideRecord("u_related_alert_cache");
			var xpath = "//results/result/row[@id='" + i + "']/";
			rec.initialize();
			rec.u_roledesc = payload.getNodeText(xpath + "role_desc");
			gs.log("Role Description from database: " +  payload.getNodeText(xpath + "role_desc"));
			rec.u_ecc_id = eccQueueId;
			rec.u_alarmid = payload.getNodeText(xpath + "alarmID");
			rec.u_severity = payload.getNodeText(xpath + "severity");
			rec.u_node = payload.getNodeText(xpath + "node");
			rec.u_ipaddress = payload.getNodeText(xpath + "ipaddress");
			rec.u_summary = payload.getNodeText(xpath + "summary");
			rec.u_first = payload.getNodeText(xpath + "first");
			rec.u_last = payload.getNodeText(xpath + "last");
			rec.u_tally = payload.getNodeText(xpath + "tally");
			gs.log(rec);
			rec.insert();
		}
	},
	
	type: 'SQLUtil'
});

Client Script: 

function onChange(control, oldValue, newValue, isLoading) {
   if (isLoading || newValue == '') {
      return;
   }
		showLoadingDialog();
  		//call script include to get primary server
		var serverone = g_form.getValue('u_serverone');
		var servertwo = g_form.getValue('u_servertwo');
	
		console.log('calling to script include SQLUtil.getAAgPrimaryServer');
		var ga = new GlideAjax("SQLUtil");
		ga.addParam("sysparm_name", "getAAgPrimaryServer");
		ga.addParam("sysparm_serverone", serverone);
		ga.getXML(parseQueryResponse);
	
		function parseQueryResponse(response) {
			console.log('was able to get into parseQueryResponse');
			var answer = response.responseXML.documentElement.getAttribute("answer");
			hideLoadingDialog();
			alert(answer);
		}
		
}

I am banging my head, and am not sure why it won't return anything. I am pointing to the name of my mid server as it shows up under MID servers in the CMS. I am using a username and password that have access to the SQL servers themselves. Any help would be greatly appreciated!

[DaveHertel]

Hi -- Since the SQL box is within your organization, seems like a much easier approach would be to do an import job... with the import process set to connect to your SQL box, get data, tranform it (if needed) and put it in some temporary table on your instance.   now that the data is replicated into your instance, just refer to it from whatever internal SNOW processes you are building.

Maybe I'm oversimplifying things, but this would be my 1st approach.... get the data natively replicated to SNOW where its easy to consume.  My 2 cents.

Hope this helps?

[kyleanderson]

My understanding is that if I use a table within service now i incur cost. i don't want to incur a cost for throwing the data into a service now table if i really don't have to do that. 

[DaveHertel · Accepted solution]

Hi - theres also a real financial cost to building a custom solution that A) has to be developed/debugged  B) maintained over the long term C) documented  D) eventually transferred to someone else (after you move on... win the lottery... etc 🙂 ) 

What you are trying to do could be built with simple integration in under an hour and then the process is also using native capabilities which are relatively simple to understand by those who follow after you...  my 2 cents.

Hint:  CMDB tables that are subclasses don't incur that licensing cost you referred too.  Something to ponder...................

[Abhay Somani]

Have you checked the writes of users which run Mid server services in the machine where the Mid server is installed?  This user should have access to the database where you are trying to run the query. Moreover, please check the port. 

The Mid Server machine should port open to access SQL server and also in your connectionString the port details are missing.