Reporting

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

lycollicott
Motivator

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
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Motivator

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.

0 Karma

somesoni2
Revered Legend

Mind trying the version 2 query?

0 Karma

lycollicott
Motivator

Version 2 returns the wrong results.

0 Karma

somesoni2
Revered Legend

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.

0 Karma

lycollicott
Motivator

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

0 Karma

lycollicott
Motivator

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.

0 Karma

jmallorquin
Builder

Hi,

Can you post the query?

0 Karma
Get Updates on the Splunk Community!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...