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...
- Determining exactly what the Connector does when it first creates the connection
- Using another tool to replicate the same behavior
For step one, I established a connection through the ServiceNow Connector, opened up the ServiceNow system logs (System Logs > Utilities > Node Log File Browser), and found the exact SQL query that the Connector uses...
ON sys_dictionary.sys_id = sys_metadata.sys_id
sys_dictionary.active = 1
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 ServiceNow 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.
1 of 1 people found this helpful
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…
"engLabel": "Metric Result"
I've reached out to Tableau support to see exactly how one should go about doing that.
Hey Gray Carper,
Haha, let us know how it goes!
If you need further support, we're ready and happy to help.
Lénaïc RIÉDINGER, Global Community Engineer Tableau
If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!
I'm very impressed by the investigation you've done here! Thanks for pointing out this issue in the connector, it's supposed to support that table, I'll have the team fix this.
More generally, for you and anyone else reading this, we focused on the ITSM space for the first release of the connector which is why it doesn't support all the tables you might find in your ServiceNow instance. Future iterations will likely include more tables. For example, I've receive requests for custom tables from many customers. This is usually the reason why a table isn't listed by the connector, though you've found an exception.
If you haven't already, I suggest you sign up for ServiceNow Connector Announcements at the bottom of Tableau Pre-Release Programs | Tableau Software . We have some updates coming that I think you could provide some really valuable feedback on.
Product Manager - Cloud
Wonderful! Thanks so much for your help!
Just so you know, I have an open support case (#03334312) on this, so feel free to track your work over there if you like. Either way, I'll make sure Tom, the support tech, knows you've weighed in here just to avoid duplicated effort.
And I just signed up for ServiceNow Connector announcements thanks to your pointer.
Thanks Gray, we've connected the dots on this side. On an unrelated note I shared your post with the ServiceNow ITSM connector developers, they too were very impressed by your investigation. :-)
Product Manager - Cloud
Thanks so much, Drew! We're just starting to use ServiceNow here, and this was my first encounter with any web data connector, so I learned a lot along the way. Next time I'll go straight to the debugger and I won't spend any time trying to decrypt SSL on the wire.
I'm so relived to have this in your team's hopper. Before you got involved, I was concerned I wouldn't be able to report on ticket survey satisfaction - at least not through the Connector - and that would have been a bummer.
I can't say I'm often in Fremont, but the next time I'm there I'll bring a bag of quarters and challenge you guys to a few rounds of your favorite games at Add-a-Ball Arcade. I call dibs on Bubble Bobble.
Hey Gray, deal! I'll buy the drinks. :-) In the meantime, I wanted you to know the team deployed a fix for this issue, the asmt_metric_result table should be available the next time you open Tableau. (No downloads or installation needed.) Please take a look and let me know if it works for you or not.
Wonderful! It works like a charm. Thanks so much for all your help!
I may have already asked this elsewhere, but how can I get a list of the tables currently supported by the ITSM Connector?
Hi Mark, you can see this list by connecting in Tableau. You can even search by name.
You mentioned the first release of the connector didn't support all or custom tables. I am in need of tables that don't exist in the ITSM connector - how can I connect to these tables? If I open a ticket, can Tableau support help me add a new entry to the file Gray mentioned (servicenow/servicenow/1/app/js/modules/dataviews.setup.js)? What are my options here?
Also, did Tableau ever release a 2nd version of the Service Now connector? I'm assuming not since all tables are not visible.
Our plan to connect to custom tables was to use the ODBC connection. And join in Tableau. Haven't gotten around to it yet, so not sure if it will work. We tried and I remember having issues connecting in Tableau, but I was able to connect in Excel. My plan was to just bring the data into Excel and join that to ITSM connector in Tableau. Might be wroth a shot..
1 of 1 people found this helpful
Hey Tyler, unfortunately our connector does not support custom tables and there isn't a way to modify it. That said I've heard from many customers for whom this is a significant problem. I can't commit to a timeline but I do hope to improve the situation in the future.