topic Re: How to find hosts without logs by time? in Getting Data In

How to find hosts without logs by time?

skottska — Wed, 22 May 2019 08:50:47 GMT

I have a query which finds hosts without logs for the whole search and it looks like this:

| inputlookup hosts.csv | search NOT [search index=prod ("successfully placed") | dedup host | table host]

What I would like to be able to do is to split this over _time so that I can search for say "over the last 24 hours, which minutes have no logs by host".

I've tested with trying to squeeze in "bucket _time span=1m" but I can't figure out how to combine this with my host.csv lookup.

Anyone got any tips?

Re: How to find hosts without logs by time?

drodman29 — Wed, 18 Dec 2019 18:42:28 GMT

This is what I wrote for my system, the asset file contains the short name as Host and an Environment Column.

Hosts with 0 events are not reporting, and Hosts with Unknown environments are unknown assets.

Re: How to find hosts without logs by time?

gcusello — Thu, 19 Dec 2019 08:13:46 GMT

Hi @skottska,
you have to create a lookup (called e.g. perimeter.csv, containing at least one field, host, and eventually other information that are useful for you, e.g. from a CMDB) and run something like this:

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

It's better to have the search as main search and the lookup in the append because there's the limit of 50,000 results in subsearches and you can use the command | metasearch to speed your search.

If you don't use the last row (| where total=0), you can have the status of you perimeter, that you can also display in graphic mode with icons.

Ciao.
Giuseppe

Re: How to find hosts without logs by time?

woodcock — Fri, 20 Dec 2019 10:08:11 GMT

Like this:

| tstats count WHERE index=* BY host _time span=1m
| timechart limit=0 span=1m first(count) AS count BY host
| fillnull value=0
| untable _time host count
| where count=="0"

I do not see any reason to filter, but if you do, then add this:

| lookup hosts.csv host OUTPUT host AS found
| where isnotnull(found)
| fields - found