topic Re: Count each Item of a multivalue table field in Splunk Search

Count each Item of a multivalue table field

hypePG — Wed, 29 Mar 2017 07:01:27 GMT

Hey everybody,

I got a search in which I'll try to visualize who many calls from an IP a calling a specific URL.
The stats part of my search is kind of easy at the moment and looks like this:

| rename client_host as SourceIP 
| stats values(domain) as Domains count by SourceIP

It gets me a table like this:

SourceIP     Domains            counts
127.0.0.1     www.google.com     12
              www.reddit.com
127.0.0.2     www.google.com      6

The count in the last column only gives me the total amount of calls on all domain the IP called. How do i get a unique value for each domain the IP Adresse called? I tried it on another way, but only could make this work where a single IP had only a single Domain and this was kind of ugly... 😉

Thanks in advance!

Max

Re: Count each Item of a multivalue table field

gcusello — Wed, 29 Mar 2017 07:09:44 GMT

Hi hypePG
try this:

your_search
| stats count by client_host domain
| rename client_host as SourceIP domain AS Domains

Bye.
Giuseppe

Re: Count each Item of a multivalue table field

hypePG — Wed, 29 Mar 2017 07:45:55 GMT

Hey Giuseppe,

thanks for your answer. But unfortunately this doesn't help me with my problem.
With this commands I get the following output.

 SourceIP     Domains            count
  127.0.0.1     www.google.com     6
  127.0.0.1     www.reddit.com      6
  127.0.0.2    www.google.com      6

I don't want a single line for the identical ip.
Regards, Max

Re: Count each Item of a multivalue table field

niketn — Wed, 29 Mar 2017 09:06:12 GMT

@hypePG... following would be one of the ways to show count of SourceIP by Domains

| chart count(Domains) over SourceIP by Domains

| chart count(Domains) over Domains by SourceIP

Re: Count each Item of a multivalue table field

gcusello — Wed, 29 Mar 2017 09:07:34 GMT

Hi hypePG,
it's not so easy but try this:

your_search
| stats count by client_host domain
| eval col=domain+" - "+count 
| stats values(col) AS col values(domain) AS domain by client_host 
| rex field=col "[^-]\s-\s(?<count>\d+)" 
| table client_host domain count
| rename client_host as SourceIP domain AS Domains

Bye.
Giuseppe

Re: Count each Item of a multivalue table field

hypePG — Wed, 29 Mar 2017 13:24:34 GMT

Hey Giuseppe,

this works just fine! I had some trouble understanding your steps, but finally i worked it out.

Thanks alot.

Regards

Re: Count each Item of a multivalue table field

hypePG — Wed, 29 Mar 2017 14:21:00 GMT

Hey @cusello,

I got one further question. I played a little with your search. If i want to add an additional filter, where i only want to see the IP Adresses which have more than "X" requests I added:

 | search count>10

But than I am loosing the multivalue displaying for the domains. At the moment i cant explain why...

Regards,
Max

Re: Count each Item of a multivalue table field

gcusello — Wed, 29 Mar 2017 15:19:59 GMT

Hi hypePG,
if you want to filter the total number of IPs you have to add

| eventstats sum(count) AS Total by host | where Total>X

before the table command.
If instead you want to filter the total number of IPs for each domain you have to add

| where count>X

after the first stats command.

The logic of my search is the following:

at first, I count the occurrences for IP and domains;
I need to insert the eval command because otherwise the order of domain and count fields in the following stats command is different and the only way is to correlate them with eval command;
after I can show all the domains values for each IP;
using rex command, I can extract the count value;
so I can show results.

I hope to be as possible clear!

Bye.
Giuseppe