Splunk Search

Where function not calculating fields as expected

cshadduck
Explorer

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.

0 Karma
1 Solution

cshadduck
Explorer

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.

View solution in original post

0 Karma

cshadduck
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

woodcock
Esteemed Legend

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

cshadduck
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...