Query help with Status Panel

Grafana Stat Panel
1

My query below is looking at a single sensor, but I’m not seeing anything on the panel. I would like to have one square for every sensor with the sensor name so we can see what the status is of each sensor, and remove the sensor = ‘sensor1’ restriction from my query.

But I’m having some problems creating queries for Grafana and getting the results I want in panels. I think I’m missing some fundamental bit of knowledge that would connect the dots for me, but I’m not finding it in the documentation.

Can anyone point me in the right direction to figure out what I’m missing here?

image
image1904×964 92.8 KB

2

Welcome @aforsythe to the :grafana: forum

First of all, in Datasources >> MSSQL, when you click Save & Test, do you get the success?

image
image989×532 44.6 KB

Second (assuming the above results in :white_check_mark: success), I believe the error message
image

means that it doesn’t know what tblname is. Does the above query work fine in Sql Server Management Studio?

3

Thanks Grant, I didn’t think about the confusion it might cause when I changed the tablename for the post. So please ignore that error.

The rest of the syntax is correct and produces the following results:
image

What kind of result is Grafana really after from Queries. I am unable to find examples for most panels, so I have to assume or guess what the results set should look like to satisfy the panel and I’m having trouble figuring out what fundamental concept I’m missing here.

I see a series that needs a value that gets compared to a threshold and causes a color shift on a panel. So I feel like I need a series name (metric) and a value (value) but then the panel tells me that I also need a time or time_sec. So I add in an arbitrary value of now for that, and I still get nowhere.

The only panel I’ve tried using so far that I don’t have this problem with is a line graph charting the number of sensor trips over time grouping every half hour, and after I figured out the unix time requirement, that panel just works as perfectly as I could hope for.

I’ve read through the documentation on Grafana, but wasn’t able to find anything that would help me here. I think some example queries in the panel documentation might help myself and others get started much faster and easier.

4

Hi @aforsythe

I am not sure how much you have played around with Grafana, but you can go here and play around. Keep in mind that Grafana was originally developed to view time series data (and that is what it really excels at), but you can also use it for pie charts, bar graphs, etc. which do not include a time series.

Regarding your original question re: the sensor, what is your ideal looking panel, i.e. what do you want to see in Grafana with this sensor? For example:

how it looks over time:
image

how it looks now:
image

5

I really appreciate your help Grant, here is my ultimate desire with our data here:

image
image1884×537 192 KB

I will have multiple sensors for multiple hosts and I would love to know what my result set needs to look like to achieve the dashboard pictured above with the status panel.

Right now, I’m just trying to get it to report a single sensor on a single host, and I’m not even including the host name. Do I need that?

6

@aforsythe

Is the datasource Prometheus or InfluxDB or something else? If InfluxDB, is it configured for InfluxQL or Flux?

Was the screenshot you posted from some other example created by another person? Does your data have just 3 status levels (green/yellow/red) for each server (or sensor or whatever it is that you are monitoring)?

7

That’s a screenshot from the Grafana website for the status panel.

Our data is coming from a custom event collector and is being corelated into a status table based on SNMP data, syslogs, Pings, etc…

Then I’m running a SQL query from Grafana to collect the data. I’m just not sure what kind of result set it’s looking for. How many columns? Is there a naming convention required? Etc…

Does Grafana just not work this way? I would think so considering that it allows data source connections and custom queries.

The result set I posted is just my query in SQL Server Management Studio. The Data Source is good and is used for another dash board, but when I run the query for the panel, no errors, the panel just stays blank when I try to refresh it.

I’ve edited my query multiple times adding columns, reordering columns, I just don’t know what Grafana is expecting and there is no information that I can find to tell me how to structure my query.

8

OK, so this query (in SQL Server Management Studio)…

image
image1327×139 55.9 KB

results in this, right?

image

But when you paste the same query (working in SSMS) into Grafana, you get a blank panel, right?

@yosiasz is a wizard when it comes to getting SQL queries to work in Grafana.

9

That is correct. I also have another modification to the query that adds a column ‘hostname’, but I get the same results with it.

I can get display results with other panels too btw. Like I can just switch the panel type to bar chart and then I get a green bar of 4 on the screen.

So the query works, the datasource connection is good. Either the status panel is misconfigured or it’s expecting different results from what I’m delivering. If I can figure this out, I’m sure it will answer my other panel questions that have similar results.

10

image
image1459×694 93.6 KB

using this as fake sample data

select 4 value, 'Sensor01' as metric, 1669967496 as time union
select 23 value, 'Sensor02', 1669967496 as time union
select 13 value, 'Sensor03', 1669967496 as time

@grant2 you are too kind. I don’t consider myself wizard

11

Thanks @yosiasz, I appreciate your assistance here as well.

You had originally asked for more detail on the data. so I have posted below, but I will also try your example and see if that answers my question and gets me to my goal…

Sample Table:
CREATE TABLE sensorlogs (
Hostname Varchar(40), – Hostname or IP
Sensor Varchar(40), – Sensor Description
Mode Int, – Mode of communication delivering sensor status, SNMP, Ping, Syslog, eventlog, etc. 5.
Status Int, – Status depends on Mode
UpdateTimeUTC Datetime – Datetime of record entry
)

Sample Data:
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,1,‘2022-12-02 01:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,2,‘2022-12-02 02:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,3,‘2022-12-02 03:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,4,‘2022-12-02 04:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,1,‘2022-12-02 05:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,2,‘2022-12-02 06:00:00.000’)
Insert Into sensorlogs (Hostname, Sensor, Mode, Status, UpdatetimeUTC ) Values (‘TestHost’,‘Sensor1’,5,1,‘2022-12-02 07:00:00.000’)

And my current Query to test 1 sensor on 1 hostname:
Select HostName, count(*) As Value, – Hostname should be self explanatory, Value is a count of the Status 1& 2 for Mode 5 over a 7 day period.
Sensor As metric, – Only labeled as metric as a test to see if the column name made any difference to the functionality of the panel based on an example I saw somewhere.
DATEDIFF(s,‘19700101 05:00:00:000’,getdate()) As time – I’m not concerned about time for this particular status, just the number of times it’s been out for this particular metric.
Where Hostname = ‘testHost’ – This is just for testing a single sensor on a single host right now, there is other data in the table currently
And (Status NOT IN (3,4)) And (Mode = 5) – Only looking for mode 5 and any status other then 3/4
Group by Hostname – I would imagine this would end up grouping by Hostname and Sensor in the end.

And lastly, hopefully a decent explanation of the data and our goal:
In our example, we are looking at a single Sensor, ‘Sensor1’ for TestHost
The mode of the sensor is 5 which is a syslog message giving an up/down/trouble state for a windows service.
The down status is 1, trouble is 2 and up is 3, 4 is out of service which is why it was also excluded from the result set in the query.

The end result goal is to have a different box in the Status Panel for each hostname monitored and per the description of the Status Panel regarding
its capabilities, Green if all is good, Orange if sensors are outside of threshold and RED if ALL sensors are outside of threshold.

1 Like
12

Have you tested this script?

13

Your script or my script?

14

I’m sorry, I have made so many edits trying to test this and then adding comments to the query to explain it…

I had accidently dropped the from and said I needed to fix the group-by but then didn’t do it. The below query is correct, but still does nothing.

Select Hostname, count(*) As Value, – Hostname should be self explanatory, Value is a count of the Status 1& 2 for Mode 5 over a 7 day period.
Sensor As metric, – Only labeled as metric as a test to see if the column name made any difference to the functionality of the panel based on an example I saw somewhere.
DATEDIFF(s,‘19700101 05:00:00:000’,getdate()) As time – I’m not concerned about time for this particular status, just the number of times it’s been out for this particular metric.
From sensorlogs
Where Hostname = ‘testHost’ – This is just for testing a single sensor on a single host right now, there is other data in the table currently
And (Status NOT IN (3,4)) And (Mode = 5) – Only looking for mode 5 and any status other then 3/4
Group by Hostname, sensor – I would imagine this would end up grouping by Hostname and Sensor in the end.

15

please post your final result you want to see in a sample data also with ddl & dml.
Your requirements are very nice and detailed but a bit verbose and one gets lost in them

16

@yosiasz Thanks for your time and assistance on this, I greatly appreciate it, but after playing with your example above, it’s not my query that is the problem.

I’m unable to achieve the same results you achieve with the sample query you posted, and it shows that what I was originally trying to feed into this panel was correct to begin with, I’m just missing something else.

image
image1892×970 77.2 KB

image
image1159×941 30.8 KB

If you can help me figure out why your sample query isn’t working for me the same as it is for you, I think that might be a faster and better solution than helping me out with my own sample data that will likely have the same result.

I’m guessing that I’m missing a display setting somewhere, but I’ve been playing around with this and reading documentation for a couple hours now and haven’t found it.

If I switch to table data, I can select between Sensor1, Sensor2, and Sensor3, but as you can see above, my panel is solid green with only what I put in the title field showing.

17

import this dashboard and see. It works on my computer :laughing:

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "grafana",
          "uid": "-- Grafana --"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "target": {
          "limit": 100,
          "matchAny": false,
          "tags": [],
          "type": "dashboard"
        },
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": 35,
  "links": [],
  "liveNow": false,
  "panels": [
    {
      "datasource": {
        "type": "mssql",
        "uid": "4HBRT3G4k"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "id": 2,
      "options": {
        "colorMode": "background",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": true
        },
        "textMode": "auto"
      },
      "pluginVersion": "9.2.3",
      "targets": [
        {
          "datasource": {
            "type": "mssql",
            "uid": "4HBRT3G4k"
          },
          "editorMode": "code",
          "format": "time_series",
          "rawQuery": true,
          "rawSql": "select 4 value, 'Sensor01' as metric, 1669967496 as time union\r\nselect 23 value, 'Sensor02', 1669967496 as time union\r\nselect 13 value, 'Sensor03', 1669967496 as time ",
          "refId": "A",
          "sql": {
            "columns": [
              {
                "parameters": [],
                "type": "function"
              }
            ],
            "groupBy": [
              {
                "property": {
                  "type": "string"
                },
                "type": "groupBy"
              }
            ],
            "limit": 50
          }
        }
      ],
      "title": "Panel Title",
      "transformations": [
        {
          "id": "prepareTimeSeries",
          "options": {
            "format": "many"
          }
        }
      ],
      "type": "stat"
    }
  ],
  "schemaVersion": 37,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-2d",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "Stats Time Series",
  "uid": "tSpGafKVz",
  "version": 2,
  "weekStart": ""
}