Max/Min/Avg TPS

[WanLohnston]

Hi all, 

I have this query:

| timechart span=1s count AS TPS
| eventstats max(TPS) as MaxPeakTPS
| stats avg(TPS) as avgTPS first(peakTPS) as peakTPS first(peakTime) as peakTime
| fieldformat peakTime=strftime(peakTime,"%x %X")

This currently outputs Max TPS when Max TPS took place as well as the AVG TPS. I was wondering if it's possible to also display Min TPS and when that took place? 

TIA

[bowesmana · Accepted solution]

I assume that there is a typos in your MaxPeakTPS in the eventstats command and your use of peakTPS in the following stats and also the use of peakTime, which does not exist as a field.

You can do this

| timechart span=1s count AS TPS
``` Calculate min and max TPS ```
| eventstats max(TPS) as max_TPS min(TPS) as min_TPS
``` Now work out average TPS, actual min and max TPS and then the first 
    occurrence of the min/max TPS ```
| stats avg(TPS) as avgTPS values(*_TPS) as *_TPS 
        min(eval(if(TPS=max_TPS, _time, null()))) as maxTime
        min(eval(if(TPS=min_TPS, _time, null()))) as minTime
| fieldformat maxTime=strftime(maxTime,"%x %X")
| fieldformat minTime=strftime(minTime,"%x %X")

The min(eval... statements just look for the first _time when TPS is either min or max to get the earliest time when these occurred.

Note the use of field naming conventions min_TPS/max_TPS that allows the use of wildcards in the stats.

[dtburrows3]

Tried this out and came back with this. Format might be a little different than what you asked for but I think tells the same story.

| bucket span=1m _time
    | stats
        count as TPS
            by _time
    | eventstats
        min(TPS) as min_TPS,
        max(TPS) as max_TPS
    | foreach *_TPS
        [
            | eval
                <<MATCHSTR>>_TPS_epoch=if(
                    'TPS'=='<<MATCHSTR>>_TPS',
                        mvappend(
                            '<<MATCHSTR>>_TPS_epoch',
                            '_time'
                            ),
                        '<<MATCHSTR>>_TPS_epoch'
                    )
            ]
    | stats
        avg(TPS) as avg_TPS,
        first(*_TPS) as *_TPS,
        first(*_TPS_epoch) as *_TPS_epoch
    | eval
        avg_TPS=round('avg_TPS', 2)
    | foreach *_TPS_epoch
        [    
            | eval
                <<MATCHSTR>>_TPS_timestamps=case(
                    mvcount('<<FIELD>>')==1, strftime('<<FIELD>>', "%x %X"),
                    mvcount('<<FIELD>>')>1, mvmap('<<FIELD>>', strftime('<<FIELD>>', "%x %X"))
                    ),
                <<MATCHSTR>>_TPS_json=json_object(
                    "type", "<<MATCHSTR>>",
                    "TPS", '<<MATCHSTR>>_TPS',
                    "Timestamps", '<<MATCHSTR>>_TPS_timestamps'
                    ),
                combined_TPS_json=mvappend(
                    'combined_TPS_json',
                    '<<MATCHSTR>>_TPS_json'
                    )
            ]
    | fields + combined_TPS_json, avg_TPS
    | addinfo
    | eval
        search_time_window_end=strftime(info_max_time, "%x %X"),
        search_time_window_start=strftime(info_min_time, "%x %X"),
        avg_TPS_time_window='search_time_window_start'." --> ".'search_time_window_end'
    | eval
        combined_TPS_json=mvappend(
            'combined_TPS_json',
            json_object(
                "type", "avg",
                "TPS", 'avg_TPS',
                "Timestamps", 'avg_TPS_time_window'
                )
            )
    | mvexpand combined_TPS_json
    | fromjson combined_TPS_json
    | fields - combined_TPS_json
    | fields + type, TPS, Timestamps

 
Output should look something like this. 

dtburrows3_1-1702917740960.png

You should also be able to change the time bucket span form 1m back to 1s since that is how it was setup in your initial query.

[splunkreal]

Hello @WanLohnston you can try something like this :

|  timechart span=1d count(myfield) as nb_myfield |  eventstats min(myfield) as min_fields max(myfield) as max_fields avg(myfield) as moy_fields

[WanLohnston]

Hi! 

Thanks for taking the time, sadly this didn't work out for me. 

Ideally if I can keep the same format of: 

| timechart span=1s count AS TPS
| eventstats max(TPS) as peakTPS
| eval peakTime=if(peakTPS==TPS,_time,null())
| stats avg(TPS) as avgTPS first(peakTPS) as peakTPS first(peakTime) as peakTime
| fieldformat peakTime=strftime(peakTime,"%x %X")

With the addition of a couple lines for Min TPS and when it took place that would be ideal.