Show alert component if certain conditions are met

[vladosam]

Hi, thank you for making this cool piece of software. I'm trying make alert when my device skips value update. This sql code i'm using.

SELECT 
    mac,
    mac::TEXT || ' Older than 30 minutes' AS status
FROM node
GROUP BY mac
HAVING MAX(created_at) IS NOT NULL AND NOW() - INTERVAL '30 minutes' > MAX(created_at)

Can I somehow show alert component if this is true? Thanks.

[lovasoa]

Hello and welcome to SQLPage !

If I understand well, you have a table that looks something like this:

CREATE TABLE node (
    mac macaddr,
    -- other fields
    created_at timestamp
);

and you want to create a web page that will contain one alert message for each value of mac that does not have an entry with a created_at that is before 30 minutes ago. Is that right ?

Here is how you could do this with sqlpage:

SELECT
  'alert' AS component,
  'Missing value update' AS title,
  mac::TEXT || ' older than 30 minutes' AS description,
  'alert-circle' AS icon,
  'red' AS color
FROM
  node
GROUP BY
  mac
HAVING
  MAX(created_at) < NOW() - INTERVAL '30 minutes'

does this answer your question ?

I made a small SQLite example you can fiddle with online on replit:
https://replit.com/@pimaj62145/SQLPage-alert-example#index.sql

[vladosam]

Yes that is exactly what i'm trying to achieve. I tried your code but nothing is showing on page. No error nothing.

[vladosam]

When I try your code on db it works. But for some reason nothing is showing on sqlpage.

[lovasoa]

Is sqlpage connection to the database working ? What does it say on the console ? What do you see in the browser ?

[vladosam]

No errors on console. Connection to db is working. I added table component and empty alert component and it's working. This is the code.

SELECT 'list' AS component, 'SQLPage' AS title;

SELECT 'N1' AS title;

select 
    'alert'              as component,
    'Error'              as title,
    'Your cart is full.' as description,
    'alert-circle'       as icon,
    'red'                as color;

SELECT
    'alert' AS component,
    'Missing value update' AS title,
    mac::TEXT || ' older than 2 minutes' AS description,
    'alert-circle' AS icon,
    'red' AS color
FROM
    node
GROUP BY
    mac
HAVING
    MAX(created_at) < NOW() - INTERVAL '2 minutes';


select 'table' as component, TRUE as search, TRUE as sort;
SELECT
    mac,
    active1,
    active2,
    maxgraf,
    reactive1,
    reactive2,
    TO_CHAR(created_at::timestamp, 'DD-MM-YYYY HH24:MI:SS') AS time
FROM
    node
ORDER BY
    id DESC
LIMIT 2;

[lovasoa]

That is strange, it indeed looks like the query is returning nothing. Can you set the environment variable RUST_LOG=debug and paste the (probably long) logs here ? This will show exactly what sqlpage sends to the database, and what it receives from it.

[vladosam]

You weren't kidding when you said it will be long log.
I paste it on pastebin.
https://pastebin.com/zRXDCBmS

[lovasoa]

Thanks ! Indeed, I can see the database is returning no data for the query:

[2023-12-01T10:38:52.715Z DEBUG sqlpage::webserver::database::execute_queries] Acquired a database connection
[2023-12-01T10:38:52.717Z DEBUG sqlpage::webserver::database::execute_queries] Finished query with result: AnyQueryResult { rows_affected: 0, last_insert_id: None }
[2023-12-01T10:38:52.717Z DEBUG sqlpage::render] -> Query 1 finished

The query does not return an error, but just does not return any row.

What you can try to do to see where the problem is coming from is display all the macs in a table:

select 'table' as component;
select mac, created_at, 
  MAX(created_at) as "latest creation", 
  (NOW() - INTERVAL '2 minutes') as "2 minutes ago",
  (MAX(created_at) < NOW() - INTERVAL '2 minutes') as should_alert;
group by mac;

[vladosam]

I'm not sure where to put this sql?

[lovasoa]

In your web page.

It will make sqlpage return a table with all the macs, and hopefully we'll see why the query with having doesn't return any row

select 'table' as component;
select mac, created_at, 
  MAX(created_at) as "latest creation", 
  (NOW() - INTERVAL '2 minutes') as "2 minutes ago",
  (MAX(created_at) < NOW() - INTERVAL '2 minutes') as should_alert;
group by mac;

[vladosam]

Yea it's not working. error returned from database: column "mac" does not exist probably because there is not from table name "node". Sorry I am a noob.

[lovasoa]

No, my bad, sorry

select 'table' as component;
select mac,
  MAX(created_at) as "latest creation", 
  (NOW() - INTERVAL '2 minutes') as "2 minutes ago",
  (MAX(created_at) < NOW() - INTERVAL '2 minutes') as should_alert
from node
group by mac;

[vladosam]

progres

[lovasoa]

Ok, it looks like a timezone problem, doesn't it ?
Is this a postgres database ?
What is the type of your latest_creation column ? It looks like it is a "naive" timestamp (without time zone), but should be interpreted as UTC+1. Right ?

[lovasoa]

If you do have control of the database and the process ingesting the data, the ideal solution would be to store latest_creation as a timestamp with time zone to remove all risk of errors both in sqlpage and in any other system connected to the database.

If you don't have control of that, you can at least specify the timezone manually if you know it. If you are certain all values in the created_at column will be stored in, for instance, Paris time, you can use AT TIME ZONE: created_at AT TIME ZONE 'Europe/Paris' everywhere instead of just created_at

[vladosam]

Yes you are right. This is postgres db. I changed timestamp for created_at after i created db. Now I am not sure how and what command I used. I need to find that. As you can see in SQLPage time is 2023-12-01T13:09:01.285074+00:00 and if I run the same sql on db it's

latest creation	2 minutes ago
2023-12-01 14:07:53.658	2023-12-01 14:08:55.933 +0100

[lovasoa]

I'm not sure what database connection tool you are using (what is it on your screenshots?), but it probably allows you to view graphically the type of columns.
Otherwise you can find out with

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'node';

The difference observed between your other database connection software and sqlpage is probably due to it setting the timezone to your local timezone on connection and sqlpage not doing that.

You should get the same result for SELECT NOW() in both systems if you set your timezone in sqlpage/on_connect.sql with

SET TIME ZONE 'Europe/Paris' -- again, I'm making the assumption that the system ingesting the naive timestamps is in paris. Change accordingly.

https://www.postgresql.org/docs/current/sql-set.html

[vladosam]

I use DBeaver as client. You are right, created_at is timestamp without time zone. And if I "SET TIME ZONE 'Europe/Sarajevo' it shows alert. Wooohooo. Now I need to change timestamp for created_at in db. Now is there option to show missing updates for all devices in one alert under description? Now if there are multiple devises they all get their own alert. Thanks for all your help. You rock.

[lovasoa]

Yes, you can make a query that will return a single line you can use string_agg to generate the description that will contain all the macs.

SELECT
    'alert' AS component,
    'Missing value update' AS title,
    string_agg(mac::text, ', ') AS description,
    'alert-circle' AS icon,
    'red' AS color
FROM (
  SELECT mac, MAX(created_at)
  FROM node
  GROUP BY mac
  HAVING MAX(created_at) < NOW() - INTERVAL '2 minutes';
)

[vladosam]

Thanks man. There was small syntax issue that I fix with gpt. ;)

SELECT
    'alert' AS component,
    'Missing value update' AS title,
    string_agg(mac::text, ', ') AS description,
    'alert-circle' AS icon,
    'red' AS color
FROM (
  SELECT mac, MAX(created_at) AS max_created_at
  FROM node
  GROUP BY mac
  HAVING MAX(created_at) < NOW() - INTERVAL '2 minutes'
) AS subquery;

Anyway thanks for all your help. Have a great weekend.

[vladosam]

Hi again. I'm having problem with this query. The problem is that 'Missing value update' is always shown on page even when no mac shown in description as they are not exceeded 2 minutes interval. I would like to hide alert when no value is beyond that interval. ChatGPT didn't help me with this problem :)

[lovasoa]

You can add a HAVING clause to the outer query to prevent it from returning a row if you don't have values:

SELECT
    'alert' AS component,
    'Missing value update' AS title,
    string_agg(mac::text, ', ') AS description,
    'alert-circle' AS icon,
    'red' AS color
FROM (
  SELECT mac, MAX(created_at) AS max_created_at
  FROM node
  GROUP BY mac
  HAVING MAX(created_at) < NOW() - INTERVAL '2 minutes'
) AS subquery
HAVING count(*) > 0;

I'm happy to answer your questions here, but when you have more general questions that are related to SQL in general and are not specific to SQLPage, you may receive better answers by asking on stackoverflow. When asking there, you can mention in the context of your question that you are using your query in a sqlpage website.

[vladosam]

It works beautifuly. In future I'll ask here only questions related to SQLPage. Thanks.