Splunk Search

Help understanding search command : "Where"

DavidHourani
Super Champion

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

MuS
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

woodcock
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

woodcock
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

DavidHourani
Super Champion

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

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

MuS
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

DavidHourani
Super Champion

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

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

DavidHourani
Super Champion

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

vamsiksmile
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

vamsiksmile
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

mprreddy51
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

DavidHourani
Super Champion

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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...