Hi All, I have created a dashboard with four panels and used a base query and sub query. But in the base query i have used the non-transforming command.
Base query details
index=test sourcetype=x_service component=x_component | eval result=case(result="Passed","CompletePassed",result="Failed","CompletedFailed",isnotnull(result),result,isnull(result),null) | eventstats lastest(result) as result latest(status) as finalStatus earliest(_time) as Earliest latest(_time) as Latest by transId,component | eval Final_result=case(isnull(result) AND isnotnull(finalStatus),finalStatus,isnotnull(result),result,isnull(result) AND isnull(finalStatus),"MissingStatus") | fields _time, transId,component,result,Final_result,status,finalStatus,message,duration
In one of the panels I have used sub query to find the average duration to complete the transaction.
Average duration to complete the transaction
| timechart span=30m avg(duration)
Using the above base query and sub query, I am able to get the output but it seems it is not best practice to use the non-transforming commands in dashboards, so used stats command instead of event stats command in base search and got the output.
Similarly to calculate the average duration to complete the transaction created another base query for that panel and used the stats/timechart unable to get the output.
index=test sourcetype=x_service component=x_component | stats earliest (_time) as Earliest latest(_time) as Latest by correlationId | eval duration= Earliest-Latest | timechart span=30m avg(duration)
unable to get the output.
But when we use the eventstats commands I am getting the output.
index=test sourcetype=x_service component=x_component | eventstats earliest (_time) as Earliest latest(_time) as Latest by correlationId | eval duration= Earliest-Latest | timechart span=30m avg(duration)
Question is how to write the sub query using the base query.
Thanks in advance.