Splunk Search

How does OR work with strings?

pm771
Communicator

Hello,

I noticed that 

... WHERE somefield = string1 OR string2

works the same way as 

... WHERE somefield = string1 OR somefield=string2


Why is it so? How OR works with strings?

Labels (1)
Tags (3)
0 Karma
1 Solution

acharlieh
Influencer

Unfortunately it is not clear which SPL command you are using here (or if you're talking about before the first pipe and therefore the search command itself), and that is important because semantics can be different for each.

To let folks follow along, I'm going to make some sample data like so, and call this my <base> query:

 

| makeresults
| eval somefield=split("one;two;three;four",";"), lol="where", otherfield="three", thirdfield="five"
| mvexpand somefield 
| collect testmode=true 
| fields - lol

 

Using this you'll see we have 4 results, with three extracted fields, and a raw body of some sample data. We can then follow this with other commands and talk about what they do / how they work. 

So let's talk about the search command:

<base>
| search WHERE somefield = one OR two


If you execute this, you'll get back two results. But what's actually going on here, is we're looking for events whose _raw field contains the word "where"  AND ( either has a called somefield set to the value "one" OR whose _raw field contains the value "two" ) . WHERE is not a keyword for the search command, and so is being treated as like just another word. 

You'll see that we can get rid of the "WHERE" in this command, and it works just the same. (Again, other SPL commands may have other semantics)

<base>
| search somefield = one OR two

Same two results come back

Now realizing that the raw string is being looked for as a term in _raw may become more apparent if we change "two" to "five", i.e.

 

<base>
| search somefield = one OR five

And now we get all rows back because while somefield didn't contain "five" the text of the raw event does (namely as part of thirdfield)

And the importance of keywords with search may also become apparent when we change the OR to lowercase: 

<base>
| search somefield = one or five

then OR is no longer being treated as a keyword and we get no results back.  (we are looking for somefield="one" AND TERM(or) AND TERM(two) ) 

 

Now if we looked for somefield with the values of either one or five 

<base>
| search somefield = one OR somefield = five

we only get one result back as expected. (while "five" shows up in the _raw event, only thirdfield has it as a value)

Now we change back to your other original query (without the extra WHERE), and get back two results: 

<base>
| search somefield = one OR somefield = two

but we got to these two results in a slightly different manner than the original. 

With currently supported versions of Splunk, there is also now an IN operator as well: 

<base>
| search somefield IN (one, two)


Thats just some of the ins and outs of the "search" command... I'll note that if we move over to the where command that the question becomes rather silly since this:

 

<base>
| where somefield = one OR two

Is actually a syntax error. (Where doesn't interpret strings on the right hand side automatically as strings but rather as field names... and the field named two cannot be a boolean expression)

If we remove the OR two part... like this:

 

 

<base>
| where somefield = one

We'll see more about that interpretation change since we get no results back. (There is no field named one therefore somefield cannot equal it). This is actually one of the powerful things about where as opposed to search, making it easier to compare field values to eachother. Let's change this so we get a result back: 

<base>
| where somefield = otherfield

Now we get the "three" result back. If we wanted to look for a field that has a particular string value with the where command, we have to enclose that string in double quotes: 

<base>
| where somefield = otherfield OR somefield = "one"

 

 

 

 

Hopefully this gives you a start around a few SPL commands, and helps you understand how meaning can be slightly different with different commands, and answers your question about how with the search command (which is implicit before the first pipe) could be working similarly but differently.

If you haven't yet taken them, I definitely recommend the Fundamentals courses through Splunk Education, and the Search tutorial on Splunk Docs. (It's been a while for me, but I believe these semantics and more should be covered in those courses).

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @pm771,

the two condition you shared are different because:

the first is:

| WHERE (somefield = string1) OR (string2)

in other words, you have an OR condition between the condition "somefield=string1" and the search string "string2";

The second one is instead:

| WHERE (somefield = string1) OR (somefield=string2)

so you have an OR condition between "somefield=string1" and "somefield=string2".

In other words the second condition is similar but more strong than the first.

The OR condition can work using strings and pairs field=value as you need.

For more infos see at https://docs.splunk.com/Documentation/SCS/current/Search/Predicates#Logical_operators

Ciao.

Giuseppe

acharlieh
Influencer

Unfortunately it is not clear which SPL command you are using here (or if you're talking about before the first pipe and therefore the search command itself), and that is important because semantics can be different for each.

To let folks follow along, I'm going to make some sample data like so, and call this my <base> query:

 

| makeresults
| eval somefield=split("one;two;three;four",";"), lol="where", otherfield="three", thirdfield="five"
| mvexpand somefield 
| collect testmode=true 
| fields - lol

 

Using this you'll see we have 4 results, with three extracted fields, and a raw body of some sample data. We can then follow this with other commands and talk about what they do / how they work. 

So let's talk about the search command:

<base>
| search WHERE somefield = one OR two


If you execute this, you'll get back two results. But what's actually going on here, is we're looking for events whose _raw field contains the word "where"  AND ( either has a called somefield set to the value "one" OR whose _raw field contains the value "two" ) . WHERE is not a keyword for the search command, and so is being treated as like just another word. 

You'll see that we can get rid of the "WHERE" in this command, and it works just the same. (Again, other SPL commands may have other semantics)

<base>
| search somefield = one OR two

Same two results come back

Now realizing that the raw string is being looked for as a term in _raw may become more apparent if we change "two" to "five", i.e.

 

<base>
| search somefield = one OR five

And now we get all rows back because while somefield didn't contain "five" the text of the raw event does (namely as part of thirdfield)

And the importance of keywords with search may also become apparent when we change the OR to lowercase: 

<base>
| search somefield = one or five

then OR is no longer being treated as a keyword and we get no results back.  (we are looking for somefield="one" AND TERM(or) AND TERM(two) ) 

 

Now if we looked for somefield with the values of either one or five 

<base>
| search somefield = one OR somefield = five

we only get one result back as expected. (while "five" shows up in the _raw event, only thirdfield has it as a value)

Now we change back to your other original query (without the extra WHERE), and get back two results: 

<base>
| search somefield = one OR somefield = two

but we got to these two results in a slightly different manner than the original. 

With currently supported versions of Splunk, there is also now an IN operator as well: 

<base>
| search somefield IN (one, two)


Thats just some of the ins and outs of the "search" command... I'll note that if we move over to the where command that the question becomes rather silly since this:

 

<base>
| where somefield = one OR two

Is actually a syntax error. (Where doesn't interpret strings on the right hand side automatically as strings but rather as field names... and the field named two cannot be a boolean expression)

If we remove the OR two part... like this:

 

 

<base>
| where somefield = one

We'll see more about that interpretation change since we get no results back. (There is no field named one therefore somefield cannot equal it). This is actually one of the powerful things about where as opposed to search, making it easier to compare field values to eachother. Let's change this so we get a result back: 

<base>
| where somefield = otherfield

Now we get the "three" result back. If we wanted to look for a field that has a particular string value with the where command, we have to enclose that string in double quotes: 

<base>
| where somefield = otherfield OR somefield = "one"

 

 

 

 

Hopefully this gives you a start around a few SPL commands, and helps you understand how meaning can be slightly different with different commands, and answers your question about how with the search command (which is implicit before the first pipe) could be working similarly but differently.

If you haven't yet taken them, I definitely recommend the Fundamentals courses through Splunk Education, and the Search tutorial on Splunk Docs. (It's been a while for me, but I believe these semantics and more should be covered in those courses).

isoutamo
SplunkTrust
SplunkTrust
Excellent explanation @acharlieh!
This "| collect testmode=true " was also nice trick !
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Are you sure they're the same?  Create an event with otherfield=string2 and then try those two queries again.  I think you'll find they're different.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...