Aggregate report on a multi-value field

[chiwang]

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.

[linu1988]

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

[linu1988]

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

[chiwang]

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

[linu1988]

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

[chiwang]

Is there a way to get urls in the report?

[chiwang]

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

[linu1988]

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