If you’ve ever worked with any kind of integrations within ServiceNow, you are probably aware of the term MID Server. It’s a very powerful Java
application installed inside customer’s network on dedicated server. It is usually used as a proxy between ServiceNow instance and any database you would want to load the data from.
As I have learned recently, you can also use it to send direct SQL
commands to the database. That’s cool! First, let me explain you the use case that lead me to this post.
Use case
Imagine you have a database you want to connect to. This database is updated every hour with new records coming from… somewhere, it’s not important to know. Customer wants you to load this data into the platform, pretty basic requirement. But he also asked you to DELETE the data you have already loaded.
When I first heard about it, I was thinking that it cannot be done that easily. I was aware that in older releases we could use orchestration to run some Powershell
scripts, and these scripts probably could delete something. But it’s deprecated and I don’t know with what it was replaced. We are still in a meeting, I am considering my options, and suddenly my colleague jumps in excitement with some scary words Of course, we can do it. I spent the rest of the meeting thinking about how and why. It was pretty obvious to me that either I was missing something or that he is a lunatic. Thankfully, it was the first case.
He told me that we can use MID server
to run SQL
queries directly on the database. When I was just a beginner in this world (Helsinki release), I heard rumors that there is a hidden and undocumented API to do that from the platform. I still havem’t found it, but now I know the super powers of MID server
.
The solution
The solution, in general, is really simple:
- Load the data using an import set
- Run your transformations
- Use
MID server
to delete records from the table withonComplete
transform script
I’ll be honest, I was lucky to have him on my team. He’s developed a really nice piece of code in the past to do similar things – he wasn’t deleting the records, just updating them through ECC queue
.
There must be an out-of-the-box way to send commands to the MID
, right? That’s how the data load is processing the SQL
statements you provide. And yes, there is – global.JDBCProbe
. There is even a sample code in the description:
var j = new JDBCProbe('davidloo7da2-274');
j.setDriver('com.sybase.jdbc3.jdbc.SybDriver');
j.setDriverJar('C:\\Progra~1\\IBM\\Tivoli\\Netcool\\omnibus\\java\\jars\\jconn3.jar');
j.setConnectionString('jdbc:sybase:Tds:10.10.11.112:4100/NCOMS?user=xxx%26password=xxx');
j.setFunction("select");
j.setTable("alerts.status");
j.create();
Interesting lines:
- Line 1 creates a new instance. What’s the parameter? It’s the name of the
MID
which should be used - Line 5 sets the function to be used. You can see
select
, but you can use anySQL
command, includingdelete
- Line 6 sets the
SQL
table on which our command will be used
Now, the question is – how do I add a WHERE
clause to the delete
? Scroll down in the API and you will see a method for this:
setWhereClause: function(w) {
this.whereClause = w;
}
Is it really that simple? Sending the command – yes. Processing the result – not so much, but still doable.
Development
I won’t give you the whole script include to handle your own SQL
queries through MID server
. Just some hints to help you get started:
- Create a script include in
global
scope - Have a variable which is an instance of
JDBCProbe
class - Create separate method for different
SQL
operations - And finally, have a separate method to manage
ECC queue
The ECC queue
management is the tricky part. You may have already noticed that the JDBCProbe.create()
method returns the sys_id
of the output
queue message. You have to monitor this record until it’s state changes to processed
. Using GlideRecord('ecc_queue')
and gs.sleep()
turns out to work well.
But how to read the response? You first have to understand that it will be send to the input
queue and will have the same name as the output
message. It will be created by MID server
, so a slight delay should also be expected.
Once you receive the response, the payload
field contains an XML
, which could look like that:
<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="221" result_code="0">
<result>
<deleteResult>7 rows deleted</deleteResult>
</result>
<result/>
<parameters>
<parameter name="agent" value="agent.name"/>
<parameter name="signature" value=""/>
<parameter name="response_to" value=""/>
<parameter name="work" value="<?xml version="1.0" encoding="UTF-8"?><work><DELETE table="TABLENAME" where="WHERECLAUSE"/></work>"/>
<parameter name="from_sys_id" value=""/>
<parameter name="source" value="84affa0b1b9979105bcc1062b24bcb01"/>
<parameter name="priority" value="2"/>
<parameter name="agent_correlator" value="50beb8f91b32f9505bcc1062b24bcbee"/>
<parameter name="skip_sensor" value="true"/>
<parameter name="processed" value=""/>
<parameter name="error_string" value=""/>
<parameter name="sys_id" value="14beb8f91b32f9505bcc1062b24bcbee"/>
<parameter name="sequence" value="18c1bda6ae00000001"/>
<parameter name="from_host" value=""/>
<parameter name="sys_created_on" value="2023-11-29 16:12:28"/>
<parameter name="sys_domain" value="global"/>
<parameter name="name" value="84affa0b1b9979105bcc1062b24bcb01:1701274348246"/>
<parameter name="jdbc_password" value="HASHEDPASSWORD"/>
<parameter name="topic" value="JDBCProbe"/>
<parameter name="state" value="ready"/>
<parameter name="queue" value="output"/>
<parameter name="ecc_queue" value="14beb8f91b32f9505bcc1062b24bcbee"/>
</parameters>
</results>
With this, and the knowledge of XMLDocument
API, you can do everything!
Doesn’t it feel great?
Conclusion
I learned some important lessons here:
- I still have a lot to learn
- Lots of crazy ideas are possible with some creativity
- You can’t handle everything by yourself
- Write your code like you know someone will have to use it in the future
MID server
is not that scary as I thought