Direct access to ServiceNow?

[icalderus]

Hello All,

I am wondering if there is any way to access Service Now using JDBC? From what I can gather the only protocol that is used to access SN is SOAP, and that is done either through the Windows based ODBC or via the PERL API.

I have a quandry here, I need to run a SQL join statement and pipe the output to a flat file.

From the research I have done none of the PERL API would allow me to query the tables I need to touch.

Has anyone out there ever got direct access to ServiceNow and been able to run a SQL join against multiple tables?

I am searching high and low through the PERL API documentation and while I see some examples of getting or inserting incident information, I see nothing even close to a SQL join being run .

We are on a linux box and trying to reach our ServiceNow database.

Anything you can offer is appreciated?

[DrewW]

I did something similar using a view. Just remember that when you do your web services query you have to use the table prefix you put in the view in front of the column names to get it to work right, at least if I am remembering correctly.

[icalderus]

Most excellent news. I am new to both PERL and Service Now API so it has been an interesting experience thus far =). all is good as I am learning.

Ok so the prefix for the view would be used when running the query correct? I think i gotcha, do you have any sample code to offer? For a newbie like myself that would be great as a learning tool.

anything you can offer is appreciated..

thanks

[DrewW]

This is part of the code from the UI Macro I wrote to display all of the child journal entries for a Requested Item. I created a view between sys_journal_entry, task, and sys_user and then used the below to filter a query against the view. je is the variable prefix for the sys_journal_entry table.

var gr = new GlideRecord("u_journal_field_detail");
gr.addQuery("je_element_id", "IN", tasks.toString());
gr.orderByDesc("je_sys_created_on");
gr.setLimit(100);
gr.query();
gr;

[icalderus]

Good Morning,

Thanks for the sample code. I appreciate it. This is in PERL correct?

So let me get this straight, ill try to understand the code piece by piece here

var gr = new GlideRecord("u_journal_field_detail"); <- Here you are creating a new object that will house the output from the view query. Is u_jounral_field_fetail the name of the custom view?

gr.addQuery("je_element_id", "IN", tasks.toString()); <- I am not 100% sure but it looks like the addQuery function is being used to query against the sys_journal_entry table? . If so, what exactly is being queried? What does the "IN" signify?


gr.orderByDesc("je_sys_created_on"); <- An order by statement, you are ordering by the sys_created_on field in your output.

gr.setLimit(100) <-Youre setting the output limit to 100 rows/records.

gr.query(); <- Youre executing the query.

gr; <- Not sure what is going on here, again, I am new to PERL.


Having said all that, what is the name of the custom view that you created and are querying? I assume the view you created is in fact named "sys_journal_entry" ?

I understand after we create our custom view we can query per the example above. In this example I want to be sure that I know how this custom made view is referenced.

thanks again, anything you can offer to correct my misunderstandings is appreciated.

thanks

[DrewW]

My code was/is not Perl, it is javascript, it was meant to be an example of querying a view.