Subsearch and real-time: How to write a search to get all-time values and display them with real-time values?

[canuzun]

Below is my simple search.

index="ix-lp-tps"
| stats count as CurrentCount
| appendcols [search earliest=-100y index="ix-lp-tps" | stats count as AllTimeCount]
| table CurrentCount, AllTimeCount

If I choose last 15 mins from date picker, this search works fine. I can see count of events in the last 15 mins vs count of events from all time.

My problem occurs when I try to switch from last 15 mins to 5 min window. Historic to real-time. Why do I need to do this? There is a dashboard I need to develop and I need to use a real-time gauge, min & max of gauge is required to be dynamic. So I figured I could calculate min and max of count (for all time) and display the current count.

I understand that subsearches finish first and and main search finishes later. It is also said that real-time searches never finish, so subsearches can't be real-time which will cause the main search to never finish. It makes sense, but here I am trying to get all time values and display them with real-time values, so I don't understand what I am doing wrong.

I am also open to any other suggestions.

Thanks.

[sundareshr · Accepted solution]

You could try this approach. Avoids the use of subsearch. This will compare alltime vs 5min window.

index="ix-lp-tps" earliest=0
| eval Current=if(_time>relative_time(now(), "-5m@m"), "Yes", "No")
| eval x=" " 
| chart count as AllTimeCount count(eval(Current="Yes") as CurrentCount
| table CurrentCount, AllTimeCount

[canuzun]

I think this is like using CASE keyword and get the SUM in the end with SQL.
I see your point here. I will try this approach.

In case anyone wants to use your sample; There was a ) missing and I have removed eval x=" " line.

index="ix-lp-tps" earliest=0
| eval Current=if(_time>relative_time(now(), "-5m@m"), "Yes", "No")
| chart count as AllTimeCount count(eval(Current="Yes")) as CurrentCount
| table CurrentCount, AllTimeCount

Thanks