Dashboards & Visualizations

Delta time between each row - calculate frequency of values in buckets

maayan
Path Finder

Hi,

I have a table of time, machine, and total errors. I need to count for each machine how many times 3 errors (or more) happened in 5 min. if in one bucket more than 3 error happened I  sign this row as True. 
finally i will return the frequency of 3 errors in 5 min (Summarize all rows==True)
i succeeded in doing that in Python, but not in Splunk.

i wrote the following code :

| table TimeStamp,machine,totalErrors
| eval time = strptime(TimeStamp, "%Y-%m-%d %H:%M:%S.%3N")
| eval threshold=3
| eval time_window="5m"
| bucket span=5m time
| sort 0 machine,time
| streamstats sum(totalErrors) as cumulative_errors by machine,time
| eval Occurrence = if(cumulative_errors >= 3, "True", "False")

| table machine,TimeStamp,Occurrence



It almost correct. row 5 supposed to be True. If we calculate the delta time between row 1 to 5
more than 5 min passed, but if we calculate the delta time between row 2 to 5 less than 5 min passed 
and number of errors >=3 errors.
How to change it so it will find the delta time between each row (2 to 5 , 3 to 5,.. ) for each machine ?

hope you understand. i need short and simple code because i will need to do that also for 1m,2m,.. 3,5,..errors

row Machine TimeStamp Occurrence
1 machine1 12/14/2023 10:12:32
 

 

FALSE
2 machine1 12/14/2023 10:12:50 FALSE
3 machine1 12/14/2023 10:13:06 TRUE
4 machine1 12/14/2023 10:13:24 TRUE
5 machine1 12/14/2023 10:17:34 FALSE
6 machine1 12/16/2023 21:01:45 FALSE

7 machine2 12/18/2023 7:53:54 False

maayan_1-1702991168920.png

thanks,

Maayan

Labels (2)
0 Karma

dtburrows3
Builder

Not sure if this is exactly what you are looking for but I think it is pretty close.

dtburrows3_0-1703000010773.png


I got this output by stringing together a couple of streamstats with window=<int> and reset_before=<criteria> parameters

| sort 0 +Machine, +time    
    | streamstats
        count as row
    | eval
        TimeStamp=strftime(time, "%m/%d/%Y %H:%M:%S")
    | fields - _time
    | fields + row, Machine, TimeStamp, time
    | streamstats window=3
        count as running_count,
        min(time) as min_time,
        max(time) as max_time
            by Machine
    | eval
        seconds_diff='time'-'min_time',
        duration_diff=tostring(seconds_diff, "duration")
    | streamstats window=3 reset_before="("seconds_diff>300")"
        count as running_count
            by Machine
    | eval
        Occurrence=if(
            'seconds_diff'<=300 AND 'running_count'==3,
                "TRUE",
                "FALSE"
            )
    | fields + row, Machine, TimeStamp, Occurrence

 
Here is the full SPL I used to generate the screenshot (results may vary because of the use of relative_time())

| makeresults
    | eval
        Machine="machine 1",
        time=relative_time(now(), "-2h@s")
    | append
        [
            | makeresults
                | eval
                    Machine="machine 1",
                    time=relative_time(now(), "-2h+18s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 1",
                    time=relative_time(now(), "-2h+34s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d@d+20h@h+31m@m+48s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 1",
                    time=relative_time(now(), "-2h+52s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d+5h+5m+2s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 1",
                    time=relative_time(now(), "-2h+302s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 1",
                    time=relative_time(now(), "+2d-5h+18s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d+5h+18s@s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d+5h+2m+1s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d+5h+2m+34s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d+5h+4m-12s")
            ]
    | append
        [
            | makeresults
                | eval
                    Machine="machine 2",
                    time=relative_time(now(), "+4d@d+20h@h+43m@m+5s@s")
            ]
    | sort 0 +Machine, +time    
    | streamstats
        count as row
    | eval
        TimeStamp=strftime(time, "%m/%d/%Y %H:%M:%S")
    | fields - _time
    | fields + row, Machine, TimeStamp, time
    | streamstats window=3
        count as running_count,
        min(time) as min_time,
        max(time) as max_time
            by Machine
    | eval
        seconds_diff='time'-'min_time',
        duration_diff=tostring(seconds_diff, "duration")
    | streamstats window=3 reset_before="("seconds_diff>300")"
        count as running_count
            by Machine
    | eval
        Occurrence=if(
            'seconds_diff'<=300 AND 'running_count'==3,
                "TRUE",
                "FALSE"
            )
    | fields + row, Machine, TimeStamp, Occurrence
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It is not clear why row 5 should be true since you haven't shared the data (number of errors in each event).

Having said that, are you trying to implement a sliding 5 minute window, or are you using time bins?

If you are using time bins, the row 5 is in a different bin to rows 1-4.

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...