topic Re: Why did my "| where not" saved search start to error? in Reporting

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

lycollicott — Mon, 29 Aug 2016 15:54:03 GMT

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

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

jmallorquin — Mon, 29 Aug 2016 15:57:16 GMT

Hi,

Can you post the query?

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

somesoni2 — Mon, 29 Aug 2016 16:13:52 GMT

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

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

lycollicott — Mon, 29 Aug 2016 17:01:09 GMT

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.

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

somesoni2 — Mon, 29 Aug 2016 17:04:40 GMT

Mind trying the version 2 query?

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

lycollicott — Mon, 29 Aug 2016 17:30:42 GMT

Version 2 returns the wrong results.

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

lycollicott — Mon, 29 Aug 2016 19:25:06 GMT

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.

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

somesoni2 — Mon, 29 Aug 2016 21:06:31 GMT

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.

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

lycollicott — Tue, 30 Aug 2016 14:02:30 GMT

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