Accessing Metric Result (asmt_metric_result) table data through the Tableau Connector?

[graycarper]

Howdy!

I'd like to develop some ticket satisfaction survey reports that integrate data from a number of different tables (incident, sys_user, cmdb_ci, asmt_assessment_instance, asmt_assessment, asmt_metric_result, etc). Everything has gone smoothly so far with one exception: the Tableau Connector doesn't display Metric Result (asmt_metric_result) as an available table. As a way of checking my roles and ACLs, I used the ODBC driver in combination with Tableau Desktop to connect to the same account on the same instance. Tableau Desktop has no problem accessing asmt_metric_result through ODBC.

The account I'm using has the itil, soap_query, survey_admin, snc_read_only, and table_metadata roles. That last one - table_metadata - is a custom role that grants read-only access to the sys_dictionary, sys_db_object, and sys_glide_object tables via ACLs. (This allows for browsing of ServiceNow's database structures and is used, in particular, to make accounts compatible with the Tableau Connector.)

Could the ODBC (SOAP API) be given access to tables that the Tableau Connector (REST API) is not? Seems unlikely, but based on this experiment that's my best guess at the moment. I'm not sure how to troubleshoot that, though.

Any ideas? Am I overlooking something? Has anyone run into this problem before?

Thank you!

-Gray

[graycarper]

More data! To try and troubleshoot SOAP vs REST, I used the pysnow Python library against the REST API...

import pysnow
auth = {'instance': 'my instance', 'username': 'myusername', 'password': 'mypassword'}
s = pysnow.Client(instance=auth['instance'], user=auth['username'], password=auth['password'])
r = s.query('asmt_metric_result', query={})
for record in r.get_multiple(order_by=['-created_on']):
               print(record)

This pulled up all the asmt_metric_result records just fine, so this isn't a problem with the REST API.

A thought: The Tableau Connector only allows you to use the tables it can find by itself. It generates a list (presumably through what it can see in the sys_db_object table), you select the tables you want from that list. Could it be that whatever it uses to generate the list isn't telling it about asmt_metric_result? If that's the case, my first guess would be an ACL problem, but - contradictory to that - the ODBC driver has no trouble browsing through all the tables and it does show asmt_metric_result.

-Gray

[graycarper]

I haven't found a solution yet, but I've done some more troubleshooting and I thought I'd record that here.

In my initial post, I theorized that whatever the Connector uses to generate the list of tables isn't telling it about asmt_metric_result. I wanted to see if I could test that theory by...

1. Determining exactly what the Connector does when it first creates the connection
2. Using another tool to replicate the same behavior

For step one, I established a connection through the Tableau Connector, opened up the ServiceNow system logs (System Logs > Utilities > Node Log File Browser), and found the exact SQL query that the Connector uses...

SELECT
     sys_dictionary.calculation,
     sys_metadata.sys_replace_on_upgrade,
     sys_dictionary.dynamic_ref_qual,
     sys_dictionary.choice_field,
     sys_metadata.sys_updated_on,
     sys_dictionary.spell_check,
     sys_dictionary.reference_cascade_rule,
     sys_dictionary.reference,
     sys_metadata.sys_updated_by,
     sys_dictionary.read_only,
     sys_metadata.sys_created_on,
     sys_dictionary.element_reference,
     sys_dictionary.array_denormalized,
     sys_metadata.sys_name,
     sys_dictionary.reference_key,
     sys_dictionary.reference_qual_condition,
     sys_dictionary.xml_view,
     sys_dictionary.dependent,
     sys_dictionary.internal_type,
     sys_metadata.sys_created_by,
     sys_dictionary.element,
     sys_dictionary.max_length,
     sys_dictionary.use_dependent_field,
     sys_dictionary.delete_roles,
     sys_dictionary.active,
     sys_dictionary.choice_table,
     sys_dictionary.foreign_database,
     sys_metadata.sys_update_name,
     sys_dictionary.unique,
     sys_dictionary.name,
     sys_dictionary.dependent_on_field,
     sys_dictionary.dynamic_creation,
     sys_dictionary.primary,
     sys_metadata.sys_policy,
     sys_dictionary.next_element,
     sys_dictionary.virtual,
     sys_dictionary.widget,
     sys_dictionary.use_dynamic_default,
     sys_dictionary.sizeclass,
     sys_dictionary.mandatory,
     sys_metadata.sys_class_name,
     sys_dictionary.dynamic_default_value,
     sys_metadata.sys_id,
     sys_dictionary.write_roles,
     sys_dictionary.array,
     sys_dictionary.audit,
     sys_dictionary.read_roles,
     sys_metadata.sys_scope,
     sys_dictionary.dynamic_creation_script,
     sys_dictionary.create_roles,
     sys_dictionary.defaultsort,
     sys_dictionary.column_label,
     sys_dictionary.comments,
     sys_dictionary.use_reference_qualifier,
     sys_dictionary.reference_floats,
     sys_dictionary.display,
     sys_metadata.sys_mod_count,
     sys_dictionary.default_value,
     sys_dictionary.staged,
     sys_dictionary.reference_type,
     sys_metadata.sys_package,
     sys_dictionary.attributes,
     sys_dictionary.choice,
     sys_dictionary.table_reference,
     sys_dictionary.reference_qual,
     sys_metadata.sys_customer_update,
     sys_dictionary.text_index 
FROM
     (
           sys_dictionary sys_dictionary 
           INNER JOIN
                 sys_metadata sys_metadata 
                 ON sys_dictionary.sys_id = sys_metadata.sys_id 
     )
WHERE
     sys_dictionary.active = 1 
ORDER BY
     sys_metadata.sys_created_on

To see how this behaves, I opened an ODBC-friendly SQL query tool (WinSQL), and plugged this in. At first, the tool wouldn't run the query because it claimed it couldn't find the sys_metadata table and - in a subsequent test after resolving that error - the sys_metadata.sys_scope field. To fix both issues, I created a read ACL on the sys_metadata table and sys_metadata.sys_scope field for a role applied to my account (the same role that gives the account read access to sys_dictionary). Once that was done, the query ran successfully, but the additional ACLs didn't fix the problem with the Tableau Connector. (Not surprising. The account could already submit a successful API query against sys_metadata without those ACLs, but the ACLs allowed WinSQL to see that the table and field existed when it built its catalog of the database at connection time. WinSQL seems to just be very strict about only letting you query what it is certain exists in the database.)

So, what did I find in the query results? A list of all the active database tables and fields, including everything for asmt_metric_result. That's puzzling since it should mean the Connector is getting an accurate list of tables. One thing, though: When the Connector displays the list of tables, it uses the contents of sys_db_object.label to provide a human-friendly display name. The sys_db_object table doesn't appear anywhere in the query above, though, so I suspect I haven't found all the queries being run. To be absolutely sure, I think I have to look for SQL traffic on the wire.

My first attempt to do that was with Wireshark, but the traffic is encrypted and that's tricky with Wireshark. Microsoft Message Analyzer has decryption built-in, so that's what I'm trying next. For that to function, though, I need to have the ServiceNow instance's server certificate and private key (or create new ones), then provide them to Microsoft Message Analyzer. That's where I'll continue when I return to this.

-Gray

[graycarper]

Ah! I think I've found the source of the problem!

I just learned about Tableau Desktop's Web Data Connector Debugger, so I fired that up and used it to explore the ServiceNow ITSM connector source code. It looks to me like dataviews.setup.js (connectors.tableau.com > servicenow/servicenow/1/app/js/modules/dataviews.setup.js) establishes all the tables that the connector will look for, and asmt_metric_result isn't in the list. I believe the fix is to simply add a new entry to that file…

{

        "id": "asmt_metric_result",

        "dateFilter": true,

        "extends": [],

        "extraConditions": "",

        "engLabel": "Metric Result"

}

I've reached out to Tableau support to see exactly how one should go about doing that.

-Gray

[graycarper]

We're on the path to resolution! Tableau Support was able to replicate and confirm the problem, so they then passed it on to their engineering team, who also confirmed it. They have committed to fix it and it's been added to their development cycle. Hopefully that means a fix will come soon-ish, but they haven't given me a time estimate on it.

-Gray