All hosts without specific event

[erick4x4]

Hello Splunk Community,

I'm trying to write a query to show me a chart (or table) for all hosts in my index in the last 45 min that haven't written a specific string to a log. The below query shows me that it has happened on a single host, but I want two columns in a table: column 1 showing the host name and column 2 showing how many times that string appeared in that log (including all the hosts with 0 times).

Query so far:

index="index" source="C:\\Windows\\System32\\LogFiles\\Log.log" "Detection!" earliest=-45m latest=now | stats count by host

[erick4x4]

This is exactly what I needed. Thank you so much!

[erick4x4]

Thank you for the reply. I wasn't clear enough about the hosts already being in the index. If I run this query:

index="index" source="C:\\Windows\\System32\\LogFiles\\Log.log" earliest=-45m latest=now

I have 34 hosts listed. That Log.log is used for many things. It is constantly being updated. I want to know which hosts have had that Log.log updated but don't have the string "Detection!"

[bowesmana]

If data is not in an index, Splunk cannot create what is not there, so to solve this type of problem you have to

a) get the data of events for hosts that DO write to the index

b) append a list of hosts you want to know about from a lookup file

i.e. your search will look something like this

index="index" source="C:\\Windows\\System32\\LogFiles\\Log.log" "Detection!" earliest=-45m latest=now 
| stats count by host
``` This bit gets all the hosts you want to know about and just contains a field called host ```
| append [ 
  | inputlookup list_of_wanted_hosts.csv
  | eval count=0
]
``` now this joins all together, so you have a list with the counts found and 0 where no data is present ```
| stats max(count) as count by host

[erick4x4]

Essentially, I'm trying to create a checklist of hosts I manage and which ones haven't had this event occur yet. This is the first half of what I want:

index="index" source="C:\\Windows\\System32\\LogFiles\\Log.log" "Detection!" earliest=-45m latest=now | chart count by host

This query shows me 2 columns: host and # of times "Detection!" happened. I just need a 3rd column  or a continuation of the 2nd column that shows hosts with count 0 so I know which ones I still need to work on.

[bowesmana · Accepted solution]

OK, so if ALL your hosts are in the logs, you just need this

index="index" source="C:\\Windows\\System32\\LogFiles\\Log.log" earliest=-45m latest=now 
| eval Detection=if(match(_raw, "Detection!"), 1, 0)
| stats sum(Detection) as Detections by host

This finds all events from Log.Log and then line 2 sets the value of a new field to 1 if the word "Detection!" is found in the event.

Then the stats will add together all the Detection events for each host

This is a key technique in Splunk for getting different sets of information from the same data, by first selecting ALL the data you want to consider and then using the eval statement (Splunk's Swiss Army knife) to set some indicator (in this case, determining if a particular event is the one you are really interested in counting) and then the stats just adds up all detections.

This hosts that do NOT have the Detection! word, will always have Detection=0, so will end up with a Detections column value of 0

Hope this helps.