Splunk Search

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?

thisissplunk
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 http_doman and http_uri 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(http_uri) needs to lined up with the values(http_uri) 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
1 Solution

javiergn
Super Champion

This should give you what you are looking for.
I wasn't sure about the different between field http_host and http_domain, 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

javiergn
Super Champion

This should give you what you are looking for.
I wasn't sure about the different between field http_host and http_domain, 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

thisissplunk
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

javiergn
Super Champion

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

sundareshr
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
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...