How does OR work with strings?

[pm771]

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?

[gcusello]

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 · Accepted solution]

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]

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

[richgalloway]

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.