Introduction
In the basic design of ServiceNow, one of the most powerful features is the ability to access table data and many other features via REST API calls as standard, without having to undertake any development whatsoever. This makes it very easy to integrate with other applications.
Many users and developers want to extract data into Excel for further analysis, comparison and presentation. I personally find it useful for comparing before and after data when developing new functionality, or for pulling data together when researching an issue. It's also proved useful for showing the schema differences between Orlando and Paris versions.
The objective of the Excel Add-in is to make it easy to pull data into Excel from tables. In addition to access simple schema information so that comparisons between instances, or before and after applying update sets, can be made.
The Add-in is supplied as a xlsm file, which can be converted to a Excel Add-in xlam file as required with say a custom ribbon (something I use myself). It consists of a number of macros that can be viewed from the View/Macros option on the Ribbon, assuming that macros are enabled.
Version 00.06.000 is a free evaluation/prototype version, apologises that the UI is not polished yet! and is valid to use until the 31st December 2020. Further versions will be made available as I add and fix functionality. I can be contacted for support, advice and any feedback via gwilmot_gsoftware@hotmail.co.uk and if you wish to receive updated versions, please send a registration email to the same email address.
I welcome any requests for additional functionality as some of the features I'm thinking of adding are:-
- Snapshot Analysis, where details such as which tables reference a given table are given.
- SQL-Server DDL generation so that equivalent tables can be created in SQL-Server with the option that only those fields that are created at that given parent/child level are created.
- Ability to read Excel sheets and post data to ServiceNow.
As well as tidying up the UI and making it easier to build predicates.
I hope some people find it as useful as I have!
General Notes
When any of the functionality is executed, it creates a worksheet called 'ServiceNowAddinConfig', this where configuration information is stored. It does store passwords and if therefore the spreadsheet data is to be distributed, it is suggested that this sheet is deleted. Further versions may well have the ability to password protect this sheet, although of course this can be done manually. It also stores the parameters from table, aggregate and schema data loads so that the data can be easily reloaded.
Any functionality that accesses a ServiceNow instance requires the following information:-
Server : {in the form https://NNNNNNN.service-now.com}
User :
Password:
The user account must have REST API read access to the tables in ServiceNow, typically the same generic account that might be used for ODBC queries will suffice.
The functionality has been tested against 64-bit and 32-bit versions of Excel and should also work if you're operating behind a proxy.
Macros
ServiceNowAddinForm
Displays a form from which the further functionality can be selected
ServiceNowAboutAddin
'About' on the Custom Form
Displays copyright notice and version.
ServiceNowTableDataLoad
'Load Table' on the Custom Form
Allows the data from ServiceNow Tables to be pulled into a worksheet.
Enter the ServiceNow Instance details to begin with, then enter a table name. The table name can be entered manually, or selected from a drop down list if the 'Get Tables' button has been executed.
The fields to return can either be entered as a manually entered comma-separated list, or by using the 'Get Fields' button. Although the 'Get Fields' functionality only returns the fields for the given table, it is possible to enter dot walked fields manually.
The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {table name} is used. Be careful an error can occur if the name length is greater than 31 characters.
The number of rows returned can be limited by the drop down from 1 to 1,000,000. It can be useful to set this to a low level when testing out the returned data.
The 'Display Value' option when selected will returned the display value for reference fields rather than the sys_id.
The 'Exclude Reference Link' option selects whether to display the referenced table link together with the sys_id. Be careful returning the display value and the reference link as the sys_id component appears to be truncated.
The Encoded Query allows a predicate to be entered, the syntax of which is complicated, eg:-
manufacturer.name=IBM^ORmanufacturer.name=Dell Inc.
Details of this syntax are available here:-
https://docs.servicenow.com/bundle/orlando-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
Future plans include a simplified form of constructing these encoded queries (hence greyed out 'Get Query').
Apply runs the query and if successful, loads data to the worksheet without closing the form
OK runs the query, loads data to the worksheet closing the form.
ServiceNowAggregateLoad
'Load Aggregate' on the Custom Form
Allows aggregate data from ServiceNow Tables to be pulled into a worksheet.
Enter the ServiceNow Instance details to begin with, then enter a table name. The table name can be entered manually, or selected from a drop down list if the 'Get Tables' button has been executed.
The group by fields to return can either be entered as a manually entered, comma-separated list, or by using the 'Get Fields' button. Although the 'Get Fields' functionality only returns the fields for the given table, it is possible to enter dot walked fields manually.
The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {table name - aggregate} is used. Be careful an error can occur if the name length is greater than 31 characters.
A record count is always returned as long as one row is returned.
The fields that a minimum value is required for can be entered as a comma separated list, or selected from the list if the label 'Min Fields' is clicked.
The fields that a maximum value is required for can be entered as a comma separated list, or selected from the list if the label 'Max Fields' is clicked.
The fields that an average value is required for can be entered as a comma separated list, or selected from the list if the label 'Avg Fields' is clicked.
The fields that a sum value is required for can be entered as a comma separated list, or selected from the list if the label 'Sum Fields' is clicked.
The number of rows returned can be limited by the drop down from 1 to 1,000,000. It can be useful to set this to a low level when testing out the returned data.
The 'Display Value' option when selected will returned the display value for reference fields rather than the sys_id.
The Encoded Query allows a predicate to be entered, the syntax of which is complicated, eg:-
manufacturer.name=IBM^ORmanufacturer.name=Dell Inc.
Details of this syntax are available here:-
https://docs.servicenow.com/bundle/orlando-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
Future plans include a simplified form of constructing these encoded queries (hence greyed out 'Get Query').
count^manufacturer^>^2
Details of the syntax are available here:-
Apply runs the query and if successful, loads data to the worksheet without closing the form
OK runs the query, loads data to the worksheet closing the form.
https://developer.servicenow.com/dev.do#!/reference/api/orlando/rest/c_AggregateAPI
ServiceNowSchemaLoad
'Load Schema' on the Custom Form
Enter the ServiceNow Instance details to begin with.
The Snapshot functionality grabs a 'snapshot' of the Database Definition of the given instance of ServiceNow. This includes a list of all the tables, the fields and their basic attributes of those tables and (if the table is configured to be accessed by REST API) a row count for that table. If a negative row count is given it represents the REST API error code returned which probably indicates that the table is not configured for REST API access.
The row count is given for those rows that have the sys_class_name matching the name of the table. In this way no count is given for the child table extended for the parent, only the records that are defined where the sys_class_name is the same as the table name.
A snapshot can be used to compare different instance, or say an instance after an upgrade. The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {Snapshot DDMMYYYY} is used. Be careful an error can occur if the name length is greater than 31 characters.
It can take up to 50 minutes to compile a snapshot.
The Table Schema functionality grabs a list of all the tables for a given instance with an optional row count. Without the row count, it will only take a couple of minutes to compile, with the row count it can take up to 50 minutes.
The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {instance name} is used. Be careful an error can occur if the name length is greater than 31 characters.
The Field Schema functionality grabs all the fields defined for a given table. The table name can be entered manually, or selected from a drop down list if the 'Get Tables' button has been executed. There are a considerable number of columns that are returned so there is the option to return a restricted set. The actual set of restricted columns can be set by selecting the Set Restricted button. This is stored in the worksheet called 'ServiceNowAddinConfig', the default set of restricted columns can be restored by blanking the value in the worksheet called 'ServiceNowAddinConfig'.
The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {table - fields} is used. Be careful an error can occur if the name length is greater than 31 characters.
ServiceNowSnapshotComparison
'Snapshot Comparison' on the Custom Form
Two snapshots can be selected for comparison, it will highlight if there are tables, or fields missing from either snapshot and if there are any differences in the core attributes of the fields such as size, mandatory, unique, or default settings. Optionally it will compare the row count.
This functionality is useful to compare what has changed between versions and even after an update set has been applied.
The name of the Output Worksheet can either be entered manually, or selected from the current list. If left blank, the default of {Snapshot Comparison DDMMYYYY} is used. Be careful an error can occur if the name length is greater than 31 characters.
ServiceNowReloadData
'Reload Data' on the Custom Form
Allows any Aggregate data, Table data or Schema data to be reloaded (apart from Snapshots). The list is based on the data held in the worksheet 'ServiceNowAddinConfig' and any individual worksheet can be selected for reload.
