Hello dear splunkers,
Can anyone tell me why these two commands give different results ?
sourcetype=shopping date="2015-01-23" | where status= 41 | stats count by _time
And this:
sourcetype=shopping date="2015-01-23" status= 41 | stats count by _time
Thanks a lot!
Regards,
David
Hi DavidHourani,
The where
command allows you to evaluate more complex/structured expressions. Here's a list of functions you can use with eval
and where
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions
In you example the ... | where status=41
compares the value of the status
field to the value of the 41
field. Where as ... | search status=41
compares the status
field value with the string "41"
similar to ... | where status="41"
.
Also you should always try to reduce as much as you can in the first (implicit) search command in terms of efficiency:
sourcetype=shopping date="2015-01-23" status=41
instead of
sourcetype=shopping date="2015-01-23" | search status= 41
Hope this helps ...
cheers, MuS
The best thing to do is open a support ticket at this point because it may be a bug. Be sure to update this Q&A when you find out the full story.
This can happen if there is a (search-time) field named 41
in some of the events. To check this, use this search with the same time-picker value:
sourcetype=shopping date="2015-01-23" "41"=* | stats count by _time
Because of this kind of thing, you should be using this instead:
sourcetype=shopping date="2015-01-23" | search status="41" | stats count by _time
I have no field called 41...what bugs is that both commands are supposed to do them and they dont... it's very weird..
Do either sourcetype=shopping date="2015-01-23" status=41
or sourcetype=shopping date="2015-01-23" | where status="41"
but preferably the first, because it's more efficient.
Hi DavidHourani,
The where
command allows you to evaluate more complex/structured expressions. Here's a list of functions you can use with eval
and where
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions
In you example the ... | where status=41
compares the value of the status
field to the value of the 41
field. Where as ... | search status=41
compares the status
field value with the string "41"
similar to ... | where status="41"
.
Also you should always try to reduce as much as you can in the first (implicit) search command in terms of efficiency:
sourcetype=shopping date="2015-01-23" status=41
instead of
sourcetype=shopping date="2015-01-23" | search status= 41
Hope this helps ...
cheers, MuS
Thank you for your detailed reply. Is it normal then to have different results with :
sourcetype=shopping date="2015-01-23"status=41
and
sourcetype=shopping date="2015-01-23" | search status= 41
I understand that where might be causing an issue but does search behave the same way ?
No, this should not yield different results. BUT - do you search over the same time range ?
Maybe you should provide some log examples so we can check and verify.
Thanks again for your help, I am searching over the same period of time and it doesn't give the same results, I'm going to try to look more into it and meanwhile I will check with my team if I can provide a sample.
I am a bit skeptic about searching a date for a string and also because of the fact that search interprets = as a string comparison operator.
So, here is what I would check :
It might also be a good idea to quote all your literal values to force various commands to interpret them as strings and not fields.
I dont think we need to use a where in your case as the status field seems to be available, The normal use of where is when you want to compare a value to a eval.
So, if you were calculating the status , I would use your first search and I would use your second search if the status field is already available in teh event
both will return same results i check with this query " index=_internal status=200 |stats count by _time" and " index=_internal |where status=200 |stats count by _time" please try with custom time and check
Thank you but the problem is that the result is not the same.. if i use search
instead of where
the both commands should provide the same result, but they don't and that's quite weird in my opinion...