im trying to fire an alert, but the state is always ok.
in the graph it looks good. and in table view it also has data .
tried everything. now im stuck.
with hour_time_series as (
with time_series as (
from generate_series(now() - interval ‘7 days’, now(), ‘1 minutes’) as time_column
select date_trunc(‘minute’, time_column) as time_column
order by time_column desc
missing_images as ( --the original query from 1.1.0
SELECT date_trunc(‘minute’, au.created_at) as created_at, count(*) as missing_images_count
FROM service.active_urls au, service.active_domains ad
WHERE ad.id = au.domain_id
AND au.created_at > now() - INTERVAL ‘7 days’
AND au.extracted_at > ‘-infinity’
AND au.title IS NOT null
and (au.image_url is null or au.image_url not like ‘https%’)
group by date_trunc(‘minute’, au.created_at)
order by date_trunc(‘minute’, au.created_at)
select COALESCE(mi.missing_images_count, 0), __time(hts.time_column) from hour_time_series hts left join missing_images mi on date_trunc('minute', mi.created_at) = hts.time_column where __timeFilter(hts.time_column)
order by hts.time_column desc