Regular expression and aggregate the result

[mkrauss1]

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..

[niketn · Accepted solution]

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.

[mkrauss1]

Interesting lesson, that works for me, THANKS!

[niketn]

@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.

[mkrauss1]

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

[niketn]

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&