Time series (count of values over time)

Grafana MSSQL
1

Hi all,

I’m a total Grafana newb (sorry!) and find myself having to ask what should have a very simple solution.

I’m connected to a MSSQL DB and have a SQL query that returns a table bringing back data related to an alert monitoring system. After some transformations (grouping/counting), Grafana shows my table as follows (I manually wrote this down as it would appear in a CSV):

SeverityName,RaisedDate,AlertName(count)
Warning,2024-06-20,2
Critical,2024-06-27,4
Warning,2024-07-01,2
Warning,2024-07-03,1
Critical,2024-07-03,1
[…]

Try as I might, I cannot figure out the right incantation to get Grafana to show a separate line for each SeverityName (Warning and Critical in this case). But assume there could be more severity levels.

I have 3 transformations:

  1. Convert field type
    The RaisedDate field coming back from SQL is a string, so I convert to a Time field (YYYY-MM-DD). The HH:mm:ss components are not only irrelevant here, but also unwanted. The panel should show a summary of number of alerts raised, for each severity level, for each day.

  2. Group by
    AlertName → Calculate / Count
    SeverityName → Group By
    RaisedDate → Group By

  3. Sort By
    Raised Date

I’d like my X axis to have dates, and the Y axis to show counts. I should have 2 lines: One showing the count of Warning-level alerts that have been raised on a given day, and the other showing the count of Critical-level alerts for that day. However, as I mentioned above, for all intents and purposes, there could be many other severity names being returned. In this case it just so happens that I only have 2 values, Warning and Critical.

The Time series visual draws a single line, and (because I only have 2 severity values) up to 2 dots on a single day (warning and critical). I instead want separate/independent lines for each severity.

screenshot
screenshot1045×399 19.9 KB

What am I doing wrong?

Once I have that working, ideally, each line should have a different color, but that’ll be another matter.

I would appreciate any pointers.

2

Hi,

when you want to split a time series in to multiple series, you have to use
“partition by value” with severity as field.
or you can use “prepare time series” with the multi-frame time series format.

that does the trick.

Jo

2 Likes
3

Such a simple fix - I knew it. Literally, 3 or 4 clicks from my starting point and I was done.

Thanks so much Jo.