Sisense alert

Maintained by:

What is a SiSense SQL alert?

A SiSense SQL alert can run SQL on a schedule and trigger an email when a certain criteria or rule, such as a threshold or target, is met. This email can optionally generate a message in a slack channel.

How do I create one?

Identify the graph that shows the data you want to alert on
View the source SQL
Expand the source SQL to see the raw SQL if it contains variables. To do this, select Query underneath the SQL.
Copy/paste the query into a new exploration
Change the query to aggregate per day if it aggregates on a longer time frame
Determine the range for what is "too high" and what is "too low". To do this you may want to copy the data using the "multiple file" icon which has the tooltip Copy to clipboard and paste it to a Google sheet.
Change the query to use a SQL case statement that returns 0 if everything is ok and 1 if it should alert.
Choose the bell icon on the far right to show alerts.
Paste in your SQL, set the criteria and set the email (which can be directed to a slack channel if desired) to send when the criteria is met.
Test your alert by setting the frequency to be often (hourly) and reverse the criteria (so it fires when things are ok) to make sure it works. After you get the first alert, set the criteria back and confirm an hour later you are no longer receive the alert.

Hints

Every time you change the query in a small way, test to make sure it still works. The error messages from SiSense are not always clear on what may be broken. Update your SQL iteratively!
Using one standard deviation from the average is often a good choice for determining if a metric is outside of a normal range
An effective alert may be, each day, compare the average over the last 7 days to see if it is higher or lower than the average +/- the standard deviation over the last 30 days.

Psuedo-code example

Query from the graph:

select date_trunc(week,created_at_date) as day, count(*) as widgets_created_per_day
from widget_log
where created_at_date < DATEADD(day,-90,CURRENT_DATE()
group by 1

Query changed to be daily:

select date_trunc(day,created_at_date) as day, count(*) as widgets_created_per_day
from widget_log
where created_at_date < DATEADD(day,-90,CURRENT_DATE()
group by 1

Average over the last 7 days

select avg(widghets_created_per_day) from (
select date_trunc(day,created_at_date) as day, count(*) as widgets_created_per_day
from widget_log
where created_at_date < DATEADD(day,-7,CURRENT_DATE()
group by 1) as T

Average over the last 30 days

select avg(widghets_created_per_day) from (
select date_trunc(day,created_at_date) as day, count(*) as widgets_created_per_day
from widget_log
where created_at_date < DATEADD(day,-30,CURRENT_DATE()
group by 1) as T

Standard deviation over the last 7 days

select stdev(widghets_created_per_day) from (
select date_trunc(day,created_at_date) as day, count(*) as widgets_created_per_day
from widget_log
where created_at_date < DATEADD(day,-7,CURRENT_DATE()
group by 1) as T

Example for an alert if metric is above acceptable threshold

Change the > to a < for checking it is under (in a separate alert)

select case when 
/* copy above average over the last 7 days */
> 
(
/* copy above average over the last 30 days */
+
/* copy above standard deviation over the last 30 days */
)
then 'alert'
else 'ok'
end

Where can I find an example?

Confidential inside GitLab due to specific metrics that were discussed that are not things we can be publicly transparent about