Splunk Search

Regular expression and aggregate the result

mkrauss1
Explorer

Assume the following records:

Nov 17 19:24:51 x.x.x.x Nov 17 19:24:51 myserver (appx): 1510943091.801 520 192.168.0.5 CONNECT something else
Nov 17 19:24:51 x.x.x.x Nov 17 19:24:51 myserver (appx): 1510943091.801 1040 192.168.0.5 CONNECT something else

The above record is a modied squid log and i'd like to get the average response time, in this case it's the value of 520 and 1040

My query:

 myserver |  rex field=_raw "appx\):\s+\d+\.\d+\s+(?<TIME>\s+\d+)"  |  convert num(TIME)   | stats avg(TIME) 

Somehow the avg aggregate returns nothing but using the min/max aggregate returns a value.
Whats wrong with the query?

Thanks..

Tags (1)
0 Karma
1 Solution

niketn
Legend

Removed \s from (?\s+\d+) in the rex command. You need not use | convert num(TIME)

<YourBaseSearch>
|  rex field=_raw "appx\):\s+\d+\.\d+\s+(?<TIME>\d+)"
|  stats avg(TIME)

Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

Removed \s from (?\s+\d+) in the rex command. You need not use | convert num(TIME)

<YourBaseSearch>
|  rex field=_raw "appx\):\s+\d+\.\d+\s+(?<TIME>\d+)"
|  stats avg(TIME)

Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mkrauss1
Explorer

Interesting lesson, that works for me, THANKS!

0 Karma

niketn
Legend

@mkrauss1, you can use regex101.com for testing and creating regular expression for your sample events. It also has a quick reference of regular expression and gives you step by step information of each regular expression pattern match.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mkrauss1
Explorer

I never became a friend of regexbody but regex101.com looks very interesting. Thanks again!

0 Karma

niketn
Legend

If you want to pick up on Regular Expressions you should see .conf 2017 presentation on "Beyond Regular Regular Expressions" by Cary Petterborg, our regex "guru" and Splunk Trust member.

http://conf.splunk.com/sessions/2017-sessions.html#search=Beyond%20REGULAR%20Regular%20Expressions&

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...