Using Canvas to conditionally format an element powered by a query based on a previous timeframe of the same query

[dgbarrow (Daniel Barrow)]

Hi all,

I am currently working on a dashboard to enable me to display a traffic light system showing errors by my column "callsite". I have worked out how to conditionally format, and I have worked out how to create a second query duplicating my result set but offsetting it by 7 days historically.

What I am stuck with is conditionally formatting element A based upon query B (A - 7 Days).

my current code looks like this:

Query A

    filters

    | essql

      query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program'"

    | math "size(message)"

    | metric "Errors triggered by AgilityToEmmaUserSync.Program"

      metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {gt 0} then="red" else="black"} align="center" lHeight=48}

      labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {gt 0} then="red" else="black"} align="center"}

    | render

Query B

    filters

    | essql

      query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS"

    | math "size(message)"

    | metric "Errors triggered by Agility ToEmmaUserSync.Program"

      metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}

      labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}

    | render

Can I somehow reference or insert Query B into the conditional formatting element of Query A? I have tried color={if{gt (QUERYB)}} by typing the query out again, but this is not providing the desired results.

Thank you for your time

Daniel

[Catherine_Liu]

@dgbarrow The only way to achieve this is to insert query B as part of the sub-expression for conditionally setting the color.

It would look something like this:

filters
| essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program'"
| math "size(message)"
| metric "Errors triggered by AgilityToEmmaUserSync.Program"
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={
    if {
      gt {
        essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS" 
        | math "size(message)"
      }
    } then="red" else="black"} align="center" lHeight=48}
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={
    if {
      gt {
        essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS" 
        | math "size(message)"
      }
    } then="red" else="black"} align="center"}
| render

[dgbarrow (Daniel Barrow)]

Hi Catherine,

Thank you so much for your response.

This is throwing an error -

[metric] > [font] > [if] > [gt] > [essql] > Can not cast 'number' to any of 'filter'.

Is this telling me that the gt function expects a type 'filter', and the query is returning a number?

Thanks

Daniel

[dgbarrow (Daniel Barrow)]

Hi @Catherine_Liu, sorry to pester but could you explain this problem to me? I understand that effectively you cannot parse a query result into a mathematical equation in ESSQL the same way you can in SQL, but is there an alternative way of doing this which would enable me to achieve the desired result?

Thanks

Daniel