How to get Incident_Metric data via SQL / ODBC?

[srudenko]

We need to be able to pull in data which shows every single Assignment Group that has worked on an Incident
To do this we were directed to Reports section of Service Now and told to work with the 'Incident_Metric' view.

I was able to successfully setup a report to get the info we need on the web.
Here is what I have:

Type: List
Table: Incident Metric [incident_metric]
Group By: None
Selected Columns: Number(inc_number), Value
Filter: Definition IS Assignment Group

So this gets us what we need which is every ticket and the groups that it was assigned to from open to close.
This is good but we can't really do anything with this data on the web. We need to pull this into our SQL database.

Our SQL database is already linked with SN but I can't figure out how to setup an OPENQUERY to get the same information that we are getting through the web reporting. I also tried creating the query using MS Query and all I am getting are just blank results.

Can someone assist with this please?

[david_legrand]

Hi Steve,

You maybe need http://wiki.servicenow.com/index.php?title=ODBC_Driver 🙂
Another point, take care about the data you extract and when you extract them for avoiding performance issues.

An issue i had when i used the method to export on excel file was the data to get was too important and i didn't get all the data (maybe i forget something about the properties) so I added time filters to export the data week by week.

So a good thing for you is to export every day or every week only the data of the last day / last week.

Tell us if you need more support,
Best regards,

[srudenko]

David,

Thanks for the response. We do have the ODBC driver installed on our SQL server and have ServiceNow linked.
So we can do OPENQUERY and it works great against other tables such as 'incident'

Its just an issue with incident_metric.
Even when I do a basic query to pull one column or try to replicate what I did on the SN reporting site - all results are blank

[david_legrand]

Oh, do you use the same account to do that? Because it's looking like ACL restrictions.
In fact, I just checked (in case i was using another table and not this view) but it's ok, I was using incident_metric too and my admin account.

If the account isn't the same, try to impersonate this account to check if you have access or not to the records.
If the account is the same, i'm thinking but nothing pops up for now. If i think to possible root causes, i'll tell you.

Regards,

[srudenko]

Do you know what permissions are needed?
The account that we use for ODBC / SQL Querying is not the same that is used to access ServiceNow via the browser.
I don't have access to view or modify any permissions but we were told that the ODBC account was given the 'metric_admin' role

Prior to that, we could not even see the incident_metric table