Count each Item of a multivalue table field

[hypePG]

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

[niketn]

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

| chart count(Domains) over SourceIP by Domains

Or

| chart count(Domains) over Domains by SourceIP

[gcusello · Accepted solution]

Hi hypePG
try this:

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

Bye.
Giuseppe

[hypePG]

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

[gcusello]

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

[hypePG]

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

[gcusello]

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

[hypePG]

Hey Giuseppe,

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

Thanks alot.

Regards