Splunk Search

Variance or Average Delta Between MV Field of timestamps

mhorch
New Member

I'm trying to calculate the variance and delta between a multivalue field that contains epoch timestamps. The purpose is to determine the interval between web requests for a system to a specific domain/url.

The mvfield (event_time) will contain at most 100 values. 

Labels (2)
0 Karma

dtburrows3
Builder

You can try utilizing a foreach mode=multivalue loop to gather deltas between the timestamps and then do descriptive statistics around the new delta MV field.

Something like this:

<base_search>
    ``` sorting event_time mvfield values ```
    | eval
        event_time=mvsort(event_time)
    ``` initializing field current for the nex foreach loop ```
    | eval
        current=mvindex(event_time, 0)
    ``` loop through each value in event_time and subtract the preceding value to get a delta ```
    | foreach mode=multivalue event_time
        [
            | eval
                tmp_delta='<<ITEM>>'-'current',
                delta=mvappend(delta, tmp_delta),
                current='<<ITEM>>'
            ]
    ``` removing these fields as they are no longer needed ```
    | fields - current, tmp_delta
    | eval
        ``` stripping off first entry from delta mvfield since it will always be zero and skew stats ```
        delta=mvindex(delta, 1, -1),
        ``` calculate avergae delta betwwen timestamps ```
        avg_delta=avg(delta),
        ``` diff is a temp field to assist with evaluating the standard deviation s=√(Σ((delta-avg_delta)^2/(n-1))) ```
        diff=mvmap(delta, 'delta'-'avg_delta'),
        diff_2=mvmap(diff, pow(diff, 2)),
        stdev_delta=sqrt(sum(diff_2)/(mvcount(diff_2)-1)),
        ``` evaluate variance ```
        variance_delta=pow('stdev_delta', 2)
    ``` remove diff fields as they were temporary to calculate standard deviation ```
    | fields - diff, diff_2
    ``` zscore for more detail ```
    | eval
        zscore_delta=mvmap(delta, (('delta'-'avg_delta')/'stdev_delta'))
    ``` human readable format (duration) of deltas for context ```
    | eval
        stdev_duration=tostring(stdev_delta, "duration"),
        range_delta=max(delta)-min(delta),
        range_duration=tostring(range_delta, "duration")
    ``` just sorting fields list for final display ```
    | fields + event_time, delta, zscore_delta, avg_delta, stdev_delta, variance_delta, range_delta, stdev_duration, range_duration

 It should return you a table that looks like this.

dtburrows3_0-1704756344701.png

 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...