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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

Mind trying the version 2 query?

0 Karma

lycollicott
Motivator

Version 2 returns the wrong results.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...