How can get an alert for each host in a lookup?

[weddi_eddy]

I currently have a lookup that contains two columns. Hostnames and Location.  I can use the following formula to search for squirrel in all hostnames in this lookup:

"squirrel" [| inputlookup mylookup.csv | fields MY_Hostname | rename MY_Hostname as host]

 Works like a dream. I've also set up an alert for this to trigger once.

The issue I have is that the alert email is consolidated with all the different matches.

For example:

"squirrel" is found:

• a few times in hostname_1
• twice in hostname_3 and,
• 17 times in hostname_8.

The email that is sent contains all the "squirrel" logs for all the hosts. What I would like to do is separate out each alert by individual hostname. So, in our example, I should receive 3 email alerts. One for hostname_1 with a few records, one for hostname_3 with two records and once for hostname_8 with 17 records.

Is there a way to perform a sort of for loop for the lookup so that I can simply update it instead of having to manage a bunch of alerts?

[weddi_eddy]

If I perform a search for:

eventtype="nix-all-logs" sourcetype=syslog "squirrel" | stats values(SourceIP) as data

I get:

data

Performing an audit check... added file on host hostname_1: f+++++++++++++++++: /opt/folder/logs/2022-Sep-15_file.log changes were found when we performed our check. database containing your updates was created and renamed. We will start using the updated database for file changes. file was changed on host hostname_3: d =.... mc.. .. . : /opt/folder/other_folder file was changed on host hostname_3: f >.... mc..H.. . : /opt/folder/logs/file.log

I use SourceIP because when I run:

eventtype="nix-all-logs" sourcetype=syslog "squirrel" | stats values()

SourceIP is the value that contains the data I want to sort. So far, so good.

However, when I include the lookup, it says that there are matches but nothing is returned:

eventtype="nix-all-logs" sourcetype=syslog "squirrel" | stats values(SourceIP) as data by host [|inputlookup mylookup.csv | fields MY_Hostname | rename MY_Hostname as host]

 If run the lookup by itself, it returns a list of each host in the lookup:

|inputlookup mylookup.csv | fields MY_Hostname | rename MY_Hostname as host

If I can get the 'data' column above to be listed by each host from the above lookup, I think that would do what I need. So, in my example, I should get a table that roughly looks like:

hostname_1

Performing an audit check... added file on host hostname_1: f+++++++++++++++++: /opt/folder/logs/2022-Sep-15_file.log changes were found when we performed our check. database containing your updates was created and renamed. We will start using the updated database for file changes.

hostname_3

file was changed on host hostname_3: d =.... mc.. .. . : /opt/folder/other_folder file was changed on host hostname_3: f >.... mc..H.. . : /opt/folder/logs/file.log

From here, I can then tell the alert to alerts on each result.

If you need to see the alerts, what exactly do you need to see to help?

[bowesmana]

I'm not sure why you are adding the subsearch at the end of the stats command - if you want to filter by the values in the lookup, put the |inputlookup subsearch as part of the initial search rather than after the stats.

eventtype="nix-all-logs" sourcetype=syslog "squirrel" 
  [|inputlookup mylookup.csv | fields MY_Hostname | rename MY_Hostname as host]
| stats values(SourceIP) as data by host 

[bowesmana]

It would be useful to see your alert, but to group by the hostname, you should do something along the lines of

your search
| stats xx by host

which will give you one row of data for each host.

'xx' will depend on what you want to include in the row for each host, for example you could do 

list(*) as *

to get a list of all the fields and their contents (up to 100 rows max) or just collect those values you want.

Then the alert is configured to produce an alert for each result. Would this work for you?