I have a low volume index where hosts send one event every 24 hours. I need to determine if each host in today's search (last two weeks) has shown up in at least four times (once each week) for the previous six weeks (eight week total time frame). By using four, this allows a host to be turned off for a couple weeks while the owner is on vacation. I need to generate a list of hosts that meet the above criteria. Using the same criteria, I need to find all the hosts that don't show up today that had been sending events over the last four weeks.
It seems like I need to use a combination of the following two searches. One way I saw to do this is by assigning each host a week/count if the number of events is greater than one with a subsearch per week. Then if the count of weeks per host is four or greater than put the host in the table.
Hosts that have sent at least one event by week.
index=blah sourcetype="blah:win" earliest=-8weeks@w latest=-2w@w | bucket span=7d _time |timechart count by host
Missing host
index=blah sourcetype="blah:win" earliest=-8weeks@w latest=-2w@w | dedup host | eval weeks="Y" | table host weeks | join host type=outer [search index=blah sourcetype="blah:win" earliest=-1d@2w latest=-0d@d | dedup host | eval thisweek = "Y" | table host thisweek ] | where weeks="Y" AND NOT thisweek="Y"
So basically with the following data:
Time | wk1 | wk2 | wk3 | wk4 | wk5 | wk6 |
---|---|---|---|---|---|---|
Host A | 2 | 7 | 10 | 0 | 0 | 5 |
Host B | 0 | 2 | 4 | 7 | 7 | 7 |
Host C | 0 | 1 | 0 | 1 | 0 | 0 |
Host D | 1 | 5 | 0 | 0 | 0 | 0 |
Output
Host A sent events
Host B sent events
Missing host output
Host C is missing
Host D is missing
It would be nice to know when a new host shows up that has never sent data in the last eight weeks.
check out the meta woot app, it makes monitoring your hosts sourcetypes and indexes real simple and includes some compliance reporting you can build on top of:
https://splunkbase.splunk.com/app/2949/
It uses summary indexing and hashes the host sourcetype index combos in kvstore allowing for super performant searching of this data and takes some of the leg work out of having to craft searches like this...
Like this:
index=blah sourcetype="blah:win" earliest=-8weeks@w latest=-2w@w | timechart span=1w count BY host | untable _time host count | eventstats count(eval(count>0)) AS weeks_non_zero BY host | search weeks_non_zero<=2
Then you can go back to timechart
format if you like by adding back:
| timechart span=1w sum(count) AS count BY host
So much for a table of data. Each column represents a week.
Time w 1 2 3 4 5 6
Host A 2 7 1 0 0 5
Host B 0 2 4 7 7 7
Host C 0 1 0 1 0 0
Host D 1 5 0 0 0 0