Grafana

Grafana is a open source, easy to use, tool to create interactive time series graphs. Attached the step by step description how to access the data base with grafana and how to create new dashbords and tables.

We have created some dashboards to explore our data. Here is an example how it looks:

Access and permission

Grafana can be accessed on the managed server with a browser. The URL is: http://192.168.70.23:3000. You need a username and password. If you don’t have one please ask for it ehub@empa.ch.

Creating a new dashboard

You can create dashboards as follows:

  1. Open the Grafana WebUI and login with your credentials
  2. In the left hand pane, select “Dashboards” > “Manage”. See figure below open dashboard management if you are unable to find it.
  3. Hover over a folder, wait for the settings icon to appear and click on it. See Figure 43 Grafana open dashboard folder settings if you are unable to find it.
  4. Open the tab “Dashboards” and select “New Dashboard”

Add panels and queries

After you created a new dashboard, you can add panels to it to display data in different forms of graphs, tables, or single stat panels. To add a panel, click “New Panel” in the top right corner of the dashboard. You now have the option to select between “Add Query” or “Choose Visualization”. It does not matter which one you select, both will lead you to the same dialog. It is just a matter of preference what you wish to configure first:

Query

You can choose the desired data source in the top left corner of the dialog “Query”. On the right hand side, you have the option to add additional queries with different or with the same datasource. For the use cases in this document, one query is usually enough though. You can query the database tables directly with SQL, or use the stored procedure.

Example Query with stored procedure

The following example shows how you can use the stored procedure within the NEST TEST Database to return time series data that is already time zone classified:

USE [NestTest]

DECLARE @RC int
DECLARE @listOfSystemId varchar(max)
DECLARE @listOfDeviceId varchar(max)
DECLARE @listOfNumericId int
DECLARE @theStartDate datetime
DECLARE @theEndDate datetime
DECLARE @WithData int
DECLARE @WithPivot int
DECLARE @Language int
DECLARE @Datapoint_Unit varchar(max)
DECLARE @Location_RoomNr varchar(max)
DECLARE @SystemDescription varchar(max)
DECLARE @Datapoint_SignalDescription varchar(max)
DECLARE @Datapoint varchar(max)
DECLARE @AKS_Code varchar(max)

--Date and Time Calculations
DECLARE @UTC_Start BIGINT
DECLARE @UTC_End BIGINT
DECLARE @Start_Normalized DATETIME
DECLARE @End_Normalized DATETIME
SET @UTC_Start = $__from
SET @UTC_End = $__to
SET @Start_Normalized = DATEADD(hh,2,DATEADD(MILLISECOND, @UTC_Start % 1000, DATEADD(SECOND, @UTC_Start / 1000, '19700101')))
SET @End_Normalized = DATEADD(hh,2,DATEADD(MILLISECOND, @UTC_End % 1000, DATEADD(SECOND, @UTC_End / 1000, '19700101')))

declare @temp TABLE(
  "time" datetimeoffset,
"'65NT_U30R8_B871_M00 / Temperature sensor in °C'" decimal(38,5),
"'65NT_U30R8_B871_M01 / Humidity Sensor in rfH %'" decimal(38,5),
"'65NT_U30R8_M201_D00 / Humidification pump Run in 1/0'" decimal(38,5),
"'65NT_U30R8_Y700_D00 / Hygiene valve open in 1/0'" decimal(38,5),
"'65NT_U30R8_M201_D09 / Humidification pump On temperature limit in °C'" decimal(38,5)
);

INSERT @temp
EXECUTE @RC = [usp].[uspSingleLine37Grafana_Sascha] 
   @listOfSystemId=''
  ,@listOfDeviceId=''
  ,@listOfNumericId='42110457,42110458,42110463,42110464,42110663'
  ,@theStartDate= @Start_Normalized
  ,@theEndDate= @End_Normalized
  ,@WithData= 1
  ,@WithPivot= 1
  ,@Language= 1
  ,@Datapoint_Unit=''
  ,@Location_RoomNr=''
  ,@SystemDescription=''
  ,@Datapoint_SignalDescription=''
  ,@Datapoint=''
  ,@AKS_Code=''
  
SELECT
"time",
"'65NT_U30R8_B871_M00 / Temperature sensor in °C'",
"'65NT_U30R8_B871_M01 / Humidity Sensor in rfH %'",
"'65NT_U30R8_M201_D00 / Humidification pump Run in 1/0'",
"'65NT_U30R8_Y700_D00 / Hygiene valve open in 1/0'",
"'65NT_U30R8_M201_D09 / Humidification pump On temperature limit in °C'"

FROM @temp
ORDER by time ASC;

In order to account for the time offset, the results of the stored procedure are put into a temporary table, and are later selected with the offset for the time zone. The statement will return a time series, which means that each datapoint returned will have a corresponding time. Grafana is a bit picky when it comes to finding the time column. If the column is not explicitly named “time”, Grafana will return an error that not time column could be found.

Order management

The stored procedure does return data in an increasing order of @listOfNumericId via the stored procedure. If you set the @listOfNumericId parameter of the stored procedure to “42110458,42110457”, the stored procedure will sort the columns by an increasing of @listOfNumericId and return the results for the NumericID ending in 457 before 458. This means, that have to check the return order before building the Grafana query with the temp table, so that the correct values are written in the correct temporary table columns.

A graph shows one or more datapoints over time. This is useful if you want to see if datapoint correlate in some way, or if you want to have an overview of multiple systems over time. When visualizing data over time, this is the graph usually used by the author. It comes out of the box when installing Grafana. For more information about the Grafana Graph panel, refer to the Grafana Labs documentation: https://grafana.com/docs/features/panels/graph/

Row management

Dashboards with multiple visualization can get overwhelming and confusing quite fast. In addition, having many visualizations on the same dashboards can impact performance significantly, because changing the time selector will result in each query to be executed again. To solve this issue, you can either put visualizations in different dashboards, or use rows to categorize the visualizations. When a certain row is irrelevant to your task, you can retract it and its dashboards. While a row is retracted, the data of its visualizations will be not updated if changes to f. e. the time selector are made.

You can create rows as follows:

  1. Open the Grafana dashboard to which you want to add rows to
  2. Select “Add panel” > “Convert to row”