topic Re: Regular expression and aggregate the result in Splunk Search

Regular expression and aggregate the result

mkrauss1 — Fri, 17 Nov 2017 19:04:07 GMT

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

Re: Regular expression and aggregate the result

niketn — Fri, 17 Nov 2017 19:14:44 GMT

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.

Re: Regular expression and aggregate the result

mkrauss1 — Fri, 17 Nov 2017 19:27:18 GMT

Interesting lesson, that works for me, THANKS!

Re: Regular expression and aggregate the result

niketn — Fri, 17 Nov 2017 19:47:44 GMT

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

Re: Regular expression and aggregate the result

mkrauss1 — Fri, 17 Nov 2017 19:52:19 GMT

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

Re: Regular expression and aggregate the result

niketn — Sat, 18 Nov 2017 05:09:22 GMT

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&