louislam / uptime-kuma

A fancy self-hosted monitoring tool
https://uptime.kuma.pet
MIT License
59.46k stars 5.32k forks source link

Monitor SQL query result #2342

Open thedatabaseme opened 1 year ago

thedatabaseme commented 1 year ago

⚠️ Please verify that this feature request has NOT been suggested before.

🏷️ Feature Request Type

New Monitor

🔖 Feature description

Hello,

I would love to see the possibility within Uptime-Kuma, to specify an expected result within a SQL query monitor type (e.g. Postgres). So a SQL monitor would not only check if it can connect to a database and can execute a query. Another wish from my side would be, to store the query result in the heartbeat data. You could cut the result to save space, but at least one would have some idea on how the result looked like.

Kind regards Philip

✔️ Solution

I want to check for the replication status of a Postgres cluster and if a specific cluster member is in recovery or not. So a query like select * from pg_is_in_recovery(); would return either t (true) or f (false). I would like to have the possibility to monitor for the expected result.

❓ Alternatives

If you don't consider to implement this request, at least the result should be exported as a metric so that I can filter on it later. Implementing both options, would be the best solution.

📝 Additional Context

No response

blackandred commented 1 year ago

Alternatively you can try to use Infracheck with a custom script, expose the endpoint and just ping it using a regular HTTP check in Uptime Kuma.

https://infracheck.docs.riotkit.org/en/latest/

cfoellmann commented 1 year ago

I am with @thedatabaseme I would like to set my SQL statement and define a return value for up (and down). Or just "0 = down" and "1 = up". Forming the return in your query is easy enough, right?

In my case I would count the rows of a table and return "DOWN" if the count is 0.

evolucja commented 1 year ago

Totally for it. Doesn't seem to be very complicated to implement.

rogerioadris commented 1 year ago

Is there any condition of the query to return down? or is the query just executed without monitoring the return?

thedatabaseme commented 1 year ago

Is there any condition of the query to return down? or is the query just executed without monitoring the return?

My understanding of the current implementation is, that there is no actual check for the result of the SQL query. When the query finishes without error, the monitor is up. Else it's down.

rogerioadris commented 1 year ago

Thanks for the answer. I solved my problem as follows

IF NOT EXISTS(.........) BEGIN
    THROW 51000, 'ERROR XXXXX.', 1;  
END
wvolkov commented 7 months ago

Thx to @rogerioadris idea, I could use it at PostgresSQL as well:

do
$$
declare
    total_errors int2;
    err_text text;
begin

select
        count(*) as total_errors,
        string_agg(p.query || ':\n' || p.context , '\n\n') as x 
into
        total_errors,
        err_text
from
        pglog p
where
        error_severity != 'LOG'
        and application_name = 'pg_cron'
        and log_time > now() - interval '1 day';

IF total_errors > 0 THEN
    raise 'Failed Queries\n ```%s```', err_text;  
end if;

end
$$
YuriyGavrilov commented 1 month ago

+1

What about Trino sql support? can I configure it already or need to support it?

YuriyGavrilov commented 1 month ago

Maybe add support sqlalchemy instead of adding db one by one? Trino does not supported yet (((

CommanderStorm commented 1 month ago

Maybe add support sqlalchemy instead of adding db one by one?

sqlalchemy is a python tool, while uptime kuma is written in js

Adding and maintaining monitors for every db out there is indeed unmanagable. I'd prefer to limit ourselves to the popular ones. Since I have not heard of Trino, I don't think this is a good fit.

You can always use the push monitor to push results to UK. This way, arbitrary checks can be performed, including databases without the large support of postgres/..