count something with a specifc rule and time

[splunkinsfs]

Hi,

I have many lines of event like these two:
2017 12 07 21:32:23.669 | 20,3329788638103|CT02053,15.96x11.81x6.15,211 lbs
2017 12 07 21:29:26.648 | 19,42233978863257|CT02010,16.26x15.43x16.75,4441 lbs

Each event starts with a time, then a bunch of random number and code, and ends with the weight in "lbs"(the weight is the number before the "lbs"). I need to accomplish two goals:
1. Count if same weight happens over in 10 events
2. Count those events found from #1 if they are happened within 10 minutes based on their time stamps.

I use this query to achieve goal #1.
Base search..........| use rex command to create the field for the weight | stats count by weight | where count>10

But I don't know how I can confirm the time of those events are happens within 10 minutes?
I tried to add "transaction weight maxspan=10m" at the end of the query but it didn't work. Any idea?

Thanks.

[adonio]

hello there,

try this:

  <your_search_here> 
    | rex <your_rex_here>
    | stats count as weight_count latest(_time) as last_one earliest(_time) as first_one by weight
    | eval duration=last_one-first_one
    | where weight_count>10 AND duration<600

hope i understood your question

[DalJeanis]

@adonio - Slight issue with this one... if there are ten within an hour, but an eleventh outside of that hour, this one will give a false negative.

[adonio]

yup...
thanks for pointing out, forgot about the time_window in streamstats.
appreciate your feedback and answers!

[splunkinsfs]

Thanks for the quick suggestion. The result of your query did bring up the result that I need.

Again, I was looking for
1. Count if same weight happens over 10 times
2. Count among from those events found from #1 if they are happened within 10 minutes based on the event time.

[DalJeanis · Accepted solution]

Here's one approach... use streamstats to count how many are in a 10m time_window for each weight. Once that pass is complete, use eventstats to copy the highest count found in each weight onto each record that has that weight, and use that to eliminate events that did not reach the threshold.

base search..........
| use rex command to create the field for the weight 
| fields weight
| sort 0 _time 
| streamstats time_window=10m count as mycount by weight
| eventstats max(mycount) as maxcount by weight
| where maxcount>10

After the above, you have only weights that occurred at least 10 times within a 10m period, but you have all examples of that weight, whether they were in such a period or not. If you then want to stats them and have the earliest _time, latest _time, and the total number of occurrences, you can do that. If you want to do other analysis, you can do that.

If you want to get rid of all records that are NOT members of a set of at least ten within ten minutes, then you are going to have to reverse the list and process them backwards. That would look something like this...

| reverse
| eval tenflag=case(mycount>=10,"Y")
| streamstats time_window=10m max(tenflag) as tenflag by weight
| where tenflag="Y"
| reverse

[splunkinsfs]

Thanks for the suggestion. Your query seems to be working . I'll do additional stats count to make the final result looks good.