Why did my "| where not" saved search start to error?

[lycollicott]

I have a saved search that started to fail like so....

ERROR SavedSplunker - savedsearch_id="nobody;search;Powered On VMs Without UF", message="Error in 'where' command: The 'not' function is unsupported or undefined.". No actions executed

I can open the saved search and run it without any errors and it was working fine every Monday morning for months, but has recently started to fail when scheduled.

Any thoughts?

| inputlookup VMs.csv | rename "Summary|Guest Operating System|Guest OS Full Name" as OS | search OS="Microsoft Windows*" | eval Name=lower(Name) | fields Name
| where NOT [| metadata index=perfmon type=hosts earliest=-1d@d latest=now
              | where lastTime > relative_time(now(), "-1d@d") 
              |  rex field=host "(?<Name>[^\.]+)" 
              | eval Name=lower(Name) | fields Name]
| sort Name

[somesoni2 · Accepted solution]

Based on the error, it seems the subsearch in where is returning null. Could you try this variation?

| inputlookup VMs.csv | rename "Summary|Guest Operating System|Guest OS Full Name" as OS | search OS="Microsoft Windows*" | eval Name=lower(Name) | eval hasUF=1
| append [| metadata index=perfmon type=hosts earliest=-1d@d latest=now
               | where lastTime > relative_time(now(), "-1d@d") 
               |  rex field=host "(?<Name>[^\.]+)" 
               | eval Name=lower(Name) | fields Name | eval hasUF=2]
| stats sum(hasUF) as hasUF by Name | where hasUF=1 | table Name

Version 2

Update: fixed query

Can you try this as well. Just want to eliminate that metadata command is the issue.

| inputlookup VMs.csv | rename "Summary|Guest Operating System|Guest OS Full Name" as OS | search OS="Microsoft Windows*" | eval Name=lower(Name) | eval hasUF=1
| append [| tstats max(_time) as lastTime WHERE index=perfmon earliest=-1d@d latest=now by host
               | where lastTime > relative_time(now(), "-1d@d") 
               |  rex field=host "(?<Name>[^\.]+)" 
               | eval Name=lower(Name) | fields Name | eval hasUF=2]
| stats sum(hasUF) as hasUF by Name | where hasUF=1 | table Name

[lycollicott]

Ok, so your revision and my original both work when I run them manually in search and they return identical results. However, when I scheduled yours it doesn't error, but it returns the wrong results.

Since both our queries work manually I think it is more of a scheduler issue than syntax.....too much weird for a Monday.

[somesoni2]

Mind trying the version 2 query?

[lycollicott]

Version 2 returns the wrong results.

[somesoni2]

Did you check the scheduler log if the query was run successfully OR what the error was (index=_internal sourcetype=scheduler)?

BTW, there was a type on the version 2, just fixed it.

[lycollicott]

I did check the scheduler logs, but found no clues. They just indicated that your search worked.

Now, that last revision you made has worked both manually and by schedule. Thanks, dude.

(I wish I understood why the exact same NOT search suddenly stopped working in one domain, but still works in another domain with same Splunk version, same SAML setup, yada-yada. Weird.)

[lycollicott]

Hmm. This report is scheduled every Monday and it has failed the last 4 weeks. The last time it ran successfully was the Monday before we converted authentication from LDAP to SAML.

[jmallorquin]

Hi,

Can you post the query?