ODBC on-premise plugin

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

ODBC

Connects to any on-premises ODBC data resource.

This is an on-prem data source.

How to add the data source

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.

Configuring and deploying an agent

If you have already created an agent in SquaredUp that you can use for this data source, you can skip this step and choose the agent group you want to use while adding the data source.

See one of the following, depending on your platform type:

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. Object Query (optional):
    The ODBC Connection can create objects in the Knowledge Graph index and these can be used to create context sensitive objects and drill-downs.
  5. Relationship Query (optional):
    If you are creating objects with the Object Query set in the previous step, you can relate those objects in the Knowledge Graph.

  6. Optionally, select whether you would like to restrict access to this data source instance. By default, restricted access is set to off.

  7. Click Add.

    You can also add a data source from Settings > Data Sources > Add data source, but sample dashboards are not added when using this method.

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

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

Using the ODBC data streams

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

Data streams standardize data from all the different shapes and formats your tools use into a straightforward tabular format. While creating a tile you can tweak data streams by grouping or aggregating specific columns. Depending on the kind of data, SquaredUp will automatically suggest how to visualize the result, for example as a table or line graph.

Data streams can be either global or scoped:

  • Global data streams are unscoped and return information of a general nature (e.g. "Get the current number of unused hosts").
  • A scoped data stream gets information relevant to the specific set objects supplied in the tile scope (e.g. "Get the current session count for these hosts").

Data streams

The following data streams are installed with this plugin.

When using the data source to create data streams you have the option to create three different query types, as detailed below. The use of dynamic {{mustache}} properties is supported and is detailed following the types.

Mustache Parameters

A mustache parameter is a dynamic value, the actual value will be inserted to replace the field in curly braces. For example, {{timeframe.start}} will insert the start time based on the timeframe configured within the tile, or {{name}} will insert the name of the object(s) in scope.

Check the relevant data source documentation to see which fields accept mustache parameters. See Data Sources

  • Only scoped data streams are able to insert properties from objects in scope, such as sourceIds, sourceId and targetNodes. They will also be able to use other properties such as timeframe.
  • Global data streams are able to use other properties, such as timeframe.

See Data Streams

Was this article helpful?

Have more questions or facing an issue?
Submit a ticket