Grafana/Timestream: how to visualize number of certain events over time

Question

I am new to Grafana and Timestream and want to expore their suitability for my needs.

My goal is to visualize a number of certain events in the logs.

I have a field repsesenting a session id (cmcd_sid ) and some indicator - cmcd_bs:

cmcd_sid                                   cmcd_bs         <other fields>

"78b459fc-9fab-4286-8840-8f1420f528da"     -

"78b459fc-9fab-4286-8840-8f1420f528da"     -

"78b459fc-9fab-4286-8840-8f1420f528da"     true

So what I need is to get and visualize number of sessions (cmcd_sid) that have more than 1% of cmcd_bs=true

WITH total_requests AS (
    SELECT cast (count(*) as double) as CNT_TOTAL, cmcd_sid as SID_TOTAL FROM $__database.$__table
    WHERE cmcd_sid is not NULL
       AND time > ago(3h)
       GROUP BY cmcd_sid
), bs_requests AS (
    SELECT cast (count(*) as double) as CNT_BS, cmcd_sid as SID_BS FROM $__database.$__table
    WHERE cmcd_bs is not NULL
       AND time > ago(3h)
       GROUP BY cmcd_sid
)
SELECT count(DISTINCT SID_BS), SID_BS
FROM total_requests, bs_requests
WHERE SID_TOTAL = SID_BS
  AND CNT_BS / CNT_TOTAL * 100 > 1
GROUP BY SID_B

I wrote a query that gave me those sessions but when I try to visualize it in Grafana it throws "Data does not have a time field"

Ideally, I want to have a graph depicting how many these sessions I have for a period of time but not sure how I can incorporate time field into the query?

MaFF · Accepted Answer · 2022-01-12T12:02:41.280

1. Type of visualization

The Time Series graph in Grafana expects a time field, you can choose other types of visualization in the top right corner of the edition panel. The total number of sessions with more than 1% of cmcd_bs = true, seems to be one single number. If so you can choose graph type "Stat" or a "Gauge" for instance.

2. Timestream query

Note that:

The final group by in your query has no impact since the 2 CTEs are already groupped by this column.
You can combine the 2 CTEs into one since they group by the same key.
cmcd_bs is not NULL is not equivalent to cmcd_bs = true You can therefore rewrite your query:

WITH
    session_stats AS (
        SELECT 
            cmcd_sid, 
            SUM(CAST(cmcd_bs AS INT)) / CAST(COUNT(*) AS DOUBLE) AS true_bs_rate
        FROM $__database.$__table
        WHERE 
            cmcd_sid IS NOT NULL
            AND time > ago(3h)
        GROUP BY cmcd_sid
    )
SELECT
    COUNT(*) AS high_true_bs_rate_session_count
FROM session_stats
WHERE true_bs_rate > 0.01

3. Time filter

You have a fixed time filter in your query time > ago(3h). Therefore the value won't change if you change the period in Grafana (you can select the time period in the top right corner of the main dashboard). To only count records during the period of time defined in Grafana, you have to pass $__timeFilter to your query:

WITH
    session_stats AS (
        SELECT 
            cmcd_sid, 
            SUM(CAST(cmcd_bs AS INT)) / CAST(COUNT(*) AS DOUBLE) AS true_bs_rate
        FROM $__database.$__table
        WHERE 
            cmcd_sid IS NOT NULL
            AND $__timeFilter
        GROUP BY cmcd_sid
    )
SELECT
    COUNT(*) AS high_true_bs_rate_session_count
FROM session_stats
WHERE true_bs_rate > 0.01

4. Time Series visualizations

If what you want is not a single value, but rather the evolution of that daily (or weekly, monthly, ...) count over time. You can group by the desired period instead of aggreating over the whole dataset:

WITH
    session_stats AS (
        SELECT 
            cmcd_sid, 
            MIN(time) AS session_start_time,
            SUM(CAST(cmcd_bs AS INT)) / CAST(COUNT(*) AS DOUBLE) AS true_bs_rate
        FROM $__database.$__table
        WHERE 
            cmcd_sid IS NOT NULL
        GROUP BY cmcd_sid
        HAVING 
          MIN(time) >= from_milliseconds(${__from}) 
          AND MIN(time) < from_milliseconds(${__to})
    )
SELECT
    DATE_TRUNC('day', session_start_time) AS day,
    COUNT(*) AS high_true_bs_rate_session_count
FROM session_stats
WHERE true_bs_rate > 0.01
GROUP BY DATE_TRUNC('day', session_start_time)

Note: the sessions could be truncated by $__timeFilter and the count of cmcd_bs = true would be biased for these sessions, this is why the time filter is done after the first group by. This also applies to the other queries.

Grafana/Timestream: how to visualize number of certain events over time

1 Answers1