topic Re: Display hosts with no data in Splunk Search

Display hosts with no data

sepkarimpour — Mon, 15 May 2017 11:40:45 GMT

Currently, I have a search where I'm looking for a specific string in a set of logs across a large number of hosts (62) over the last 4 hours - I'm expecting to see all 62 but only 50 appear. Example of my search: index=... sourcetype=... "abc" | dedup host | table host

Is there a way I can get the 12 missing hosts from the search I'm currently using? When I try NOT "abc", I get all 62 hosts returned which shows that they're all getting picked up correctly.

Can I potentially search for the hosts without the string and remove the first list hosts from the total list in order to get the remaining hosts?

Re: Display hosts with no data

andrey2007 — Tue, 29 Sep 2020 14:04:43 GMT

You can go this fay to add new specific field based on your search "abc" and filter necessary data

index=... sourcetype=... "abc" | eval new_field=if(like(_raw,"%abc%"),"with_abc","without_abc") | stats latest(new_field) as new_field by host | search new_field=without_abc

Re: Display hosts with no data

dineshraj9 — Mon, 15 May 2017 11:56:37 GMT

You have to flag the events where you have the required text and then filter this way -

index=... sourcetype=... | eval exists=(like(_raw,"%abc%"),1,0) | stats max(exists) as exists by host | where exists<1

Re: Display hosts with no data

sepkarimpour — Mon, 15 May 2017 12:26:40 GMT

When I try this, it still brings back no results. When I remove the search at the end, it shows that only "with_abc" values remain, which is the issue I'm having in the first place. I tried removing the string in the first part of the search query, but it just gives me the hosts without that string (which isn't helpful as it's still all of them). I'm just wondering if you can set a value if it doesn't appear at all in the search. Thanks though.

Re: Display hosts with no data

dineshraj9 — Mon, 15 May 2017 12:37:36 GMT

The problem is the base search has "abc" in it. So it won't provide the hosts where the event is not present. Try the search I have provided.

 index=... sourcetype=... | eval exists=(like(_raw,"%abc%"),1,0) | stats max(exists) as exists by host | where exists<1

Re: Display hosts with no data

sepkarimpour — Mon, 15 May 2017 12:40:40 GMT

Similar to the answer above from Andrey, no results are produced and when I remove the 'where ...', only values that have the value of 1 remain.

What does the like (...) part do? Does it look in the raw output of the logs for the string I'm looking for?

Thanks

Re: Display hosts with no data

dineshraj9 — Mon, 15 May 2017 12:51:12 GMT

The like function searches for the pattern in the field you provide(here you are searching if "abc" is part of the _raw event, if yes then set as 1 and else set as 0. So if there are no "abc" events from a host, the max value of exists field would be 0.

Check if the text is enclosed with "%" when you add in like function.

Re: Display hosts with no data

sepkarimpour — Mon, 15 May 2017 12:52:27 GMT

Ah! I thought I had tried this but I think I had missed the if as it is above but once I added that back in, it works as I'm wanting. Thanks!

Just following up though: Since it's looking through a huge number of events now (in the last four hours, it went through over 27m events for the last four hours), it's a lot slower than before. Is there any way to optimise this so it's slightly faster/takes less time?

Re: Display hosts with no data

gcusello — Mon, 15 May 2017 12:53:42 GMT

Hi sepkarimpour,
you have to create a lookup with all your hosts (e.g. perimeter.csv where the column with hostname ic called "host") and then run a search like this:

index=your_index sourcetype=your_sourcetype "abc" 
| eval host=upper(host)
| stats count by host
| append [ | inputlookup perimeter.csv | eval count=0, host=upper(host) | fields host, coult ]
| stats sum(count) AS Total by host

in this way:

hosts with Total =0 are missed,
host with Total>0 have logs.

If you like you can create an alert for Total=0 or a dashboard that shows status of each host, this dashboard can also graphically shows status using icons or semaphores.

Bye.
Giuseppe

Re: Display hosts with no data

andrey2007 — Mon, 15 May 2017 13:09:06 GMT

Sure, thanks to dineshraj9
"abc" should be deleted in base search

Also about report speed, for example
you can filter excess events and accelerate your search
http://docs.splunk.com/Documentation/Splunk/6.6.0/Report/Acceleratereports#How_reports_qualify_for_report_acceleration

Re: Display hosts with no data

newbie2tech — Thu, 15 Feb 2018 20:35:38 GMT

thank you cusello this approach helped me today!!!

Re: Display hosts with no data

marlog — Wed, 11 Jul 2018 15:21:53 GMT

Thank you! This was so helpful for me today!