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.
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?
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.