Hello, We have been using this query to list out hosts that are not sending logs since past 24h. It has been working well and for some unknown reason it has now suddenly stopped working. In the sense it does not show any results despite there r hosts that meet the condition. Can someone pls help to figure out why ?
| tstats max(_time) as lastSeen_epoch WHERE index=linux [| inputlookup linux_servers | table host ] by host
| where lastSeen_epoch<relative_time(now(),"-24H")
| eval LastSeen=strftime(lastSeen_epoch,"%m/%d/%y %H:%M:%S")
| fields host LastSeen
Our lookupfile has 700 hosts . Now if i reverse the where condition (just for testing) as shown below ,
where lastSeen_epoch > relative_time(now(),"-24H")
it shows 694 results meaning there are 6 hosts (700-694) that are not logging. So why is the original query not display the 6 hosts ?
Thanks
Hi @neerajs_81,
please try ths different search:
| metasearch index=linux [ | inputlookup linux_servers | table host ] earliest=-24h@h latest=now
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup linux_servers | eval host=lower(host), count=0 | table host count ]
| stats sum(count) AS total BY host
| where total=0
Ciao.
Giuseppe
Ciao.
Giuseppe
Hi @neerajs_81,
maybe it will be a stupid try, but adding equal to the condition?
| tstats max(_time) as lastSeen_epoch WHERE index=linux [| inputlookup linux_servers | table host ] by host
| where lastSeen_epoch<=relative_time(now(),"-24H")
| eval LastSeen=strftime(lastSeen_epoch,"%m/%d/%y %H:%M:%S")
| fields host LastSeen
Ciao.
Giuseppe
That didn't work either. It shows 0 results. Thanks for responding.
Hi @neerajs_81,
please try ths different search:
| metasearch index=linux [ | inputlookup linux_servers | table host ] earliest=-24h@h latest=now
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup linux_servers | eval host=lower(host), count=0 | table host count ]
| stats sum(count) AS total BY host
| where total=0
Ciao.
Giuseppe
Ciao.
Giuseppe
@gcusello That query worked. So how is it that this one is working while the earlier isn't ? Secondly can you pls clarify why are we appending the same lookup file again when we have already called the lookup file at the beginning ?
| append [ | inputlookup linux_deployed_servers | eval host=lower(host), count=0 | table host count ]
Hi @neerajs_81,
the subsearch using the lookup at the beginning is only to limit the main search only to the servers of the lookup and avoid other results.
The real check is made after the append.
Ciao.
Giuseppe
Curious - any way to further optimize your search to also make it show LastSeenTime of those hosts ? Even if its in epoch i can convert it into human readable format . I tried the following way in your stats command but the lastSeen column comes out empty.
| stats sum(count) AS total max(_time) as lastTime BY host
Hi @neerajs_81,
it's correct: the algorithm of my search is that total is equal to zero only when there isn't any event in the main search, so you cannot have the LastSeen value.
Maybe this is the problem of your original search.
Ciao.
Giuseppe
Ahh. Didn't realize that. Thanks