Splunk Search

count something with a specifc rule and time

splunkinsfs
Explorer

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.

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

adonio
Ultra Champion

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@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.

0 Karma

adonio
Ultra Champion

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

splunkinsfs
Explorer

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
Explorer

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

Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...