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!

[ARG645]

Do you get valid results when you run the same SQL query directly in the SQL server ?

[kyleanderson]

Hey Aman, 

Yes, When executing the query directly on the SQL server. I receive a query response telling me if the server that i run it on is primary in the AAG or not. 

[Diane22]

I am trying to have a similar outcome: using an intake form (workflow) to request access => when the user enter a name, onload the training status shows up real time (not registered, in progress, complete).  Training data is a different system, on a different server.  Couldn't a REST API or the midserver be used in order to get that outcome without complex scripting? this would be highly valuable and release capacity from resources who need to validate training completion manually for each request.

[xjxx]

Did you manage to do such things?