How to find hosts without logs by time?

[skottska]

Hi

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?

[woodcock]

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

[gcusello]

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

[drodman29]

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

| inputlookup MyAssets.csv | append [search * | rex field=host "^(?\w*).?" | stats count by Host ] | fillnull value=0 count | stats values(Environment) as Environment, sum(count) as Events by Host | fillnull value="Unknown Asset Reporting" Environment

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