Count variables for which an event is missing

[GioCortez]

Hi all. A silly question. I have the below searchresult (in my application i'm printing logs for different processing status of a specific order: I would like to know if (and how) would it be possible to extract the number of orders for which i do have a "processStarted" log, and not an "orderSaved" one. And another query to extract the orderNumbers for these case.

orderNumber	action
123	processStarted
123	orderSaved
125	processStarted
125	orderSaved
301	processStarted

As per the above example, i would like
1) a query to extract the count (1 in this case, since only order 301 don't have an "orderSaved" entry)

2) a query to extract the orderNumbers for which i do have "processStarted", but not "orderSaved"). Only 301 in this case

Which operation you would suggest me to investigate? Can you point me to some examples?

[GioCortez]

Thanks @scelikok for the quick response! That did the trick. Now i would like to complex things a little bit...basically i have orders flowing from one system to another. I have logs for which an order is sent ("processStarted") and for which an order is saved in the target system ("orderSaved"). But the problem with this, is that, basing on the search time restriction the user is setting, i may loose "orderSaved" events which happened after the timeframe. Is there any feasible (and acceptable, in terms of performances) solution for this?

[scelikok]

Hi @GioCortez,

Maybe you can give some time for "orderSaved" event before creating an alert. I think there will be acceptable timeframe after "processStarted" event. Let's say orders should be saved in hour, you can use below query as 5m scheduled. It will wait for on hour to show unprocessed order.

| stats count max(_time) as _time by orderNumber
| eval wait_time=now()-_time
| search count=1 wait_time>3600 
| addcoltotals  

[GioCortez]

Thanks @scelikok, that made my day! One more thing: what if instead of using now() i want to use the "latest" date selected in the range picker? Is there a variable where starttime and endtime is stored? So that i could do something like the below?

| stats count max(_time) as _time by orderNumber
| eval wait_time=rangepickerenddate()-_time
| search count=1 wait_time>3600 
| addcoltotals  

[scelikok · Accepted solution]

Great! 

"addinfo" command will help you. It will add these "info_min_time", "info_max_time" fields.

| stats count max(_time) as _time by orderNumber
| addinfo
| eval wait_time=info_max_time-_time
| search count=1 wait_time>3600 
| addcoltotals 

[GioCortez]

Thanks @scelikok  for helping out! That made the trick!

[scelikok]

Hi @GioCortez , please try below query;

| stats count by orderNumber
| where count=1
| addcoltotals