Splunk Search
Highlighted

Can you use stats count() on each value in a mvfield that was just created in the same stats command by the stats values() function?

Builder

I might be going to deep here but I figured I'd give it shot...

I have a stats command keying off of a domain name. I have the values() of the uri's in the stats command as well:

| stats values(dns_answer) values(http_uri) values(http_response)  count by http_domain

I don't want to key off of httpdoman and httpuri because that would would make the stats results look messy. I want one nice, clean value of the http_domain on the left hand column as I scroll through results, not a ton of the same one repeating. If you still think I should just key off both, you'll have to trust me because the above is only a sample query. The real data is much more complicated and would become even messier.

Anyways, is there a way to do something like this? count(values(http_uri)

| stats values(dns_answer) values(http_uri) count(values(http_uri) values(http_response) count by http_domain

Or rather, can I get a count of events for each value inside the values() command? Example output

http_host  values(dns_answer) values(http_uri) count(values(http_uri) values(http_respons) 
google.com 1.2.3.4            /search+blah1     3                     200
           4.5.6.7            /search+blah2     10                    408
                              /search+blah3     2     

My guess is that this is not feasible due to how the count(values(httpuri) needs to lined up with the values(httpuri) to make sense, whereas nothing else in the stats command would be. Are there other ways to do this without keying off of the uri in the stats command?

Tags (3)
0 Karma
Highlighted

Re: Can you use stats count() on each value in a mvfield that was just created in the same stats command by the stats values() function?

Legend

Try this

... | stats values(dns_answer) values(http_uri) count(values(http_uri) values(http_response) count by http_domain | foreach * [ eval <<FIELD>>=if((<<MATCHSTR>>=http_domain, <<FIELD>>, mvcount(<<FIELD>>)) ]
0 Karma
Highlighted

Re: Can you use stats count() on each value in a mvfield that was just created in the same stats command by the stats values() function?

SplunkTrust
SplunkTrust

This should give you what you are looking for.
I wasn't sure about the different between field httphost and httpdomain, so I used the latter.
Keep in mind I had to use mvzip to join counts and http_uri together, as otherwise you lose the ability to link one to the other because of the sorting.

There's probably an easier way of doing it by the way:

your base search
| stats 
    values(dns_answer) as dns_answer, 
    count(http_uri) as count, 
    values(http_response) as http_response
    by http_domain, http_uri
| eval http_uri_count = mvzip(http_uri, count, ">>>")
| fields - http_uri, count
| eventstats 
    values(dns_answer) as dns_answer, 
    values(http_response) as http_response, 
    values(http_domain) as http_domain
| stats 
    values(http_uri_count) as http_uri_count, 
    values(dns_answer) as dns_answer, 
    values(http_response) as http_response, 
    values(http_domain) as http_domain
| rex field=http_uri_count "(?<http_uri>[^>]+)>>>(?<count>\d+)"
| fields - http_uri_count
| table http_domain, dns_answer, http_uri, count, http_response

View solution in original post

Highlighted

Re: Can you use stats count() on each value in a mvfield that was just created in the same stats command by the stats values() function?

Builder

Oh man, awesome! However I left out a crucial point that may prevent this from working..... I'm using a join in my search, and http_uri will not be in every event. That's why I use values() in stats. Some of those will just be blank. In fact, almost all of the fields will be in one of the events or the other, breaking any stats command not using values(). Maybe a fillnull will work?

I'm cutting lines and seeing where it's breaking currently. Should know soon.

0 Karma
Highlighted

Re: Can you use stats count() on each value in a mvfield that was just created in the same stats command by the stats values() function?

SplunkTrust
SplunkTrust

I would simply do the following before my query above:

| eval http_uri = coalesce(http_uri, "")

Or maybe use a slash:

| eval http_uri = coalesce(http_uri, "/")

Fillnull should work too

0 Karma