topic Re: How can I compare the count of two hosts? in Splunk Search

How can I compare the count of two hosts?

karthi2809 — Thu, 17 Aug 2017 12:12:55 GMT

How to compare the two host events ?

index=test| stats count by host | stats list(count) as count by host

my result is :

How to identify if there is a large variation in count between host1 and host 2 ?

Re: How can I compare the count of two hosts?

somesoni2 — Thu, 17 Aug 2017 13:53:06 GMT

Will there always be 2 hosts or there can be more? What is your expected output?

Re: How can I compare the count of two hosts?

DalJeanis — Thu, 17 Aug 2017 14:10:47 GMT

What is the relationship between host1 and host2? Typically, no two hosts will have the same statistics for anything, so I'm not sure what "big difference" might mean in your context.

Re: How can I compare the count of two hosts?

karthi2809 — Fri, 18 Aug 2017 05:46:47 GMT

How to identify if there is a large variation in count between host1 and host 2 ?

Re: How can I compare the count of two hosts?

cmerriman — Fri, 18 Aug 2017 14:41:27 GMT

I'm not sure what you're definition of "large variation" is, but there is a command in splunk called delta that should be able to help.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Delta

Also, I'm not sure why you're adding |stats list(count) as count by hostas it should produce the same results as the stats command before it.

index=test| stats count by host|delta count as delta p=1

if you're looking to add the percent, you could use top http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Top

index=test|top host|delta count as delta p=1

Re: How can I compare the count of two hosts?

karthi2809 — Tue, 22 Aug 2017 07:33:22 GMT

Hi cmerriman,

Thanks for your valuable query .but requirement is to find percentage of host.i have two servers one is host 1 and another one is host 2.both host have almost same transaction amount.
for eg :
host 1 : 5.1%
host 2: 4.9%

In case of host have huge difference between transaction amount
for eg:

host 1: 5.1
host 2: 2.0

i need to set an alert for this differnce

Re: How can I compare the count of two hosts?

cmerriman — Tue, 22 Aug 2017 12:20:26 GMT

so would index=test|top host|delta percent as delta p=1 work, and then create an alert based on the value of delta? since you only have the two hosts, you could fill in the other line of delta with |sort + percent|filldown delta at the end of the search.

Re: How can I compare the count of two hosts?

DalJeanis — Tue, 22 Aug 2017 13:13:00 GMT

Here's some code if you want to see if all the hosts are balanced within 10% of each other. This would alert if the highest host was carrying 10% more of the total traffic than the lowest host.

index=test
| stats count as hostcount by host 
| eventstats max(hostcount) as maxcount min(hostcount) as mincount sum(hostcount) as totalcount
| eval pctcount = round(100*hostcount/totalcount,2)
| eval maxdiff = round(100*(maxcount-mincount)/totalcount,2)
| where maxdiff>10.0

Here's some code if you want to see if the two lowest hosts are balanced within 5% of each other. This compares the lowest to the second-lowest, alerting if one of them is carrying 5% more of the total load than the other. The code would function even if there was only one host, but it would obviously never alert in that case.

index=test
| stats count as hostcount by host 
| eventstats max(hostcount) as maxcount min(hostcount) as mincount sum(hostcount) as totalcount
| eval pctcount = round(100*hostcount/totalcount,2)
| eventstats min(eval(if(hostcount=mincount,null(),hostcount))) as min2count
| eval diffcount = round(100*(min2count-mincount)/totalcount,2) 
| where maxdiff>5.0

Re: How can I compare the count of two hosts?

karthi2809 — Mon, 28 Aug 2017 05:50:43 GMT

Hi DalJeanis,
I have two servers .in both two servers splunk forwarder is installed and pointed to index=test.The both servers have same level or more or less of transaction .but in my case the one server have 35 % and another server having 34 % means not a problem . In case of large difference between the percentage in two servers .then i need to set alert for the conditions