How to display a message when a specific host is not ingesting?

[auzark]

My dilemma.

index=prod_s3  sourcetype=My_Sourcetype earliest=-30m
(host=2016) OR (host=2018) OR
(host=2015) OR (host=2017)
|stats count as value by host

The above query will return a count for each host that is ingesting, however If one of the above hosts is not ingesting, I wish to alert on that host, displaying the host name as output with a message.

Any help is appreciate.

[auzark]

Hi  Giuseppe and Sanjay,

Thanks for your help....  that works.....       Doh!!!  hosts vs host.

-------------------------------------------------------------------------

I was also trying to use a join, unsuccessfully.

inputlookup NonStop_JumpHosts.csv
| table host

| join type=inner host
[ | search index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| stats count as NumberOfEvents by host ]
| where ISNOTNULL(NumberOfEvents)
| eval Title=host." No data received for the last 30 min."
| eval Description=Title." Splunk has found no events occurring for the ".host." server for the last 30 min."

I was wondering could I have done this with a join command as another methodology?

[gcusello]

Hi @auzark,

I don't like the join solution: I use join only if haven't any other solution and if I have few events, otherwise the search will be very slow!

Anyway, your solution has another problem: it isn't a good approach to use the search in a subsearch because there's the limit of 50,000 results in the subsearch, so you could have a incomplete results from it.

Ciao and happy splunking.

Giuseppe

[auzark]

Hi Sanjay and Giuseppe,

I really appreciate your help and have set up a loookup.csv with the host values I'm concerned about, however, I still can not get it to work . Below are my results, looking for =0 and >0. 
I'm running over a set 3 minute period so that I can capture a count=0.

------NonStop_JumpHosts.csv

hosts
vpw00002015
vpw00002016
vpw00002017
vpw00002018

-----------------Test run #1, where > 0.

index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| eval host=lower(host)
| stats count by host
| append [ | inputlookup NonStop_JumpHosts.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total>0

 25 events (24/02/2022 13:57:00.000 to 24/02/2022 14:00:00.000)

host,total
vpw00002015,24
vpw00002016,1

-----------------Test run #2, where = 0.

index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| eval host=lower(host)
| stats count by host
| append [ | inputlookup NonStop_JumpHosts.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

 25 events (24/02/2022 13:57:00.000 to 24/02/2022 14:00:00.000)

No results found.

-----------------Test run #3, where > 0, over a 15 minute period to show all hosts in lookup.

index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| eval host=lower(host)
| stats count by host
| append [ | inputlookup NonStop_JumpHosts.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total>0

 37 events (24/02/2022 13:45:00.000 to 24/02/2022 14:00:00.000)

host,total
vpw00002015,24
vpw00002016,2
vpw00002017,4
vpw00002018,7

---------------------------------------------------------------------

[gcusello · Accepted solution]

Hi @auzark,

when you created the NonStop_JumpHosts.csv lookup, did you remembered to create also lookup definition?

Anyway, I think that the problem is in the host column of your lookup: in the lookup the field is called "hosts", instead in your search is called "host", so you don't find anything!

So, you have two choices:

change the column name of the lookup or modfy the search in this way:

index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| eval host=lower(host)
| stats count by host
| append [ | inputlookup NonStop_JumpHosts.csv | eval host=lower(hosts), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

To better see results, you could also run the search without the last row, so in one search you have both the situations (presence and absense).

Ciao.

Giuseppe

[auzark]

Hi gcusello,

Another question, if you do not mind.

I have added the below 2 lines to the query you helped me with.  I have been trying to get a field  other than the "host" field from my lookup table to output as part of the Title or Description?

| eval Title=host." No data received for the last xx min."
| eval Description=Title." Splunk has found no events occurring for the ".host." server for the last xx min."

Also can you describe what host=lower(host) is doing?

Thanks..

[gcusello]

Hi @auzark,

host=lower(host) transforms all the hostnames in lowercase to avoid duplicates related to the upper o lower case.

Obviously you can add all the information you like to the search, e.g. you could also add more fields (e.g. IP or Department) to the lookup and display them in the final stats command, in this way:

index=prod_s3 sourcetype=WinEventLog:Security (host=vpw00002016) OR (host=vpw00002018) OR (host=vpw00002015) OR (host=vpw00002017)
| eval host=lower(host)
| stats count by host
| append [ | inputlookup NonStop_JumpHosts.csv | eval host=lower(hosts), count=0 | fields host count IP Department]
| stats values(IP) AS IP values(Department) AS Department sum(count) AS total BY host
| where total=0

Ciao.

Giuseppe

[auzark]

Thanks,   I'll give that a try.

[auzark]

Thanks guys, I'll try tomorrow.

Can the .csv be named something other than perimeter? and can I name my column in the lookup something other than host?

[SanjayReddy]

Hi @auzark 

1.Yes, you can use any custom name that you can refer it , you need to use same in lookup 

2.cloumn name in lookup can be anyname but you need to rename them in qurey , name need to march with host name in index 

| inputlookup <lookupname>.csv | rename <colnameinlookup> as host | eval host=lower(host)

[gcusello]

hi @auzark,

you can use the name you like both for the lookupname and the lookup column.

The only attention, if you use a name different than "host", is that you have to modify a little the search:

index=prod_s3  sourcetype=My_Sourcetype earliest=-30m
| eval host=lower(host)
| stats count by host
| append [ | inputlookup your_lookup.csv  | eval host=lower(your_column_name), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

Ciao.

Giuseppe

[gcusello]

Hi @auzark,

there are many answers in Community aboput this problem.

In few words, the best approach is to create a lookup (called e.g. perimeter.csv with one column called host) containing all the servers to check, and run a search like this:

index=prod_s3  sourcetype=My_Sourcetype earliest=-30m
| eval host=lower(host)
| stats count by host
| append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

If you have only few hosts you could also run something like this:

index=prod_s3  sourcetype=My_Sourcetype earliest=-30m
| eval host=lower(host)
| stats count by host
| append [ | makeresults | eval host=2015, count=0 | fields host count ]
| append [ | makeresults | eval host=2016, count=0 | fields host count ]
| append [ | makeresults | eval host=2017, count=0 | fields host count ]
| append [ | makeresults | eval host=2018, count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

but I hint to use the first soilution because it's more flexible.

Ciao.

Giuseppe

[SanjayReddy]

Hi @auzark 

There are couple ways you can acheive it 

you can use lookup way as suggested by @gcusello  and @adonio in another post , you can refer to following  link  

https://community.splunk.com/t5/Getting-Data-In/How-to-create-an-alert-for-the-host-not-sending-data...