Info360 uses an open framework data structure so that any of its data can be queried by an outside software like Excel if proper permissions are in place. This page explains how to dynamically connect a Dashlet to an Excel spreadsheet so that you can auto extract report data.

Prerequisites

  • A connection string to the Info360 database with a login. Your IT team may be able to help with this, but Innovyze support can also provide guidance if needed.
  • Excel with Microsoft OLEDB Provider for SQL Server (most versions).
  • A working Dashlet and its UID.

 How do I find my Dashlet’s UID?
  1. Navigate to the Dashlet panel of the ribbon and set it to the table view mode.
  2. The left column of integers represents the Dashlet UID.
  3. The following screenshot, the two Dashlets returned by the search have UID’s 2176 and 2175.

How to set up connection

  1. Open a clean data sheet in Excel.
  2. Select the Data tab and choose: Get Data > From Other Sources > From OLEDB.
  3. Paste in the connection string or click the Build button and enter the server name, login, and database name.
  4. Expand the Advanced drop-down menu and paste in the query with the following format:

SELECT * FROM [{Database Name}].[dbo].[SW_ReportTable_{Dashlet UID}]

Where

      • {Dashet UID} = the integer UID value for your desired Dashlet
      • {Database Name} = the name of the Info360 database.

Example:

SELECT * FROM [SCADAWatch].[dbo].[SW_ReportTable_99]

How to query any sensor data on demand

This procedure requires advanced knowledge of Info360.

The process above to pull data from a Dashlet table can be modified to pull any data on demand if you understand how to query it. The following section explains how to build a query that pulls data for a desired time window for a specified sensor.

For this process, we will query into the SW_TSData table of Info360, which is where all the sampled sensor data is stored. Because this table far exceeds the allowable size we are able to pull to Excel, it is important to place a number of filters on what we are pulling.

  • A date range to pull data
  • A selected data interval to use (e.g. 5 min, 15 min, Hourly, etc.)
  • A desired Sample type (e.g. Average, High, Low, Close, Open)

Queries to the SW_TSData table should use the following format:


SELECT TSDateTime, {Data Series} FROM [{Database Name}].[dbo].[SW_TSData]
WHERE SensorConfigID = {Sensor UID} AND ParHash = {Sensor UID} % 600
     AND Type = {Interval ID}
       AND TSDateTime > {From Datetime} AND TSDateTime < {To Datetime}


Where:

  • {Data Series} = The desired sample type [options: Open, High, Low, Close, Average]. You can use any of these that you want, just separate them by a comma in the query.
  • {Database Name} = the name of the Info360 database.
  • {Sensor UID} = The Sensor UID that you want to query. This can be found in the leftmost column of the Data Source panel.
  • {Interval ID} = This value defines which timestep interval of sampled data you want from the sensor. Please refer to the following table for values:

    TYPEInterval

    5000

    weekly

    4500

    daily

    4000

    hourly

    3500

    30 min

    3000

    15 min

    2500

    10 min

    2000

    5 min

  • {From/To Datetime} = Used to form a timespan filter. Use single quotes in the format ‘YYYY-MM-DD HH:MM:SS’.

Do not pull more data than you need. Excessive queries could slow down the Info360 server.

Examples:

Retrieve 15 minute Average values for Sensor #2827 on June 1 of 2019:

SELECT [TSDateTime],[Average]  FROM [SCADAWatch].[dbo].[SW_TSData]
       WHERE SensorConfigID = 2827 AND ParHash = 2827 % 600
     AND Type = 3000
        AND TSDateTime > '2019-06-01' AND TSDateTime < '2019-06-02'


Retrieve the Daily High and Low values for Sensor #67 over the past 30 days:

SELECT [TSDateTime],[High],[Low]  FROM [SCADAWatch].[dbo].[SW_TSData]
       WHERE SensorConfigID = 67 AND ParHash = 67 % 600
     AND Type = 4500
        AND TSDateTime > DATEADD(DAY,-30, GETDATE())