topic Count variables for which an event is missing in Splunk Search

Count variables for which an event is missing

GioCortez — Mon, 14 Dec 2020 17:57:19 GMT

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?

Re: Count variables for which an event is missing

scelikok — Mon, 14 Dec 2020 18:49:52 GMT

Hi @GioCortez , please try below query;

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

Re: Count variables for which an event is missing

GioCortez — Wed, 16 Dec 2020 09:05:29 GMT

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?

Re: Count variables for which an event is missing

scelikok — Wed, 16 Dec 2020 09:37:25 GMT

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

Re: Count variables for which an event is missing

GioCortez — Wed, 16 Dec 2020 14:11:44 GMT

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

Re: Count variables for which an event is missing

scelikok — Wed, 16 Dec 2020 18:47:22 GMT

Great!

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

Re: Count variables for which an event is missing

GioCortez — Wed, 23 Dec 2020 09:28:24 GMT

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