Splunk Search

Getting averages from 2 columns

dbcase
Motivator

Hi,

I have a query that looks like this

index=wholesale_app counter buildTarget=* product=* Properties.index=0 buildTarget=* product=*|convert num(Properties.args{}) as loginms|eval dura=loginms/1000|eval duration=ceil(dura)|where duration<=60|stats count as statscount by duration|sort duration|rename duration as "Login Duration in Seconds" statscount as "Number of times the Login Duration Occurred"| appendpipe [stats count | where count=0]

It makes a table that shows this

alt text

What I'd like to to is get an average number of seconds for login duration but using both columns. Example

1 second occurred 16 times so 1 second would have to be counted 16 times in the average.

Thoughts?

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

 index=wholesale_app counter buildTarget=* product=* Properties.index=0 buildTarget=* product=*|convert num(Properties.args{}) as loginms|eval dura=loginms/1000|eval duration=ceil(dura)|where duration<=60|stats count as statscount by duration|sort duration| appendpipe [stats count | where count=0]
| eval noofsecs=statscount*duration | stats sum(noofsecs) as noofsecs sum(statscount) as totaloccurances | eval "Average number of secs per login"=round(noofsecs/totaloccurances,2)

View solution in original post

0 Karma

somesoni2
Revered Legend

Give this a try

 index=wholesale_app counter buildTarget=* product=* Properties.index=0 buildTarget=* product=*|convert num(Properties.args{}) as loginms|eval dura=loginms/1000|eval duration=ceil(dura)|where duration<=60|stats count as statscount by duration|sort duration| appendpipe [stats count | where count=0]
| eval noofsecs=statscount*duration | stats sum(noofsecs) as noofsecs sum(statscount) as totaloccurances | eval "Average number of secs per login"=round(noofsecs/totaloccurances,2)
0 Karma

dbcase
Motivator

Hey Somesoni2!

Very nice solution! Thank you! (Again)

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...