Live JDBC Queries Displayed in ServiceNow

Jan 26th, 2011 | 4 responses

I recently had a requirement to provide a button on the incident form in service-now that, when clicked, would make a live, on-the-fly query to an external database, and display the results in a dialog box that could be closed after viewing. The query would be based on the incident number. Also, we had to display a dialog to the user that were were processing their request since there would be a delay between button click and response from the external database.

The following video below is a quick demo of what I will build out in this blog entry.

For this example, I used a MID Server that had access to a MySQL database. In order for us to harness the power of a ServiceNow list gadget for the results, I had to create a caching table for the results from the database. This caching table should have a scheduled job that cleans the table every day (or deletes entries that are older than X hours).

The general architecture of the integration consists of the following components:

  • Form Button – UI Action (Client Script)
  • Script Include (Server Side Script)
  • Caching Table
  • ECC Queue
  • MID Server (Customer Network)
  • External Database (Customer Network)

The flow between these components can be found in the following diagram (click on the image to get the full resolution version):

Let’s talk about the components of the integration that had to be set up on the ServiceNow side.

Caching Table

The caching table schedule is essential the same schema as the fields that are returned from the SQL query, plus an additional field where we store the ECC Queue Request Sys ID. This field will store the initial JDBC Probe query record sys_id from the ecc_queue table. We will use this ID to let us know which results to display to the end user in our dialog box.

I also personalized the List for the caching table so that the list is not editable, but display only. The user will be able to sort and filter on the results, but they will not be allow to change the results in the table.

Script Include: RelatedAlerts

Please note, to understand the next few sections of this blog entry you may want to refer to the GlideAjax wiki page for ServiceNow.

The Script include has the following properties:
Name: RelatedAlerts
Active: True
Client Callable: True
Description: Class used for Client Scripts to call and trigger a JDBC query on the related alerts database

Now lets get into the nitty gritty portion of the script. I am going to divide it up a bit and explain the methods in the class and their inner workings.

First, we need to create the RelatedAlerts class. In order for it to be called asynchronously from a client script, the class will need to extend from the AbstractAjaxProcessor class.

1
2
3
4
5
RelatedAlerts.prototype = Object.extendsObject(AbstractAjaxProcessor,{

//All class method declarations will go here

});

The method that is going to drive the entire query process will be called “executeQuery”. In this method, we will take the following steps:

  1. Set up the Database Connection string

    2. Connection strings are dependent on the type of Database you are connecting to. To view these formats, check out the wiki page on the Direct JDBC Probe.

  2. Generate a JDBC Probe XML Payload that represents the database query
  3. Post the query to the ECC Queue
  4. Wait for a response record to come into the ECC Queue
  5. Insert the results into the temporary caching table
  6. Return the ECC Record ID as the key for the client to query the corresponding results

The following is the code for the Script Include as it follows the above steps.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
var RelatedAlerts = Class.create();

RelatedAlerts.prototype = Object.extendsObject(AbstractAjaxProcessor,{

executeQuery:function(){
  var timeout = 50;

  //Database Information
  var driver = "com.mysql.jdbc.Driver";
  var dbServer = "mysql.myserver.com";
  var dbPort = "3306";
  var database = "snctestdb";
  var dbUser = "snctester";
  var dbPassword = "sncrocks";
  var connectionString = "jdbc:mysql://"+dbServer+":"+dbPort+"/"+database+"?user="+dbUser+"&"+"amp;password="+dbPassword;

  //ECC Queue Information
  var midServer = "MID_MAIN1";
 
  var incNumber = this.getParameter('sysparm_incident_number'); //This comes from the UI Action client script
  var sqlQuery = "SELECT incidentNum,alarmID,severity,node,ipaddress,summary,first,last,tally FROM related_alerts WHERE incidentNum='"+incNumber+"'";
 
  gs.log("incNumber: " + incNumber);

  var xml = this._getQueryXML(driver, connectionString, sqlQuery);

  var eccQueueId = this._postQueryToEccQueue(midServer, xml);

  var eccResponse = this._waitForQueryResponse(eccQueueId, timeout);
 
  if(!eccResponse.sys_id){
    eccQueueId = 0;
  } else {
    var eccResultRecord = this._getResultRecordFromQueue(eccResponse, eccQueueId);
    this._insertResultsIntoCacheTable(eccResultRecord, eccQueueId);
  }
  return eccQueueId;
},
_getQueryXML:function(driver, connectionString, sqlQuery){
  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>';
  return xml;
},
_postQueryToEccQueue:function(midServer, xml){
  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);
  return ecc.sys_id;
},
_waitForQueryResponse:function(eccQueueId, timeout){
  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);
  return resp;
},
_getResultRecordFromQueue:function(eccResponse, eccQueueId){
  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;
    }
  }
  return eccRes;
},
_insertResultsIntoCacheTable:function(eccResultRecord, eccQueueId){
  gs.log("Inserting " + eccResultRecord.name + " 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_incidentnum = payload.getNodeText(xpath + "incidentNum");
    gs.log("IncidentNumber From database: " +  payload.getNodeText(xpath + "incidentNum"));
    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");
   
    rec.insert();
  }
}
});

Related Alerts UI Action

The properties for the UI Action are as follows:

  • Name: Related Alerts
  • Table: Incident
  • Client: True
  • Form Button: True
  • Onclick: loadItUp();

The steps taken by this script are as follows:

  1. Show a loading dialog box to give the user a sense that something is happening
  2. Make an asynchronous Ajax call to the server-side script include defined above. We also send the incident number since the query we have requires that value.

When the asynchronous call is complete, it will then do the following to the active form:

  1. Hide the Loading Dialog box
  2. Display a GlideDialogWindow dialog showing the list of query results. I used this article on GlideDialogWindow to get this list to show up right.

Here is the script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
//On the button click, show "loading" dialog and query external database
function loadItUp(){
  //Show a loading dialog box so that people will know that we are busy
  showLoadingDialog();

  //Set up the Server-Side function call to query the external database
  var ga = new GlideAjax('RelatedAlerts'); //Script Include Name
  ga.addParam("sysparm_name", "executeQuery"); //Function in the Script Include
  //Send the Incident Number for the query
  ga.addParam("sysparm_incident_number", g_form.getValue('number'));

  //Make an asynchronous Ajax call and the process the response in the callback function
  ga.getXML(parseQueryResponse);
}

//Parses the asynchronous ajax response and show results as a dialog window
function parseQueryResponse(response){
  var answer = response.responseXML.documentElement.getAttribute("answer");
 
  //Hide the loading dialog so that the form will be accessible again
  hideLoadingDialog();

  if( answer == "0" ){
    //We didn't get a response, push out an error.
    alert("The Query Timed Out.  A MID-Server is likely down.  Please contact a System Administrator.");
  } else {
    //Show a Glide Dialog Window as a popup over the form
    showMajorIncidents(answer);
  }
}

//Show the cached list in a GlideDialog popup window for the resulting cache id (qid)
function showMajorIncidents(qid) {
  //Initialize the GlideDialog window
  var w = new GlideDialogWindow('show_list');
  w.setTitle('Related Alerts');
  w.setPreference('table', 'u_related_alert_cache_list');
  w.setPreference('sysparm_view', 'default');

  //Set the encoded query for the list
  var query = 'u_ecc_id=' + qid;
  w.setPreference('sysparm_query', query);
   
  //Open the popup
  w.render();
}
0saves
Save
If you enjoyed this post, please consider leaving a comment or following John Andersen on Twitter.

4 Responses to “Live JDBC Queries Displayed in ServiceNow”

  1. gupta says:
    January 27, 2011 at 6:29 am

    Awsm Stuff sir !!!!

  2. gupta says:
    January 27, 2011 at 6:30 am

    Please put some topics about workflows….

  3. Martin says:
    February 2, 2011 at 9:29 am

    That’s pretty cool John. Good work. How long on average does the user wait for? I suppose for a future enhancement, you could display the cached data, but I suppose it changes quite frequently. I like!

  4. John Andersen says:
    February 2, 2011 at 9:32 am

    Martin, the user probably waits about 10 seconds for the response. I thought about making it a true cache, but it was not in the requirement (they wanted live data). I would expect someone would want to cache it in a different scenario.

Leave a Reply

Subscribe to My ServiceNow Blog

Enter your email address:

Delivered by FeedBurner