How to prepare a list of hosts that have not reported in for few days - Please check my SPL listed for errors

[SamHTexas]

I have used the following but not producing any results. Please check it for errors.

... | eval etime=strptime(time, "%d/%m/%Y"), sevenDaysAgo=relative_time(now(), "-7d")| where etime < sevenDaysAgo

[SamHTexas]

Please show me how to look up hosts that have not reported since yesterday for example.

[gcusello]

Hi @SamHTexas,

you could use my search adding the outputlookup command at the end (https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchReference/Outputlookup).

But remember that you have to create the Lookup and the Lookup Definition before to run the outputlookup command.

But my hint is diferent: run a search to list all the servers in your infrastructure and put them in a lookup, so you can use this list to check your infrastructure.

Ciao.

Giuseppe

[SamHTexas]

One more question please. So I have to have a look up table either way? My problem is that we have a 100 thousand servers !! Is there a SPL query that does not require an outlook table? Thx again

[gcusello]

Hi @SamHTexas,

in this case you can use my second answer that doesn't requires a lookup.

| metasearch index=_internal earliest=-30d@d latest=now
| eval check=if(now()-_time<604800,"last Week","Previous weeks")
| stats dc(check) AS dc_check values(check) AS check BY host
| where dc_check=1 AND check="Previous weeks"

Ciao.

Giuseppe

[SamHTexas]

Happy 2022 Bro. Just noticed that I did not give you credit for your help then. So I just did. Thank u for all your help in 2021. Stay safe.

[SamHTexas]

I check the following in our system

| metasearch index=_internal earliest=-30d@d latest=now
| eval check=if(now()-_time<604800,"last Week","Previous weeks")
| stats dc(check) AS dc_check values(check) AS check BY host
| where dc_check=1 AND check="Previous weeks"

 It checks & checks for a long time for events. What I need to do is to get ONLY the names of hosts that HAVE NOT reported in. Please advise & thank you once again

[SamHTexas]

Let me test it out. Bro, I owe you a nice dinner in Rome when I visit there next time. I live in TX but my sister lives in Ticcino in Switzerland. Have a nice day.

[gcusello · Accepted solution]

Hi @SamHTexas,

with great pleasure!

Ciao and happy splunking.

Giuseppe

P.S.: please accept the answer for the other people of Community and Karma Points are appreciated 😉

[gcusello]

Hi @SamHTexas,

if you have a list of the hosts in your perimeter in a lookup (e.g. perimeter.csv with a column called host) to check you could use:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(cout) AS total BY host
| where total=0

 if instead you want to check the hosts of (e.g.) last month and find the ones that didn't send logs in the last seven days, you could run something like this:

| metasearch index=_internal earliest=-30d@d latest=now
| eval check=if(now()-_time<604800,"last Week","Previous weeks")
| stats dc(check) AS dc_check values(check) AS check BY host
| where dc_check=1 AND check="Previous weeks"

 Ciao.

Giuseppe

[SamHTexas]

Thank u very much again. I don't have a lookup table for this purpose. But non of these 2 searches produce any results for me at all. Any idea please? 

[gcusello]

Hi @SamHTexas,

the first part of the first search gives you a list of all hosts that reported that you can insert in a lookup using output lookup, in few words you have to:

• create a lookup (called e.g. perimeter with a column called host);
• create a Lookup definition (called e.g. perimeter);
• | run the following search:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| outputlookup perimeter

Ciao.

Giuseppe

[SamHTexas]

Gracia. Would this tell me the list of hosts ONLY that have not reported in?

[gcusello]

Hi @SamHTexas,

with this search you have all the hosts in a lookup:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| outputlookup perimeter

if you want the ones that doesn't reported in the last week:

| metasearch index=_internal earliest=-30d@d latest=now
| eval check=if(now()-_time<604800,"last Week","Previous weeks")
| stats dc(check) AS dc_check values(check) AS check BY host
| where dc_check=1 AND check="Previous weeks"

Ciao.

Giuseppe

[gcusello]

Hi @SamHTexas,

as I said if you use the "where" condition in the searches of my previous answer you have only the ones that reported before but not in the last week. 

Ciao.

Giuseppe

[SamHTexas]

Thank u for being patient with my learning. Do both of your conditions require a lookup file or only the fist one does? Thanks again

[gcusello]

Hi @SamHTexas,

don't worry, no problem!

Anyway, only the first requires a perimeter lookup.

The first condition has the problem that requires the perimeter lookup to manually maintain, but gives you more control on your perimeter.

The second is easiest to maintain (doesn't requires any intervene), but it has the problem that if you have hosts that didn't send logs in the last month you loose them, at the same time if you dismiss an host, you continue to have alerts for a month.

For these reasons I prefer and usually use the first solution, you eventually could use the first maintaing it using the second.

Ciao and happy splunking.

Giuseppe

P.S.: if this answer solves your need, please accept it for the other people of Community and Karma Points are appreciated 😉