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?

[srudenko]

Here is the query that I tried which mimics what the report on the web does but it doesnt work.
Error: OLE DB provider "MSDASQL" for linked server "sndev" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]java.lang.NullPointerException[1010]".

SELECT *
FROM OPENQUERY (sndev,
'
SELECT
inc_number
mi_value
FROM incident_metric
WHERE
md_name = ''Assignment Group''
'
)

[david_legrand]

I made few tests and I think you need the role itil_admin.

I'll try to explain (it could be useful for other people as well) 🙂

The question we have is "what are the required roles to access the records?"

0) we need to check into the ACL but we don't have "incident_metric.none" or "incident_metric.*"
1) incident_metric isn't a table as we can't find it on the dictionary (1st place to check)
2) on database view, we can found incident_metric and the view is composed from metric_definition, metric_instance and incident table
3) In the metric_definition record, we'll found the "definition" of the metrics, in the metric_instance, we'll found the data so we need to check the ACL of metric_instance
4) On the ACL rules, we can see we need the itil_admin role to read the metrics.

Now a weird thing is soap_query role should be enough for query every table according role role definition "Can query records on all tables and columns"
On a demo,
* the admin role query your code without any issue
* the soap_query role has "no column error" and we i impersonate the user on the instance, i have the security constraint
* the soap_query + itil_admin doesn't have the error but it doesn't display any record. On the instance (with impersonation), i have now the incident_metric records ==> Logic according the ACL rules but not according the "role definition"

What are the roles you have for this user?

[srudenko]

David - thank you for all of your suggestions.
I am going to have to open a ServiceNow incident to our ServiceNow Admin team to check on all of this 🙂
Problem is that we don't have any access to the admin settings. We are on a separate team but have an account for ODBC so that we can create queries for various reports that we need. I don't even have a schema or a data dictionary. We just have to figure it out by looking at the available columns via MS Query Builder and/or web report section.

[david_legrand]

I just had an idea, could you check this property please 🙂

https://wiki.servicenow.com/index.php?title=SOAP_Web_Service#Enforcing_Strict_Security

Best regards,

[srudenko]

I got the following query to work via MS Query. It returns the desired data:

SELECT incident_metric.inc_number, incident_metric.mi_value
FROM SCHEMA.OAUSER.incident_metric incident_metric
WHERE (incident_metric.md_name='Assignment Group')

The same query, when run via MS SQL Server Management Studio from a server where SN is linked returns no results (no errors either)
Running it with the same account as what I used with MS Query

Not sure what the problem is