The result of the schedule search that is working in my environment is different depending on how to search.
As a premise, the log has two fields, field and field2.
When searching with the following search ①, no results are displayed.
search1
------------------------------
index = test
| search NOT (field = A OR field = B OR field = C)
------------------------------
Search period: 10/16 14: 50 ~ 14: 55
Result:
None
However, when searching with the following search ②, one result will be displayed.
search2
------------------------------
index = test
| search NOT (field = A OR field = B OR field = C)
where field 2 = "111"
------------------------------
Search period: 10/16 14: 50 ~ 14: 55
Result:
field = D, field 2 = 111
In addition, one search result will be displayed in Search ③ below.
search3
------------------------------
index = test
| search field! = A AND field! = B AND field! = C
------------------------------
Search period: 10/16 14: 50 ~ 14: 55
Result:
field = D, field 2 = 111
I do not know the reason why the search results differ with these searches ....
Why are these search results different?
This problem was solved.
When I added " | noop search_optimization=false" to the end of the search , "correct search result" was returned to the search "index = test | search NOT ~".
Likely "search_optimization" worked, and the search "index = test | search NOT ~" was optimized for search "index = test NOT ~".
And maybe the results of the optimized search " index = test NOT ~ " and search " index = test | search NOT ~ ", were different.
So, when I looked up the search job, the cause was unknown, but the application "Configurations Analytics App for Splunk" which I did not use seemed to add extra search condition to the base search.
So when I deleted this App, I got back to what I expected.
Now I will post question about mysterious movement of "Configurations Analytics App for Splunk".
This problem was solved.
When I added " | noop search_optimization=false" to the end of the search , "correct search result" was returned to the search "index = test | search NOT ~".
Likely "search_optimization" worked, and the search "index = test | search NOT ~" was optimized for search "index = test NOT ~".
And maybe the results of the optimized search " index = test NOT ~ " and search " index = test | search NOT ~ ", were different.
So, when I looked up the search job, the cause was unknown, but the application "Configurations Analytics App for Splunk" which I did not use seemed to add extra search condition to the base search.
So when I deleted this App, I got back to what I expected.
Now I will post question about mysterious movement of "Configurations Analytics App for Splunk".
@yutaka1005, sorry my bad! I though NOT with an OR was trying to reverse the gate.
However I tried to sample some data and results were as expected:
1) NOT
returned events where field
was not present
2) !=
matched only events where field
was present
I am not sure of your 2nd test with where
query as it is supposed to return only events matching field2=111
nothing else.
Following is the run anywhere query to mock test data:
| makeresults
| eval field="A"
| append
[| makeresults
| eval field="B"]
| append
[| makeresults
| eval field="C"]
| append
[| makeresults
| eval field="D"]
| append
[| makeresults
| eval field2=111]
| append
[| makeresults
| eval field2=222]
| fields - _time
Gives us the following 6 events
field field2
A
B
C
D
111
222
Search 1: Pipe the following search with NOT
| search NOT (field="A" OR field="B" OR field="C")
We get following three results i.e. event/s withfield
where value is not in A, B or C and event/s withfield2
field field2
D
111
222
Search 2: Pipe the following search with NOT and where
| search NOT (field="A" OR field="B" OR field="C")
| where field2=111
We get only one row with field2 value 111
field field2
111
Search 3: Pipe the following != search
| search field!="A" AND field!="B" AND field!="C"
We get only one event with field not in A, B or C.
The above results are as expected. In case it is not you might have to give run anywhere query with mock/sample data where you think there is discrepancy. Check which are the events which should not have been included or excluded as per expectation.
Adding Splunk Documentation link which also has examples illustrating the difference between both searches: http://docs.splunk.com/Documentation/Splunk/latest/Search/NOTexpressions
Yes! The exam you have done is quite close to my situation!
So the searches ② and ③ presented by me are the expected movements.
However, it is mysterious that the results do not come out at my search ①.
Again, this is not a search problem, is it a fault?
@yutaka1005, your search 3 in the question lists out field2=111 as well. But I guess that is a typo. In the question you had mentioned 5 minutes window. What is the total number of events returned for 5 minutes?
Are you running index="test" | search NOT (field="A" OR field="B" OR field="C")
or
index="test" NOT (field="A" OR field="B" OR field="C")
i.e. NOT filter in the base search itself?
For search 1, field="D" should definitely be returned so can you first run your search 3 with !=
and then change the filter for search 1 i.e. with NOT (both searches with filter in the base search)?
Run this first for the same static timerange (using earliest and latest static date-time values)
index="test" (field!="A" AND field!="B" AND field!="C")
Followed by this for the same static timerange
index="test" NOT (field="A" OR field="B" OR field="C")
Thank you for comment.
As you said, I ran three searchs in same timerange.
Search 1 using "NOT" in the base search
Search 2 using "search" and "NOT" after connecting with pipe
Search 3 using "! ="
timerange
2017/11/09 13:35:00.000
2017/11/09 13:50:00.000
search 1
index=_internal | search NOT (sourcetype="security_nexus" OR sourcetype="mongod" OR sourcetype="splunkd" OR sourcetype="splunkd_access")
result:11065
search 2
index=_internal NOT (sourcetype="security_nexus" OR sourcetype="mongod" OR sourcetype="splunkd" OR sourcetype="splunkd_access")
result:11065
search 3
index=_internal (sourcetype!="security_nexus" AND sourcetype!="mongod" AND sourcetype!="splunkd" AND sourcetype="splunkd_access")
result:9172
This result was in the internal log.
So this is expected result... right???
Moreover, I wanted you to run search 2 instead of search 1. Difference is in performance. Second search is supposed to perform better since you are filtering results in the base search in the seconds query. However, Search 1 gets all the results then applies filters in the 2nd pipe.
Thanks for your comment.
As you say, if I search with "index = _ internal", the correct results are coming out.
However, at the index which originally asked a question, it still has a mysterious movement.
However, this problem was solved.
When I added " | noop search_optimization=false" to the end of the search , "correct search result" was returned to the search "index = test | search NOT ~".
Likely "search_optimization" worked, and the search "index = test | search NOT ~" was optimized for search "index = test NOT ~".
And maybe the results of the optimized search " index = test NOT ~ " and search " index = test | search NOT ~ ", were different.
So, when I looked up the search job, the cause was unknown, but the application "Configurations Analytics App for Splunk" which I did not use seemed to add extra search condition to the base search.
So when I deleted this App, I got back to what I expected.
@yutaka1005, while the answer I had provided provides detail around behavior of NOT and !=, I dont think it addresses your issue.
What you have commented here should actually be converted to an answer (since it seems to be workaround for issue caused by Configuration Analytics App for Splunk
). You should un-accept my answer, convert your comment to answer and accept the same. Also if you can add Tag to this question for Bug
and Configuration Analytics App for Splunk
https://splunkbase.splunk.com/app/3295/. You should also raise a separate question for that App's issue which you have observed. Since the app does not seem to be build/supported/certified by Splunk, you would have to get help from the developer or other community members who have used the app.
@yutaka1005, please read the Splunk Search documentation on the same. You should understand the difference between NOT
and !=
!=
expects the field to be present and then returns events where field value is not equal to the value specified in the expression.
NOT
on the other hand will list above events and additionally if there are events without the field name being searched, they will also be listed.
So you should chose specific one as per your use case.
Splunk Documentation link: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Search#6._Using_the_NOT_or_.21.3D...
Also, similar Splunk Answers question for reference: https://answers.splunk.com/answers/43228/use-of-not-vs.html
Hi niketnilay,
Thank you for answer.
If "NOT" is used, the event that the specified field does not exist is also returned as the result.
When "! =" Is used, only events containing specific values are returned in the specified field, and events for which the specified field does not exist are not returned as a result.
yes I understand these things.
But what I would like to know is below.
1.Why there are more results by using "! =", in spite of "NOT" should return more results?
2.Why when using "NOT" is the result is 0, but when I specified a different field using "where", is the result come out?
Sorry for jumping in ; -
-- 1.Why there are more results by using "! =", in spite of "NOT" should return more results?
So NOT
is a wider net than !=
and therefore it excludes more and the overall result set when using NOT
will be smaller.
Sorry guys.
I can't understand it.
why "NOT" excludes more and the overall result?
I think that "! =" excludes events for which the specified field does not exist, but "NOT" does not exclude events which doesn't have the specified field.
But is it different?
@yutaka1005, I confused the query that it was negating the NOT. Observation with NOT
returning more results than !=
is correct based on the query provided and sample data
.
I tried to mock data to run the three searches and found the behavior is as expected. Please try the run anywhere search examples with three test cases to confirm.
Agree with @ddrillic, we are excluding hence NOT will match with more events and end up excluding more events.