Splunk Search

Does anyone know of a right way to perform a case match statement with an or condition?

ixixix_spl
Explorer

I am looking to perform a case match search and have found that this query template attempted to answer how to define a case statement with an or condition on two matches. However, when I have used it within my own search I have found that even though the search executes correctly, the table returns with the "k12" row missing, even though "k1", "k2", and "k3" appear. Anyone know of a right way to perform a case match statement with an or condition, or is there a better method I should be following instead?

index=abc sourcetype=xyz 
  | eval w=case( match(_raw,"keyword1"), "k1",
                 match(_raw,"keyword2"), "k2",
                 match(_raw,"keyword3"), "k3",
                 match(_raw,"keyword1") OR match(_raw,"keyword2"), "k12") 
  | chart count by w
Tags (1)
0 Karma
1 Solution

niketn
Legend

[UPDATED ANSWER]

@ixixix_spl, as stated before, if there are multiple case conditions which evaluate to true then only the first one is executed for each event. So if you are searching for keyword1 OR keyword2, then you can have either (1) k12 or (2) k1 and k2 in the results. If you really want k12 count to also show up you will have to add them yourself to data with something like following:

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1,keyword2,keyword3" 
| makemv data delim="," 
| mvexpand data 
| rename data as _raw 
| eval w=case(searchmatch("keyword1"), "k1",
    searchmatch("keyword2"), "k2",
    searchmatch("keyword3"), "k3")
| eval w=if(w=="k1" OR w=="k2",mvappend(w,"k12"),w)
| stats count by w

If you have keyword1 AND keyword2 to be matched in the event though then you can have k12 and k1 and k2 identified in the raw events. You can have query like the following:

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1 keyword2,keyword2,keyword3" 
| makemv data delim="," 
| mvexpand data 
| rename data as _raw 
| eval w=case(searchmatch("keyword1") AND searchmatch("keyword2"), "k12",
    searchmatch("keyword1") AND NOT (searchmatch("keyword2") AND searchmatch("keyword3")), "k1",
    searchmatch("keyword2") AND NOT (searchmatch("keyword1") AND searchmatch("keyword3")), "k2",
    searchmatch("keyword3") AND NOT (searchmatch("keyword1") AND searchmatch("keyword2")), "k3") 
| chart count by w

@ixixix_spl, for each event case() statement will give the first match as the result and will not evaluate subsequent conditions even though they may also be true. So in case K12 has higher priority, you would need to have the same as first condition followed by individual keyword based conditions.

Following is a run anywhere example based on the sample data/query provided.

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1,keyword2,keyword3"
| makemv data delim=","
| mvexpand data
| rename data as _raw
| eval w=case(searchmatch("keyword1") OR searchmatch("keyword2"), "k12",
              searchmatch("keyword1"), "k1",
              searchmatch("keyword2"), "k2",
              searchmatch("keyword3"), "k3") 
| chart count by w
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

[UPDATED ANSWER]

@ixixix_spl, as stated before, if there are multiple case conditions which evaluate to true then only the first one is executed for each event. So if you are searching for keyword1 OR keyword2, then you can have either (1) k12 or (2) k1 and k2 in the results. If you really want k12 count to also show up you will have to add them yourself to data with something like following:

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1,keyword2,keyword3" 
| makemv data delim="," 
| mvexpand data 
| rename data as _raw 
| eval w=case(searchmatch("keyword1"), "k1",
    searchmatch("keyword2"), "k2",
    searchmatch("keyword3"), "k3")
| eval w=if(w=="k1" OR w=="k2",mvappend(w,"k12"),w)
| stats count by w

If you have keyword1 AND keyword2 to be matched in the event though then you can have k12 and k1 and k2 identified in the raw events. You can have query like the following:

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1 keyword2,keyword2,keyword3" 
| makemv data delim="," 
| mvexpand data 
| rename data as _raw 
| eval w=case(searchmatch("keyword1") AND searchmatch("keyword2"), "k12",
    searchmatch("keyword1") AND NOT (searchmatch("keyword2") AND searchmatch("keyword3")), "k1",
    searchmatch("keyword2") AND NOT (searchmatch("keyword1") AND searchmatch("keyword3")), "k2",
    searchmatch("keyword3") AND NOT (searchmatch("keyword1") AND searchmatch("keyword2")), "k3") 
| chart count by w

@ixixix_spl, for each event case() statement will give the first match as the result and will not evaluate subsequent conditions even though they may also be true. So in case K12 has higher priority, you would need to have the same as first condition followed by individual keyword based conditions.

Following is a run anywhere example based on the sample data/query provided.

| makeresults 
| eval data="keyword1,keyword2,keyword3,keyword4,keyword1,keyword2,keyword3"
| makemv data delim=","
| mvexpand data
| rename data as _raw
| eval w=case(searchmatch("keyword1") OR searchmatch("keyword2"), "k12",
              searchmatch("keyword1"), "k1",
              searchmatch("keyword2"), "k2",
              searchmatch("keyword3"), "k3") 
| chart count by w
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

ixixix_spl
Explorer

Great explanation, thank you very much!

0 Karma

ixixix_spl
Explorer

I went ahead and ran your solution on my system and only the k12 and k3 rows display. Are you having the same problem or is there something within my search preferences that are causing this error?

what i am seeing is below in the imgur link
https://imgur .com/a/BlUc0m1

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...