Splunk Search

Help understanding search command : "Where"

SplunkTrust
SplunkTrust

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

Esteemed Legend

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.

0 Karma

Esteemed Legend

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

SplunkTrust
SplunkTrust

I have no field called 41...what bugs is that both commands are supposed to do them and they dont... it's very weird..

0 Karma

SplunkTrust
SplunkTrust

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.

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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 ?

0 Karma

SplunkTrust
SplunkTrust

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.

SplunkTrust
SplunkTrust

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.

0 Karma

Engager

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 :

  1. The date field's datatype and format
  2. The data type of the field status

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.

0 Karma

Engager

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

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma