ODBC on-prem plugin

For more information about what this plugin does and the data streams it retrieves, see:

ODBC

Connects to any on-prem ODBC data resource.

To add a data source click on the + next to Data Sources on the left-hand menu in SquaredUp. Search for the data source and click on it to open the Configure data source page.

The ODBC data source requires that you have already installed the ODBC driver you wish to use on the same machine as the SquaredUp agent.

Before you start

Configuring and deploying an agent

For an on-prem plugin you will need a relay agent that can access the server hosting your on-prem data source. You do not need a relay agent for cloud plugins.

If you have already created a relay agent in SquaredUp that can access this data source, then you can skip this step and choose the agent group you want to use while Configuring the data source.

You can install an agent on either Windows or Linux:

Configuring the data source

  1. Display name:
    Enter a name for your data source. This helps you to identify this data source in the list of your data sources.

  2. Agent group:
    Select the Agent Group that contains the agent(s) you want to use.
  3. Connection string:
    Each ODBC Data Source will require a different Connection String, with the format varying based on the ODBC driver type. See ODBC Connection String: Connecting to Your Database
  4. Enable object indexing:
    Select to enable object indexing, which creates objects in the Map used to create context sensitive objects and drill-downs. The following fields display:
    • Object query:
      Enter a database query to return the objects you want to index.
    • Relationship query:
      If you are creating objects with the Object query set in the previous step, you can relate those objects in the Map.

  5. Optional: Restrict access to this data source:
    You can enable this option if you only want certain users or groups to have access to the data source, or the permission to link it to new workspaces. See data source access control for more information.

  6. Click Add.

Importing Microsoft Excel document data

The ODBC plugin's flexibility allows you to import Microsoft Excel document data via the ODBC data source. To do so, you must simply create a document containing the mandatory sourceId, name and type columns, then pass a Connection String and Object Query for the Excel document when you Add an ODBC data source in SquaredUp. The following information details this process.

Prerequisites

When connecting to Excel, ensure that:

  • The proper drivers are installed (see Microsoft Access Database Engine 2016 Redistributable).
  • The bit architecture of the application and driver is correct (i.e. use 64-bit Excel with the 64-bit driver).
  • That the Relay agent is set up with a security principle that has access to the file (i.e. notNetwork Service, otherwise the Error: Cannot find module error occurs).

How to import excel document data via the ODBC plugin

  1. Create the Excel (64-bit) spreadsheet you'll be extracting data from and add the sourceId, name, type columns.

    The sourceId, name, and type columns must exist in your Excel spreadsheet, otherwise the data source will not function.

    You must also ensure that column headers do not contain spaces.

    1. Make a note of the Excel file name. In the following example, the worksheet name is Usage_v2.
    2. Make a note of the file location of the Excel document you created. In the following example, the file is called ServerUsage1.xlsx.
  2. On the machine hosting the Relay agent and the Excel data source, open ODBC Data Source Administrator (64-bit).
    1. Make a note of the Microsoft Excel driver full name on the Drivers tab. For example, Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).
  3. Combine the values you previously noted to create the data source connection string.

    The Connection String is created as:
    Driver={driver name};DBQ=File location;
    For example:
    Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\SqupAgent\ExcelFile\ServerUsage1.xlsx;
    The Object Query is crafted as:
    Select Excel column names from Excel document name
    For example:
    Select sourceId, name, type, ip, ServerName from [Usage_v2$]

    It is recommended that you wrap the Excel document name in square brackets, just in case there are any unsupported characters, such as spaces.

    Additionally, the query is not case sensitive. However, if it contains any spelling or formatting errors you will not receive any results.

  4. Follow the steps in Add an ODBC data source in SquaredUp, entering the Connection String and Object Query you previously created for the Excel document in the corresponding setup fields.

Next steps

  1. Add a dashboard and go into the configuration of a tile.
  2. Filter to the ODBC data source.
  3. Create a data stream by clicking on one of the customizable data streams.

Data streams

You can use these data streams to create new tiles to show data, or if there are preconfigured dashboards installed you can copy or edit those.

The following data streams are installed with this plugin.

Data stream
Description
Parameters
Custom Metrics Query
Returns the result of a SQL query for metrics used in graphing
  • Query:
    Enter a SQL query which returns metrics. For example, SELECT label,timestamp,value FROM MyPerformanceTable WHERE myObjectId = {{sourceId}}

    When writing a Metrics query, the below three columns are mandatory and all others will be ignored.
    • label: The label for the value passed in, for example ‘Percent Utilization’. Each distinct ‘label’ value will get its own line color which includes only values for that label.
    • timestamp: A DateTime value of when this reading occurred.
    • value: A numerical non-null value to be visualized.
Custom State Query
Returns the result of a SQL query for display in rollup and block tiles.
  • Query:
    Enter a SQL query which returns state. For example, SELECT label,id,state FROM MyRecentState WHERE myObjectId = {{sourceId}}

    When writing a State query, the below three columns are mandatory and all others will be ignored.
    • label: The label for the state passed in, for example ‘Deliveries’. These ‘label’ values must be distinct and will each produce their own status block
    • id: If this state will be tied to an object, then that object’s sourceId should be used, otherwise passing the ‘label’ value a second time will work effectively.
    • state: The state of the labeled object, options are ‘Success’, ‘Warning’, ‘Error’, and ‘Unknown’.
Custom SQL Table Query
Returns the result of a SQL query for display in a tabular format
  • Enter a SQL query. For example, SELECT Name FROM MyTable WHERE myObjectId IN ({{sourceIds}})

Was this article helpful?

Have more questions or facing an issue?
Submit a ticket