I have a query which finds hosts without logs for the whole search and it looks like this:
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?
| 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
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.
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.