Canvas switch images with two SQL select queries


I'm trying to switch the image from green to amber when there are no records in the last hour and change to red when no records in the last 2hrs. All within one indicator.

I've tried various ways to-do this but all have failed. I know the below isn't correct but I was simply trying to see if I could get two queries to work within one element, it does add the two values together but this isn't what I need.
Is there some way to read the nCount1h and nCount2h within the case statement or is this completely the wrong approach?

Any help would be greatly appreciated

| essql query="SELECT COUNT(*) AS nCount1h FROM \"alm-alerts-*\" WHERE TIMESTAMPDIFF('hh', \"@timestamp\"::datetime, now()::datetime) < 1"
| math
{string "nCount1h+" {filters | essql query="SELECT COUNT(*) AS nCount2h FROM \"alm-alerts-*\" WHERE TIMESTAMPDIFF('hh', \"@timestamp\"::datetime, now()::datetime) < 2" | math "nCount2h"}}
| image dataurl={switch
case={case if={gt x} then={asset asset-green}}
case={case if={gt x} then={asset asset-amber}}
case={case if={eq 0} then={asset asset-red}}
| render

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.