I have configured bigquery as data source in grafana open source. alert destination in slack.
I have a table called transactions in which i have a column called user ids.
I want to alert using grafana , evaluation time is each day.
My requirement is:
For each user ids, if number of transactions is lesser than previous week, then i need to trigger alert . How to do this?
What i did is in Alert rule:
Query A returns these columns
date,count of transactions of user 1, count of transactions of user 2,…
Query B: returns
date, count of transaction of user 1 last week,count of tranaction of user 2 last week,…
C: Reduce with Last function, this returns multiple series
D: Reduce with Last function, this returns multiple series
E: Alert condition: Math expression: $C<$D
But this is not working properly since there are multiple series it is giving combinations of all users, but i just want to compare Series 1 of C with Series 1 of D and so on.
Hi @akshayc262,
A math expression like $C<$D will compare series with the same label set.
Here is an example using the TestData data source.
Hope this resolves your question!
Thanks, this test data worked for me.But the bigquery data source is not working.
Query A:
SELECT
TIMESTAMP(DATE(ingested_at)) AS ingst_time,
COUNT(CASE WHEN account_id = 100 THEN 1 END) AS account_id_1,
COUNT(CASE WHEN account_id = 101 THEN 1 END) AS account_id_2,
FROM
my_table
WHERE
source_id =2
AND DATE(ingested_at) >= ‘2024-10-01’
AND DATE(ingested_at) < CURRENT_DATE()
GROUP BY
ingst_time
ORDER BY
ingst_time;
Query B:
SELECT
TIMESTAMP(DATE(ingested_at)) AS ingst_time,
COUNT(CASE WHEN account_id = 100 THEN 1 END)/10 AS account_id_1,
COUNT(CASE WHEN account_id = 101 THEN 1 END)/10 AS account_id_2,
FROM
my_table
WHERE
source_id =2
AND DATE(ingested_at) >= ‘2024-10-01’
AND DATE(ingested_at) < CURRENT_DATE()
GROUP BY
ingst_time
ORDER BY
ingst_time;
Expression C: Reduce: Last of A
Expression D: Reduce : Last of B
E: Math expression : $C<$D :This is alert condition,
This is actually giving 4 conditions but not 2.
Please help me on this
