topic Re: How to do a group by on regex in Splunk Search

How to do a group by on regex

utkarshpujari — Tue, 13 Mar 2018 11:22:34 GMT

I have a certain field which contains the location of a file. The filepath looks like this /some/path//some.csv. I want to group my results based on the file paths that match except the date condition. For example

Field1
/a/b/c/2016-01-01/abc.csv
/x/y/z/2016-01-01/xyz.csv
/a/b/c/2016-01-02/abc.csv
/x/y/z/2016-01-02/xyz.csv
/a/b/c/2016-01-03/abc.csv
/x/y/z/2016-01-03/xyz.csv

I want something like this if I were to do a count
/a/b/c/*/abc.csv 3
/x/y/z/*/xyz.csv 3

Re: How to do a group by on regex

Richfez — Tue, 13 Mar 2018 11:42:53 GMT

First, create the regex - IMO sedmode - to remove the date piece.

... | rex field=Field1 mode=sed "/\d{4}-\d{2}-\/d{2}//"

Now, that shoudl remove the first piece that looks like a date from Field1. NOTE if you need to use this full date field later in this search, you won't be able to do it this way. But don't worry, this doesn't actually change the field in the data itself, so your next searches will be fine, it's just changing it for the remainder of this search.

Then let's just stats count them...

... | stats count by Field1

That should do it - give it a try and let us know if it works or what it does wrong if it's not right!

for a single-pasting -

Your base search...
| rex field=Field1 mode=sed "/\d{4}-\d{2}-\/d{2}//"
| stats count by Field1

Happy Splunking,
Rich

Re: How to do a group by on regex

utkarshpujari — Tue, 13 Mar 2018 12:53:58 GMT

I modified it a bit to
mode=sed "s/\d{4}-\d{2}-\d{2}//"
And it started working.
Cool thanks

Re: How to do a group by on regex

ryhluc01 — Tue, 05 Feb 2019 03:13:44 GMT

Accept an answer : )