I am going to create a few blogs on how I was able to to query the data in ServiceNow. My longer goal is to be able to create dashboards with data from multiple systems including ServiceNow. But my end goal for these blogs is to create a few SSRS reports.
I am not going to walk you through setting up an ODBC connection to your ServiceNow instance. You can read about that here. http://wiki.servicenow.com/index.php?title=ODBC_Driver#gsc.tab=0
The ServiceNow reports are pretty descent and you are able to get some basic information. But what if you want more. With SQL we can do a lot more with the data.
My first query\report will be to list the count of incidents by group based on priority. Here is what the results look like in SQL.
I am grouping by Assignment Group, doing a total count, and then a count for each priority.
SELECT * FROM OPENQUERY (SERVICENOW , 'select dv_assignment_group as ''Assisgnment Group'', count(number) as Total, count(case when dv_priority =''Standard'' Then 1 else null end) as Standard, count(case when dv_priority =''High'' Then 1 else null end) as High, count(case when dv_priority =''Critical'' Then 1 else null end) as Critical from incident where dv_incident_state in(''New'',''Pending'',''Working on Incident'',''Due Date'',''Workaround Applied'') group by dv_assignment_group ') GO
Something to keep in mind is that when doing an open query you need to you use double ticks. dv_assignment_group as ”Assisgnment Group”
I’ll get in to some other query’s and then I’ll eventually get in to creating the SSRS reports.