Hide query's based on variable

Grafana
1

Hi i have a panel with query A and B, i would like the panel to only show query A or B dependent on a variable

anyone know how to achieve this?

2

can you please share what type of datasource do you have for these queries?

3

Hi Yosiasz,

I’m using PostgreSQL!

Here’s some more info on the queries:

Query A uses PostgreSQL’s LAG function to compare the timestamp of an error with the timestamp of the previous error that has the same error_code and product_id. If the time between the two errors is less than 40 seconds, they are grouped together.

Query B simply displays all errors filtered by product_id and error_code (set via variables), within the dashboard’s time range.

The idea is to create a data link from Query A that sets the variables for product_id and error_code, adjusts the dashboard time range (from the first to the last timestamp of the grouped errors), and then switches the panel to Query B.

4

Will Query A be displayed to the user in some sort of visualization (which one? table, time series) and when they click on visualization for Query A, Query B is filtered using values product_id and error_code from selected Query A “row” ?

5

Hi,

At the moment both panels are table panels, i have a data link on the error code cell, clicking on the datalink in Query A will pass the entity_id and code to variables and put the dashboard timerange to: from start_epoch_ms to end_epoch_ms.
this way query B displayed the errors that are in the group you pressed in query A.

Query A

Groups errors if they occur within 30 minutes of each other using the LAG window function:

WITH cte1 AS (
    SELECT
        entity_id,
        event_time,
        code,
        type,
        description,
        severity,
        LAG(event_time) OVER (
            PARTITION BY entity_id, code 
            ORDER BY event_time
        ) AS prev_event_time
    FROM event_log_table
    WHERE 
        severity IN ('critical', 'non_critical')
        AND $__timeFilter(event_time)
        AND entity_id = '$entity_id'  
),
cte2 AS (
    SELECT *,
        CASE 
            WHEN prev_event_time IS NULL 
                 OR event_time - prev_event_time > INTERVAL '40 seconds' THEN 1
            ELSE 0
        END AS new_group_flag
    FROM cte1
),
cte3 AS (
    SELECT *,
        SUM(new_group_flag) OVER (
            PARTITION BY entity_id, code 
            ORDER BY event_time
            ROWS UNBOUNDED PRECEDING
        ) AS group_id
    FROM cte2
)
SELECT
    entity_id AS "Entity ID",
    code AS "Code",
    severity AS "Severity",
    type AS "Type",
    description AS "Description",
    MIN(event_time) AS "First Occurrence",
    MAX(event_time) AS "Last Occurrence",
    EXTRACT(EPOCH FROM MIN(event_time)) * 1000 AS start_epoch_ms, -- time formatted for datalink
    EXTRACT(EPOCH FROM MAX(event_time)) * 1000 AS end_epoch_ms, -- time formatted for datalink
    MAX(event_time) - MIN(event_time) AS "Duration",
    COUNT(*) AS "Number of Occurrences"
FROM cte3
GROUP BY
    entity_id,
    code,
    type,
    description,
    severity,
    group_id
ORDER BY "Last Occurrence" DESC;

Query B

Displays all errors filtered by variables and the dashboard’s time range:

SELECT
    event_time AS "Error Timestamp",
    entity_id AS "Entity ID", 
    code AS "Code",
    type AS "Type",
    description AS "Description",
    severity AS "Severity"
FROM event_log_table
WHERE
    entity_id IN ($entity_id) 
    AND code IN ($code)
    AND severity IN ($severity)
    AND $__timeFilter(event_time)
ORDER BY event_time DESC;

Any help on how to conditionally switch between these two queries would be greatly appreciated! i might be overcomplicating things, and i would love some feedback!

7

here is a hint for your data link, play with it
${product_id:queryparam}${__data.fields.product_id}

generates
image

in the datalink when you start typing $ you will start to see all kinds of treasures :gem_stone: :coin: :oyster: