topic Re: Question regarding grouping of results into a table in Splunk Search

Question regarding grouping of results into a table

aputz — Wed, 25 May 2011 19:19:06 GMT

So I have events which have the following fields that I would like to sort by:

app, dst_ip, bytes

Preferably I would want to display them in a table such as:

web-browsing   I'M.A.DST.IP          1571453
               I'M.A.DST.IP            71453
               I'M.A.DST.IP             1453
skype          I'M.A.DST.IP          1412345
gmail-base     I'M.A.DST.IP          1012345
               I'M.A.DST.IP           212345
ssl            I'M.A.DST.IP           612345
               I'M.A.DST.IP           123456
               I'M.A.DST.IP            12345
               I'M.A.DST.IP             1234

The sort function would be preferably be having these apps sorted via "-bytes" but also showing a breakout of associated dst_ips and their respective bytes count under each app (again sorted via -bytes). I am hoping this is possible via the search language whether it is with table or any other data formatting command. I recall stumbling across a grouping command before and I can't seem to find it again.
Thank you for any help/assistance on this.

Sincerely,

Alex

Re: Question regarding grouping of results into a table

sideview — Wed, 25 May 2011 19:41:40 GMT

It's the stats command you're looking for. stats can perform any number of statistical operations, and it can run them for each value of a single field, or for each unique combination of multiple fields. In your case I think you want:

<your search> | stats sum(bytes) as bytes by app, dst_ip | sort - app bytes

Note that I'm doing a double sort with the sort command, to give you the primary and secondary sorting that you're looking for.

http://www.splunk.com/base/Documentation/latest/SearchReference/Stats

http://www.splunk.com/base/Documentation/latest/SearchReference/Sort

Re: Question regarding grouping of results into a table

aputz — Thu, 26 May 2011 12:57:20 GMT

Thanks, this is working wonderfully.
I was wondering though, is there a way to only show the app name for the first entry of each section of IPs/bytes? This would be preferred for presentation.

Re: Question regarding grouping of results into a table

sideview — Thu, 26 May 2011 16:15:48 GMT

Sure. You can tack this onto the end:

" | streamstats current=f last(app) as previousApp | eval app=if(match(app,previousApp),"",app)| fields - previousApp"

However note that if your table is split across multiple pages this could get quite confusing for your users.

Re: Question regarding grouping of results into a table

aputz — Thu, 26 May 2011 16:49:57 GMT

That is just what I was looking for. I agree it will potentially make it difficult if it is presented across multiple pages, I appreciate having the options for layout though.

Thank you very much for your help!

Re: Question regarding grouping of results into a table

koprai — Fri, 19 Dec 2014 23:34:26 GMT

Nice hack.. But I wish Splunk brings a simple XML "mergecell" table option