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?

[HugoFirst]

I have no experience with the Perl API, but I do have experience with Perl.
In one instance, I exported a report and parsed thru the output with a Perl script to write a report with the information formatted in a way that I couldn't get with the S/N report writer.

My guess is that you could do a similar thing. Export each table and be sure to include the sysid for each record.
One table will be the "top pable" and have relationships to the other tables. Where this occurs, be sure to include that variable ( with the sysid ) in your report too.

Have your perl script parse the data for each table and store it in a hash with the sysid as the key. Once all your hashes are populated, the script can iterate thru the top level hash and use the sys-ids to get information from the other hashes to build each line of the flat file.

It's not as elegant as joining the tables, but it would work.

[icalderus]

Thanks for the input Hugo.

At this point I am still using the PERL API. I now have the output from the 3 records . It is conditioned in some cases , now all I have to do is perform the action join of the 3 records.

Hugo, do you have any sample code to share? I will provide what I have at this point, I am a newbie with PERL and the SN API so anything you can offer is much much appreciated.

thanks

Code:

#!/usr/bin/perl -w
#use SOAP::Lite ( +trace => all, maptype => {} );
use SOAP::Lite;

sub SOAP::Transport::HTTP::Client::get_basic_credentials {
return 'user' => 'pass';
}

my $soap = SOAP::Lite
-> proxy('https://service-now.com/cmdb_ci_outage.do?SOAP');

my $method = SOAP::Data->name('getRecords')
->attr({xmlns => 'http://www.service-now.com/'});

# get incident by sys_id
my @params = ( SOAP::Data->name(__order_by => 'begin') );
#push (@params, SOAP::Data->name(sys_mod_count => '0') );
my %keyHash = %{ $soap->call($method => @params)->body->{'getRecordsResponse'} };

# iterate through all fields and print them
#foreach my $k (keys %keyHash) {
# print "$k=$keyHash{$k}\n";
#}

my $i = 0;
my $size = @{$keyHash{'getRecordsResult'}};
for ($i=0; $i<$size; $i++) {
my %record = %{$keyHash{'getRecordsResult'}[$i]};
print "------------------------------Outage Output $i ----------------------------\n";
foreach my $kk (keys %record) {
print "$kk=$record{$kk}\n";
}

my $soap2 = SOAP::Lite
-> proxy('https://csudev.service-now.com/change_request.do?SOAP');



# get incident by sys_id
my @params2 = ( SOAP::Data->name(__order_by => 'number') );
push (@params2, SOAP::Data->name(category => 'CMS Migration Services') );
push (@params2, SOAP::Data->name(type => 'Pre-Approved') );
push (@params2, SOAP::Data->name(phase_state => 'Open') );
push (@params2, SOAP::Data->name(approval => 'Change Control Approved') );

#push (@params2, SOAP::Data->name(sys_mod_count => '0') );
my %keyHash2 = %{ $soap2->call($method => @params2)->body->{'getRecordsResponse'} };

# iterate through all fields and print them
#foreach my $k (keys %keyHash) {
# print "$k=$keyHash{$k}\n";
#}


my $i2 = 0;
my $size2 = @{$keyHash2{'getRecordsResult'}};
for ($i2=0; $i2<$size2; $i2++) {
my %record2 = %{$keyHash2{'getRecordsResult'}[$i2]};
print "------------------------------Change Request Output $i2 ----------------------------\n";
foreach my $kk (keys %record2) {
print "$kk=$record2{$kk}\n";
}

}





my $soap3 = SOAP::Lite
-> proxy('https://csudev.service-now.com/change_task.do?SOAP');



# get incident by sys_id
my @params3 = ( SOAP::Data->name(__order_by => 'dv_parent') );
#push (@params3, SOAP::Data->name(sys_mod_count => '0') );
my %keyHash3 = %{ $soap3->call($method => @params3)->body->{'getRecordsResponse'} };

# iterate through all fields and print them
#foreach my $k (keys %keyHash) {
# print "$k=$keyHash{$k}\n";
#}

my $i3 = 0;
my $size3 = @{$keyHash3{'getRecordsResult'}};
for ($i3=0; $i3<$size3; $i3++) {
my %record3 = %{$keyHash3{'getRecordsResult'}[$i3]};
print "------------------------------Change Task Output $i3 ----------------------------\n";
foreach my $kk (keys %record3) {
print "$kk=$record3{$kk}\n";
}



}
}

[DrewW]

Have you thought of creating a view in ServiceNow and then just querying the view using the web services?

[icalderus]

Hey there,

Yes indeed that is the second approach I am pursuing. I am currently awaiting to be granted admin access in our SN system so I can create a view.
From what I understand I can use the view to run a SQL join . At that point I can query the view using the PERL API. The output can be further formatted/manipulated from there.

Does this sound logical?

I know the current thread is a hack join if you will. It can be achieved much easier using the view as opposed to this approach here. Would you agree?