Why does search to list all missing /non reporting agents instead lists all hosts?

[splunkmarroko]

the below search is for an alert, it is supposed to list all missing / non reporting agents. when I run it it lists all hosts ? can anyone help fix the search please below. greatly appreciated.

index=indexname sourcetype="sourcetypename"
| bin _time span=4d
| eval days_since = floor((now()-lastSeen)/86400)
| stats latest(lastSeen) as lastSeen , values(days_since) as days_since by host
| search days_since>4
| eval lastSeen=strftime(lastSeen, "%Y-%m-%d %H:%M:%S")

[PickleRick]

Let's see 😉

How does this part work (let's trim it a bit):

| stats values(days_since) as ds by host
| search ds>4

The first part will give you multivalued rows of results where each of your results contains _all_ of encountered values of days_since fields.

So you'll get something like

Host1	4 12 32
Host2	4 8 20

And so on

And the second part will match those hosts for which _any of the values_ in the ds field is bigger than 4. That's how searching across multivalued fields works.

As how to fix it... well just find max(lastSeen) by host (in this case it might not be significant but max(lastSeen) will give you the maximal value of that field whether latest(lastSeen) would give you a value od this field from the latest event which in some cases might not be the same.

And if you have that, just use where to limit your results with a condition based on values (you could create a temporary field and search over its values with search but where is more straightforward).

<your_search>
| stats max(lastSeen) as maxls by host
| where now()-maxls>4*86400
| fieldformat maxls=strftime(...whatever...)

And it's better to use fieldformat instead of rendering to a string field in case you'd want to do something later with the field. If you had your date as text (and not just displayed as such), you'd have to reparse it into a timestamp again or you'd have problems with manipulating time.