1 2 Previous Next 20 Replies Latest reply on Apr 18, 2019 2:43 PM by Drew Loika

    ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?

    Gray Carper

      Howdy!

       

      Might anyone be using the ServiceNow Connector to build worksheets around ServiceNow assessment / survey results? I'd like some ticket satisfaction survey reports that integrate data from a number of different ServiceNow tables (incident, sys_user, cmdb_ci, asmt_assessment_instance, asmt_assessment, asmt_metric_result, etc), and everything has gone smoothly with one exception: the Connector doesn't display Metric Result (asmt_metric_result) as an available table. This is a large roadblock because that's precisely the table that contains survey results.

       

      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.)

       

      To check my roles and ACLs, I've done two things:

      1. Use ServiceNow's ODBC driver, through Tableau Desktop, to connect to the same account on the same instance via the SOAP API.
      2. Use the pysnow Python library, again with the same account and instance, to query asmt_metric_result through the REST API.

       

      In both cases, I'm able to see asmt_metric_result with no problem, so both APIs are providing the data I need, yet the Connecter seems be unaware of asmt_metric_result.

       

      A thought: The 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) and you select the tables you want from that list. You can't give it a table name to fetch that isn't in the 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 preventing my account from browsing to the table, but then why would I be able to browse to the table via ODBC / SOAP?

       

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

       

      Thank you!

      -Gray

        • 1. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
          Gray Carper

          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 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...

           

          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 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.

           

          -Gray

          • 2. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
            Gray Carper

            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

            1 of 1 people found this helpful
            • 3. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?

              Hey Gray Carper,

               

              Haha, let us know how it goes!

              If you need further support, we're ready and happy to help.

               

              Cheers,

               

              ----------

              Lénaïc RIÉDINGER, Global Community Engineer Tableau

              Tableau Community Forums | Knowledge Base

              If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!

              • 4. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                Drew Loika

                Hi Gray,

                 

                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.

                 

                Cheers!

                 

                Drew Loika
                Product Manager - Cloud
                Tableau

                • 5. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                  Gray Carper

                  Hi, Drew!

                   

                  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 again!

                  -Gray

                  • 6. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                    Drew Loika

                    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. :-)

                     

                    Best,

                     

                    Drew Loika

                    Product Manager - Cloud

                    Tableau

                    • 7. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                      Gray Carper

                      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.

                       

                      -Gray

                      • 8. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                        Drew Loika

                        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.

                         

                        Cheers!

                         

                        Drew

                        • 9. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                          Gray Carper

                          Wonderful! It works like a charm. Thanks so much for all your help!

                           

                          -Gray

                          • 10. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                            mark.welsh

                            Hi Drew,

                             

                            I may have already asked this elsewhere, but how can I get a list of the tables currently supported by the ITSM Connector?

                             

                            Thanks

                            • 11. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                              Drew Loika

                              Hi Mark, you can see this list by connecting in Tableau. You can even search by name.

                               

                              Cheers

                              • 12. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                                Tylor Cottingham

                                Hi Drew,

                                 

                                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.

                                 

                                 

                                Thanks,

                                Tylor

                                • 13. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                                  mark.welsh

                                  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..

                                  • 14. Re: ServiceNow Connector: Accessing the Metric Result (asmt_metric_result) table?
                                    Drew Loika

                                    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.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next