Use of IN Clause to find out available host in splunk

[mchoudhary]

Hi Team,

I am a newbie in Splunk. I am using a basic IN clause in a search command to pull out the 36 windows server integration available or not in splunk.

index=* NOT index=_* host IN ("host1", "host2", "host3", ...., "host36")
| stats count by host

It gives me the servers which are available in splunk. I want to find out the missing ones and the available ones in this format-

host	status
host1	Available
host2	Available
host3	Missing

I am using the below query but it only gives 6 statistics.

| makeresults count=1
| eval all_hosts="host1,host2,host3....host36"
| makemv delim="," all_hosts
| mvexpand all_hosts
| rename all_hosts AS host
| append [ search index=* NOT index=_* host=*
    | stats count by host ]
| stats values(count) AS event_count by host
| where host IN ("host1", "host2",....,"host36")
| eval status=if(isnull(event_count), "Missing", "Available")
| table host status
| sort status, host

 Can anyone please help me out what I am doing wrong.

Thanks in Advance!!

[ITWhisperer]

Try something like this

index=* NOT index=_* host=*
| stats count by host
| append
  [| makeresults
  | eval host=split("host1,host2,host3....host36",",")
  | mvexpand host
  | eval count=1]
| stats sum(count) AS event_count by host
| eval status=if(event_count = 1, "Missing", "Available")
| table host status
| sort status, host