Splunk Search

Count each Item of a multivalue table field

hypePG
Path Finder

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

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi hypePG
try this:

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

Bye.
Giuseppe

View solution in original post

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi hypePG
try this:

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

Bye.
Giuseppe

hypePG
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

hypePG
Path Finder

Hey Giuseppe,

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

Thanks alot.

Regards

0 Karma
Get Updates on the Splunk Community!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out &gt;&gt; As our brave ...