topic Re: Looking for a way to display sub items, kind of like pivot table in Excel. in Splunk Search

Looking for a way to display sub items, kind of like pivot table in Excel.

oleg106 — Fri, 31 May 2013 19:55:59 GMT

Hi, I am looking to display results a certain way and want to know if this is possible in Splunk. We have proxy logs that contain URLs and client IPs. I want to group by URLs (top 20) and display all IPs that connected to them. I want the output to look something like this,,

Url CountOfConnections

..Client IP list

google.com 3

...192.168.1.2

...192.168.1.7

...192.168.1.3

yahoo.com 2

...192.168.1.2

...192.168.1.3

Basically a visually simple way to distinguish urls and clients.

Doing something like "top 20 url ip" doesn't look good for this type of report. Thanks!

Re: Looking for a way to display sub items, kind of like pivot table in Excel.

BobM — Fri, 31 May 2013 21:28:01 GMT

would this be what you want?

| stats values(ip) as IPs by url

Re: Looking for a way to display sub items, kind of like pivot table in Excel.

oleg106 — Sat, 01 Jun 2013 01:41:03 GMT

Thanks, but that's no quite it. I already tried it before. It doesn't give me a count for each site, and also I can't figure out a way to sort it by # of IPs per url.

Re: Looking for a way to display sub items, kind of like pivot table in Excel.

BobM — Sat, 01 Jun 2013 08:39:07 GMT

so add another function.

| stats values(ip) as IPs dc(ip) as ipCount by url | sort - ipCount