Query SQL Database from ServiceNow for user's username

[gnunez]

Hello all,

I have a requirement for one of our catalog items. Once a request has been submitted and approved, we would like ServiceNow to query our SQL database with a simple command that looks up if a user is in the table based on their username. If they aren't in the table then they are inserted. I have never done an integration like this so I have no clue where to start. I have been researching and found similar threads and I know many mention JDBC, ODBC or a script include, but I'm not too sure where to start. Has anyone had a similar requirement or can provide helpful input to guide me in the right direction? If so, any help is greatly appreciated!!

Also, would it be possible to trigger this somewhere within the workflow because we would like to query the table once the request has been approved?

SQL query can be something like this:

If SELECT count(*) FROM 'database name here' WHERE 'UID'='accountname' = 0

Then

INSERT INTO 'database name here' ('UID') VALUES ('accountname')

Thanks,

Grace

[Manish Vinayak1]

Hi Grace,

You can use JDBC Probes in ServiceNow to interact with external databases. You'll need to have a MID Server Installed with a service account having access to the requested database servers and tables. In addition to that you will require to have JDBC Probe plugin enabled in your instance.

You can find more details in the ServiceNow documentation:

https://docs.servicenow.com/bundle/newyork-platform-administration/page/integrate/inbound-other-web-...

JDBC Probe interacts with the external database using ECC Queue as a channel, it sends an output queue as the probe defined in the documentation, then receives input queue with the result of your SQL query.

You can create an script include to create the Proble Payload to be inserted into the ECC Queue, and a business rule to parse the response and do other actions based on the response received.

There's also this blog, which is old, but I believe is still valid and is helpful:

http://www.john-james-andersen.com/blog/service-now/using-the-servicenow-jdbcprobe.html

Hope this helps!

Cheers,

Manish

[gnunez]

Hello Manish,

Thank you for the response! It is my first time at this, but it's kind of gearing me towards the right direction. 

I installed the "Integration - JDBC" plugin and it comes with the script include "JDBC Probe", are you saying I can use this script include or that I need to create a new one?

Thanks in advance!

Grace

[Manish Vinayak1]

Hi Grace,

Yes it will create a script include called "JDBCProbe". You can use that script include to trigger the probes, you can find some examples in the blogpost:

http://www.john-james-andersen.com/blog/service-now/using-the-servicenow-jdbcprobe.html

Give it a try 🙂

Hope this helps!

Cheers,

Manish