Using Search Data with Third-Party Reporting Tools

Use the Cherwell REST API to connect to third-party tools to retrieve data from CSM.

Use the Data Connector to pull data from a saved search. CSM can connect to Microsoft® Power BI® Desktop (version 2.64.5285.741 (18.11) (x64)) and Tableau Desktop (version 2018.3). You can use these products to visualize the data in different ways (reports, dashboards, charts, or graphs).

Configuring the REST API

To configure the REST API:

  1. In CSM Administrator, create a new API Client ID to automatically generate a Client Key. The Client Key will be used to generate an access token for the REST API. You can also use an existing Client ID.

    You will need to copy the Client Key to be used as the Client ID when launching a third-party tool.

  2. In CSM Administrator, set the base URL for the REST API.

    You will not be able to generate URLs for saved searches from the Search Manager until this step has been completed.

  3. Distribute the Client Key and base URL to CSM users who will be using a third-party tool.

Using Power BI and the Data Connector

You must have Microsoft Power BI installed. For complete details on installation, see https://docs.microsoft.com/en-us/power-bi/.

Ensure that you follow the steps below to install the correct version of the Cherwell Power BI Data Connector, and to select this version of the data connector from within Power BI Desktop. This version is identifiable within Power BI Desktop as it has the name "Cherwell Data Connector" and a blue icon. Current versions of Power BI Desktop will also have another data connector pre-installed, which is called "Cherwell" and has a red icon - that connector version is not supported for use with CSM versions 10.1.0 and earlier as it contains features which are incompatible.

To use Power BI and the Data Connector:

  1. In the CSM Desktop Client, install the Power BI Data Connector. In the CSM Browser Client, download the Power BI Data Connector.
    • From the CSM Desktop Client menu bar, select Searching > Install PowerBI Data Connector. This will copy the .mez file (the file type for the Power BI Data Connector) into the correct folder so it can be used without any further configuration.
    • From the CSM Browser Client menu bar, select Searches > Download PowerBI Data Connector. This will only download the file to the user's machine. You must copy and add this file to the local path ([Documents]\Power BI Desktop\Custom Connectors) before it is recognized by Power BI.
      You will need to restart Power BI after this step.

      For additional details on connector extensibility, see https://docs.microsoft.com/en-us/power-bi/desktop-connector-extensibility.

  2. Open Power BI.
    1. On the main menu bar, select File > Options and Settings > Options.
    2. Select Security and under Data Extensions, select the Allow any extension to load without validation or warning option. Select OK.
    3. From the main toolbar, select the Get Data drop-down list, and then select More.
    4. Select the Other category, and then select Cherwell Data Connector. Select Connect.
    5. Enter the URL and Client ID (this is the Client Key from Configuring the REST API above), and then select OK.

      If the base API URL was not set, the URL will need to be created manually.

    6. If prompted to sign in, select Sign in. Enter your CSM username and password. Alternatively, if SAML is enabled, enter your SAML credentials. Select Login.
    7. Select Connect.

Using Tableau and Web Data Connector

You must have Tableau installed. For complete details on installation, see https://www.tableau.com/support/help.

To use Tableau and Web Data Connector:

  1. Open Tableau.
    1. On the main toolbar select To a Server > More > Web Data Connector.
    2. Specify the path to the Cherwell Tableau Web Data Connector, which is available on your Cherwell REST API server (https://[servername]/CherwellAPI/DataConnector/Tableau) and then press Enter.
    3. Enter your CSM username and password, and then enter the Client ID (API key: this is the Client Key from Configuring the REST API section above) and the target search URL.
    4. Select Authenticate.

      If the base API URL was not set, the URL will need to be created manually.