How to properly include info that wasn't included in the final result?

[dbrewer1989_mc]

Hello!

I'm relatively new to Splunk but I've worked with databases over the years so I felt like approaching this wasn't too bad. 

The problem: in our situation, we have hosts that exist under our own index for an application. However sometimes those hosts go down or stop reporting logs. That's a separate issue but it's something we want to detect and give the user/client insight into which hosts are up and which ones are down.

So here's what I have so far: ( I attempted a code sample here but it wasn't working )

| union

    [ search index=unique_index host IN ($hosts$) source="<applicationPath>/http_logs/access_log.log"

    | dedup host

    | stats count by host

    | rename host AS hostsFound

    | fields hostsFound]

    [ makeresults

    | eval hosts=split("$hosts$", ",")]

| eventstats values(hosts) as AllHosts

| stats count(hostsFound) as Match dc(AllHosts) as MaxMatch values(hostsFound) as HostsFound values(AllHosts) as AllHosts

| search Match < MaxMatch

| mvexpand AllHosts

| where !(AllHosts in (HostsFound))

| rename AllHosts as HostsMissing

| eval hosts=mvappend(HostsFound,HostsMissing)

| fields hosts,HostsMissing

| mvexpand hosts

| eval count = if(hosts in (HostsMissing), 0, 1)

| table hosts, count | dedup hosts

"$hosts$" is a local variable we have on the dashboard for this query so when a list of hosts are selected, or just one host, then it'll populate there and run the query.

This is a bit of a combination of what I've read on these forums and what I can up with. In the end we're doing the initial query in the union to get what results we have our there for hosts that report back. It's just a tomcat access log. Then the other side of the union are all of the hosts we pass in. In our example we have 7 that report and one that does not, so a total of 8.

This query in the experiences I've had will work if ONE of the hosts doesn't report, like explained above, however if all of the hosts report back then it won't return any results.

So a few questions

1. What can I do to make it return all results if all hosts return data AND if only a few or none of them return data?
2. Can this query be improved, and how? 

I'm still learning how this system works but any insight would be fantastic.

Thank you!

[johnhuang · Accepted solution]

I don't fully understand what you're trying to do but here's a guess/shot. Hopefully this will get you in the right direction. 

search index=unique_index host IN ($hosts$) source="<applicationPath>/http_logs/access_log.log"
| dedup host | eval host_found=1
| append [| makeresults | eval host=split("$hosts$", ",")
| eval host_found=0 | mvexpand host]
| eval host=UPPER(host)
| stats MAX(host_found) AS host_found BY host

[dbrewer1989_mc]

That works!

The whole purpose of what I was doing was to show which hosts didn't return results so we could see if a host wasn't reporting properly. That snippet you provided works exactly how I wanted and it isn't as intense as my solution. Thanks!