How to return the latest events where one field is equal to a certain value without using subsearches?

[DEngineer1]

Hi Guys,

I have got a problem which I need to return results when 1 field is of a certain value BUT only after a certain event.

Serial_No   Complete
     7         0
     5         0
     4         0
     3         1
     2         1
     1         0

In the case above, I only need to return rows where complete==0 BUT only considering rows that are after complete==1 (so the row where Serial_No==1 is not considered)

In the example above the results will be:

Serial_No   Complete
     7         0
     5         0
     4         0

Any possibility to get the result without any subsearches?
Thanks!

[somesoni2 · Accepted solution]

Try like this

your current search giving Serial_No Complete | sort 0 Serial_No | streamstats sum(Complete) as temp | where temp>0

[DEngineer1]

Hey thanks for the recommendation, streamstats really give me the "light bulb"

I was thinking this might work:

"search"| sort + Serial | streamstats window=2 global=f current=t first(complete) as next_com, first(serial) as next_serial | fields serial, complete, next_serial, next_com | eval test=if(complete > next_com, serial, 0) | fields serial, complete, test | eventstats max(test) as m_test | where complete == 0 | where (serial > m_test)

However I do not know is this an efficient search...

Comparing to using sub-search, will this be a faster search?

[somesoni2]

The efficiency will depend upon the amount of processing to be done. Your search is doing extra stuff so it might be slower than my query. If your query is working for you, just try like this

 "search"| sort + Serial | streamstats window=2 global=f current=t first(complete) as next_com, first(serial) as next_serial | fields serial, complete, next_serial, next_com | eval test=if(complete > next_com, serial, 0) | eventstats max(test) as m_test | where complete == 0 AND (serial > m_test)