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:
- Set up the Database Connection string
- Generate a JDBC Probe XML Payload that represents the database query
- Post the query to the ECC Queue
- Wait for a response record to come into the ECC Queue
- Insert the results into the temporary caching table
- Return the ECC Record ID as the key for the client to query the corresponding results
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.
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:
- Show a loading dialog box to give the user a sense that something is happening
- 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:
- Hide the Loading Dialog box
- 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(); } |
Awsm Stuff sir !!!!
Please put some topics about workflows….
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!
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.
Hi John
great design. Had a question, how do we handle return record number? By default this gets only up to 200 right? Do we extend jdbcprobe_result_set_rows to maximum when rows are in thousands?
Appreciate your help
Sam
@Sam – Yes, you can either change the property (which is global – so make sure that is what you want), or you can design the query so that you can paginate the results and build the list before displaying it.
John, I have customer that wants to do something similar to this but update fields on an incident record based on the results. Is this possible and how would that be done?
@Joe, I beleive there is a way to do this, and I have been meaning to blog about it. I have some rough notes, so if you are still interested, shoot me an email and I will get you the notes for you to piece together.
ecc.sequence = Packages.com.glide.util.Counter.next(‘ecc:sequence’);
Line doesnt work. Is there anything changed in to Istanbul?
I have a similar request and i cant get it to work past this var eccQuery = payload.getNodeText(“//results/parameters/parameter[@name=”ecc_queue”]/@value”);
it will not save the script
The quotes round the ecc_queue need to be escaped on line 90 of the script incude:
var eccQuery = payload.getNodeText(“//results/parameters/parameter[@name=\”ecc_queue\”]/@value”);
This is great! I am trying to modify your script include to instead of inserting records in a table, to simply pull back a response for me to determine if a value entered is unique.
Using your tutorial I am able to get the client script and script include to speak to each other. However, I am missing something on the script include. I can’t seem to execute the query to SQL, and return a result to the client.
Is there any chance you can help me modify your script to do this?
Hi John ,
I have similar requirements and follow the same process but unable to connect oracle database.
Could you please help me with oracle database connection strings.
var connectionString = “jdbc:oracle:thin:@//elocalhost:1521:WSMSAD\””+”,”+”\”IDM\””+”,”+”\”Pass@for2020″;
_getQueryXML:function(driver, connectionString, sqlQuery){
var xml = ”;
xml = ”;
xml += ”;
xml += ”;
xml += ”;
xml += ”;
xml += ”;
return xml;
},