Skip to main content

ODBC on-premise plugin

Documentation Team
  • Updated

ODBC on-premise plugin

Visit our website to see the data that you can access if you use this plugin to add the data source to SquaredUp:

ODBC

Connects to any on-premises ODBC data resource.

Note: This data source is an on-premises data source.

An on-premises data source connects a service running in your internal network to SquaredUp. They require an agent installed on a machine that has access to your internal network.

How to add an ODBC data source

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.

Configure and deploy 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.

  1. Create a unique API key for your agent and add the agent to an agent group in SquaredUp.

    You create an API key by creating an agent in SquaredUp:

    1. Go to Settings > Relay and add an Agent.

    2. Give the new agent a name and a description that helps you identify where the agent is installed. For example:
      Name: server1.domain.local

      Description: Test server in production domain

    3. Choose the Agent Group for this agent:

      1. If you already have agent groups, assign it to an existing group and click create.

      2. If you don't have any agent groups yet or want to assign the new agent to a new group, leave the Agent Groups field empty and click create. Then create the agent group by clicking on Add Agent Group and select the new agent in the Agents field for the new group.

    4. After you created the agent, the API key for this agent will be shown to you. Copy the key and store it until you inserted the key into the configuration of the agent you want to deploy on your machine.

      The API key will only be displayed to you once. If you lose this API key, you need to generate a new one (by creating a new agent) and any references to the old API key in the configuration of the agent you deployed on your machine will need to be updated.

    5. The agent status will show as gray until the next stage of configuring the service is completed successfully.

  2. Deploy the agent on a machine that has access to the service the data source connects to.

    1. Download the latest release of the SquaredUp agent zip file, by clicking the download icon under Options next to the agent you have just added.

      • The agent needs to run on a Windows machine that has access to the entry point for the on-premises data source

      • Make sure the agent is able to make outbound connections on port 443 (no inbound required) to SquaredUp, *.amazonaws.com and Microsoft APIs (Azure Relay).

      • Optional DNS-based restrictions: *.servicebus.windows.net

      • For information about Relay Agent versions see Release Notes - Relay Agent

    2. On a Windows machine, with access to the entry point your data source needs to use, extract the downloaded zip file.

    3. In the folder of the extracted zip file, open PowerShell as an administrator and run the following command:

      Copy
      ./Install-SQUPAgent.ps1 -ApiKey "key" -AsService -ServiceSuffix "name" -ServiceAccount domain\username

      Parameters to replace:

      -ApiKey "key"MandatoryReplace key with the API key you created for the agent in SquaredUp
      -AsServiceRecommendedRun the agent as a service on the machine
      -ServiceSuffix "name"OptionalTo change the default service name of squpagent replace name with your new service name.
      -ServiceAccount domain\usernameOptionalTo run the agent as a domain service account (for example, for the SCOM data source), provide the username as domain\username and it will prompt for the password when it sets up the service
      -InstallPathOptionalSpecify a folder location for where the agent will be installed. If this is not specified then the agent will be installed in the folder where the zip file is extracted.

      Consider restricting access to the folder where the agent is installed to prevent anyone from updating or viewing the configuration files.

      4. By default, the SquaredUp agent service uses the local system identity, but this can be changed to a domain service account if required, for example for the SCOM data source.

      • Configure a domain service account using the installation script, for example:

        ./Install-SQUPAgent.ps1 -ApiKey "key" -AsService -ServiceAccount domain\username

        where key is the API key, and domain\username is the domain service account

      • Alternatively, in Services > SquaredUp Cloud Agent > Properties select the account on the Log On tab.

      • Use a dedicated user account for the agent's service identity. Create a special service account for this domain service account, do not use an existing user account.

      • The account (typically a service account) needs to have the log on as a service permission.

    4. Adjust any permissions for the service and start the service.

      You can start the agent service from Services > SquaredUp Cloud Agent, or using PowerShell using either:

      Start-Service -Name <ServiceName>

      Where <ServiceName> should be replaced with the service name shown in brackets in the upgrade script output (or Properties of the service).

      For example:

      Start-Service -Name squpagent

      or

      Start-Service -DisplayName <DisplayName>

      Where <DisplayName> should be replaced with the service name shown before the brackets in the upgrade script output (or Properties of the service).

      Look at the Properties of the SquaredUp Cloud Agent service:

      1. On the server running the agent, open Services

      2. Scroll down to the SquaredUp Cloud Agent in the list

      3. Right-click on the SquaredUp Cloud Agent service and then Properties

      4. Here you can see the Service name, Display name and Path to the agent folder.

      5. You can also start or stop the service from here.

    5. Check the agent status in SquaredUp  Settings > Relay
For more information see Configuring an agent for on-premises data sources

Add an ODBC data source in SquaredUp

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

  2. Display Name:

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

  3. Agent Group:

    Select the Agent Group that contains the agent(s) you want to use.

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

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

    These plugins support the creation of objects from a database query. The resulting table must contain sourceId, name, and type fields. Additional fields are allowed, and any non-null fields will be included with the object when imported into the Knowledge Graph.

    A sample query result is below:

    sourceId

    name

    type

    initials

    operating_system

    abeLincoln

    Abraham Lincoln

    user

    AL

     

    server_1

    Infra-SQL01.local

    server

     

    windows

    In the above sample table two objects will be created in the Knowledge Graph. The first will be of type user and have a sourceId of abeLincoln and a single property for initials. The operating_system attribute will not be added to this object as it has a null property. The second object created will be a server with sourceId server_1 and a single property of operating_system.

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

    Along with an Object Query an additional Relationship Query can also be used. The result of this query must contain the fields inV, outV, and label. The rows of this result will be converted to relationships in the Knowledge Graph.

    A sample query result is below:

    outV

    label

    inV

    abeLincoln

    own

    server_1

    After the import is complete the two objects created from the Object Query will now be linked, with Abraham Lincoln owning Infra-SQL01.local

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

    The term data source here really means data source instance. For example, a user may configure two instances of the AWS data source, one for their development environment and one for production. In that case, each data source instance has its own access control settings.

    By default, Restrict access to this data source is set to off. The data source can be viewed, edited and administered by anyone. If you would like to control who has access to this data source, switch Restrict access to this data source to on.

    Use the Restrict access to this data source dropdown to control who has access to the workspace:

    • By default, the user setting the permissions for the data source will be given Full Control and the Everyone group will be given Link to workspace permissions.

    • Tailor access to the data source, as required, by selecting individual users or user groups from the dropdown and giving them Link to workspace or Full Control permissions.

    • If the user is not available from the dropdown, you are able to invite them to the data source by typing in their email address and then clicking Add. The new user will then receive an email inviting them to create an account on SquaredUp. Once the account has been created, they will gain access to the organization.

    • At least one user or group must be given Full Control.

    • Admin users can edit the configuration, modify the Access Control List (ACL) and delete the data source, regardless of the ACL chosen.

    Access Level:

    Link to workspace

    • User can link the data source to any workspace they have at least Editor permissions for.
    • Data from the data source can then be viewed by anyone with any access to the workspace.

    • User can share the data source data with anyone they want.

    • User cannot configure the data source in any way, or delete it.

    Full Control
    • User can change the data source configuration, ACL, and delete the data source.

    See Access control for more information.

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

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.

Creating a data stream using a configurable data stream

How to create a data stream for this data source:

  • Create a data stream using the configurable data stream form

A configurable data stream allows you to easily create new data streams specific to your needs, by entering information into a form, such as metric names or queries. Configurable data streams have a cog icon next to their name in the tile editor.

Any data stream you create can be edited by clicking the edit button (pencil) next to it in the tile editor, and also from Settings > Advanced > Data Streams.

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.

Custom Table Query

This allows for any tabular data to be pulled into to SquaredUp and be displayed in a standard table.

Custom Metrics Query

This configurable data stream type allows for data to be displayed in a visual format, such as a line graph. When writing a query for use as a Metrics query, the below three columns are mandatory and all others will be ignored.

Column Name (case sensitive)

Content

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

This configurable data stream allows for data to be displayed as a state tile (Red, Yellow, or Green blocks). When writing a query for use in a State query the below three columns are mandatory and all others will be ignored.

Column Name (case sensitive)

Content

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’

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

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

See Data Streams

Mustache parameters for unscoped and scoped data streams

timeframe

All of the properties in the calling event payload are passed through to the template builder with the addition of below three properties.

• durationSeconds

• durationMinutes

• durationHours

Example

Parameter

Replacement value

Type

{{timeframe.start}}

2022-03-13T19:45:00.000Z

string

{{timeframe.unixStart}}

1647200700

number

{{timeframe.end}}

2022-03-14T19:45:00.000Z

string

{{timeframe.unixEnd}}

1647287100

number

{{timeframe.enum}}

last24hours

string

{{timeframe.interval}}

PT15M

string

{{timeframe.durationSeconds}}

86400

number

{{timeframe.durationMinutes}}

1440

number

{{timeframe.durationHours}}

24

number

Select CustomerName, Order from dbo.Orders where orderDT > DateADD(mi, -{{timeframe.durationMinutes}},GetDate())

Mustache parameters for scoped data streams only

sourceIds

This is a unique property as some pre-processing occurs within the data source to modify the values. sourceIds will return a list of all the targeted nodes sourceId elements, concatenated with commas and string delimiters for TSQL friendly systems.

Example

Parameter

Replacement value

Type

{{sourceIds}}

'idOne','idTwo','idThree'

string

Select CustomerName, Order from dbo.Orders where customerId IN ({{sourceIds}})

sourceId

This will return the sourceId of the first target node in the query. While not as powerful as the sourceIds value, it works for systems that don't support TSQL syntax and will still allow drill-down behavior

Example

Parameter

Replacement value

Type

{{sourceId}}

idOne

string

Select CustomerName, Order from dbo.Orders where customerId = '{{sourceId}}'

targetNodes

This property represents the targetNodes on the calling event with the small addition of a first and last property on the respective nodes. This allows for Mustache templates to do a couple tricks, as noted below.

Example

Parameter

Replacement value

Type

{{targetNodes.0.sourceId}}

idOne

string

{{#targetNodes}}'{{sourceId}}'{{^last}},{{/last}}{{/targetNodes}}

'idOne','idTwo','idThree'

string

Select CustomerName, Order from dbo.Orders where customerId = '{{targetNodes.0.sourceId}}'

Select CustomerName, Order from dbo.Orders where customerId IN ({{#targetNodes}}'{{sourceId}}'{{^last}},{{/last}}{{/targetNodes}})