Where function not calculating fields as expected

[cshadduck]

I have a basic search to identify systems that have not checked into a service for X amount of time. There is nothing fancy about the search but I must be missing something simple because when I use the where function to compare two fields I get no results. I am searching a list of hostnames, setting a threshold to compare against, and trying to display only events that are older than the set threshold:

index=main sourcetype="app:agent" hostname IN (host1 host2 host3)
| eval hostname=upper(hostname)
| eval threshold=now()-30
| stats latest(_time) as LastCheckin values(threshold) by computer_name
| where LastCheckin<threshold
| eval LastCheckin=strftime(LastCheckin,"%m-%d-%Y %H:%M:%S")

A couple of things to note:

1. App checks in every couple minutes, so for testing the search only checks for events older than 30 seconds ago as I know there are events older than that.
2. Threshold field is included in stats to verify value is in fact evaled correctly
3. Taking out the where function will display results as expected

4. I have another search using inputlookup that does work using the same type of format:

   | inputlookup hosts.csv
   | eval drop_off=now()-1728000
   | where latest < drop_off
   | stats values(latest) as latest by hostname
   | outputlookup hosts_dropoff.csv
   Any thoughts as to why I am getting these results? As I know inputlookup works for another case, I could apply it to this search but would rather not add another step to this process.

[cshadduck · Accepted solution]

So I am at a loss as to why it is working now but it simply needed spaces before and after the < sign. I had tried all these combinations but for whatever reason it started working this morning. Maybe it had something to do with the other changes to the search in combination with that. Whatever the case it is working now:

index=main sourcetype="app:agent" hostname IN (host1 host2 host3)
 | eval hostname=upper(hostname)
 | eval threshold=now()-30
 | stats max(_time) as LastCheckin values(threshold) by computer_name
 | where LastCheckin < threshold
 | timeformat LastCheckin=strftime(LastCheckin,"%m-%d-%Y %H:%M:%S")

Thanks for all the help everyone.

[woodcock]

This is wrong and cannot (and therefore DOES not) work. You DO NOT have any field named threshold. That is the whole problem. See my answer below (which was modified since first posting) for a complete fix.

[woodcock]

You should never use latest(_time) but instead always max(_time); also, use fieldformat be sure that you maintain integer aspect of time fields. The main problem is that you did not name threshold so the field created is values(threshold). This still may not working because values() creates a multivalue field, perhaps even when there is only 1. Try this:

index=main sourcetype="app:agent" hostname IN("host1", "host2", "host3")
| eval hostname=upper(hostname)
| eval threshold=now()-30
| stats latest(_time) as LastCheckin max(threshold) AS threshold BY computer_name
| where LastCheckin<threshold
| fieldformat LastCheckin=strftime(LastCheckin,"%m-%d-%Y %H:%M:%S")

[cshadduck]

Thanks, I like fieldformat as it makes more sense. Why is max(_time) better than latest(_time)?

As for the results, it is still not filtering the events.

[woodcock]

Using max has half the work and in the past has actually been more reliable.

[woodcock]

This is not working because you did not name threshold so it has the name values(threshold) and also values() creates a multivalue field, perhaps even when there is only 1. Try my updated answer.