topic Re: Aggregate report on a multi-value field in Splunk Search

Aggregate report on a multi-value field

chiwang — Tue, 13 Aug 2013 18:03:28 GMT

I have a data set like the following:

01/21/2013  /root1/url,/root2/url,/root2/url
02/22/2013  /root1/url,/root3/url

and I would like to generate a report like the following

event    root   count    urls
1        root1  1        /root1/url
1        root2  2        /root2/url
                         /root2/url 
2        root1  1        /root1/url
2        root3  1        /root3/url

Is there a way to get what I want using splunk functions where urls are filterd by root in the same row. I was able to use "makemv" and "streamstats" to get the first 3 fields but not able to filter urls based on root value.

Re: Aggregate report on a multi-value field

linu1988 — Tue, 13 Aug 2013 18:28:53 GMT

is the log contains the time/ its just /root1/url,/root2/url,/root2/url ?

Re: Aggregate report on a multi-value field

chiwang — Tue, 13 Aug 2013 18:56:14 GMT

The log has the time and a list of comma separated URLs.

Re: Aggregate report on a multi-value field

linu1988 — Tue, 13 Aug 2013 18:58:02 GMT

Hello,
Please try this. There may be other answers but i do like this.

Sourcetype=blah|rex field=_raw "(?<evt>(?=\s).+)"|eval t=split(evt,",")|mvexpand t|rex field=t "(?<Root>(?!/)\w+(?=/))"|stats count by Root,t|rename t as URL

2nd option:

sourcetype=blah|rex field=_raw "(?<evt>(?=\s).+)"|eval URL=split(evt,",")|mvexpand URL|rex field=URL "(?<Root>(?!/)\w+(?=/))"|eval Timestamp=strftime(_time,"%d/%m/%Y %I:%M:%S %p")|Table Timestamp,Root,URL|eventstats count(URL) as count by Timestamp,Root|dedup Timestamp,Root

Thanks

Re: Aggregate report on a multi-value field

chiwang — Tue, 13 Aug 2013 19:09:11 GMT

Is there a way to get urls in the report?

Re: Aggregate report on a multi-value field

linu1988 — Tue, 13 Aug 2013 19:26:19 GMT

Updated the answer, but it's not the same as you gave in the question

Re: Aggregate report on a multi-value field

chiwang — Tue, 13 Aug 2013 19:44:21 GMT

Thanks. But I would like to have a list of URLs (even if they are duplicated) for reporting purposes.

Re: Aggregate report on a multi-value field

linu1988 — Tue, 13 Aug 2013 20:35:36 GMT

I have added another query, this the best i can think as of now.