toy superhero near laptop on table at home

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 with onComplete 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 any SQL command, including delete
  • 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

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *