Hosts that have sent events over the last eight weeks (by week) but taking into account owner vacation

Path Finder

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

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.

Tags (1)
0 Karma

Splunk Employee
Splunk Employee

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:

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...

0 Karma

Esteemed Legend

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
0 Karma

Path Finder

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

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!