Alert query result i empty even though it shows results in the graph

[egromzin]

hi,
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.

query:
with hour_time_series as (
with time_series as (
select time_column
from generate_series(now() - interval ‘7 days’, now(), ‘1 minutes’) as time_column
)
select date_trunc(‘minute’, time_column) as time_column
from time_series
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

[egromzin]

here is the test result:

[egromzin]

tried also testing it with a simpler query, but still query result is empty:

	SELECT $__time(au.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%’)
	and $__timeFilter(au.created_at)
	group by au.created_at