Splunk Search

Aggregate report on a multi-value field

chiwang
Explorer

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.

Tags (1)
0 Karma

linu1988
Champion

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

0 Karma

linu1988
Champion

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

0 Karma

chiwang
Explorer

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

0 Karma

linu1988
Champion

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

0 Karma

chiwang
Explorer

Is there a way to get urls in the report?

0 Karma

chiwang
Explorer

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

0 Karma

linu1988
Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...