topic Re: How to create a table of availabilities of a given service for a set of hosts in Splunk Search

How to create a table of availabilities of a given service for a set of hosts?

bsg273 — Wed, 23 Mar 2022 04:11:03 GMT

I'm trying to create a table of availabilities (percent uptime) for a given service for a set of hosts. My desired output is a simple 2-column table of "Host" and "Availability (%)", like the one below:

Host	Availability
my-db-1	100%
my-db-2	97.5%
my-db-3	100%
my-db-4	72.2%

rhnsd Availabilities

I have a query I currently use to get just availability of a service for a single host, but I'd like to scale it larger to create the above output. It assumes ps.sh is running every 1800 seconds and uses the number of events it finds over a give time period (info_max_time-info_min_time) and divides that by the total number of 1800 second intervals that can fit in the given time period, along with some conditions if no host matches or if the availability is >100. That query is as follows:

index=os host="my-db-1.mydomain.net" sourcetype=ps rhnsd | stats count, distinct_count(host) as hostcount | addinfo | eval availability=if(hostcount=0,0,if(count>=(info_max_time-info_min_time)/1800,100,count/((info_max_time-info_min_time)/1800))*100) | table availability

Or if there's a much easier way to accomplish this that I don't know about, I'm all ears. Any help is greatly appreciated.

Re: How to create a table of availabilities of a given service for a set of hosts

thatsabhijeet — Tue, 22 Mar 2022 05:24:56 GMT

index=os host IN ("my-db-1.mydomain.net","my-db-2.mydomain.net","my-db-3.mydomain.net","my-db-4.mydomain.net") sourcetype=ps rhnsd
| stats count, distinct_count(host) as hostcount
| addinfo
| eval availability=if(hostcount=0,0,if(count>=(info_max_time-info_min_time)/1800,100,count/((info_max_time-info_min_time)/1800))*100)
| table host, availability

Re: How to create a table of availabilities of a given service for a set of hosts

bsg273 — Tue, 22 Mar 2022 15:08:52 GMT

Hmmm, this isn't working for me. It's giving me a single row output in Statistics, whereby the host is blank and the availability is a single incorrect value.

Re: How to create a table of availabilities of a given service for a set of hosts

somesoni2 — Tue, 22 Mar 2022 19:47:01 GMT

Give this a try:

**Fixed malformed if condition

index=os host IN ("my-db-1.mydomain.net",..other hosts..) sourcetype=ps rhnsd | stats count by host | addinfo | eval availability=if(count>=(info_max_time-info_min_time)/1800,100,count/((info_max_time-info_min_time)/1800)*100) | table host availability

Please note that any host that is not reporting data will not appear in the result, not even with 0 as availability. To report availability of hosts that may not be reporting at all, you should create a lookup table with list of those hosts (just single column with name "host" will do) and use something like this.

Re: How to create a table of availabilities of a given service for a set of hosts

bsg273 — Tue, 22 Mar 2022 19:36:52 GMT

Thanks! After fixing the malformed if-statement it worked! If you correct yours I can Accept as Solution:

if(count>=(info_max_time-info_min_time)/1800,100,count/((info_max_time-info_min_time)/1800)*100)

Re: How to create a table of availabilities of a given service for a set of hosts

somesoni2 — Tue, 22 Mar 2022 19:47:25 GMT

Correction made to the answer.