How to find missing values from a field

[wkelsey]

Hi,

My database has two data sources.
Data source 1 sends a string with a list of expected values, so the field might look like: exp_val="A B C"

Data source 2 is sending up independent events each with a value. The database might contain re_val="A", re_val="B", re_val="D"

I need Splunk to report that "C" is missing. We should be able to
1 - Split the string into a table
2 - Get all re_val from the database WHICH exist in the split_string_table (to eliminate "D")
3 - diff [split_string_table] [result from 2]

But for the life of me I cannot make it work. Below is my current attempt but I've tried about 100 variants to no avail....

eval split_val_list = 
    [ search index="playground1" user="wkelsey" 
    | stats latest(exp_val) as exp_val by cell_name 
    | eval temp=split(exp_val, " ") 
    | mvexpand temp 
    | table temp] 
| set diff 
    [ search index="playground1" user="wkelsey" 
    | where in(re_val, split_val_list) 
    | table re_val] 
    [ table split_val_list]

An alternative command which gives me the exact opposite of what I want

index="playground1" user="wkelsey"
| stats latest(exp_val) as exp_val 
| eval temp=split(exp_val, " ") 
| mvexpand temp 
| eval matchfield=temp 
| join matchfield 
    [ search index=playground1 user=wkelsey 
    | stats count by re_val 
    | table re_val 
    | eval matchfield= re_val] 
| table re_val

As a new user to Splunk, I really question why they created a new language... The documentation is poor, SQL seems more powerful, and PHP or Python would give users more efficiency. I'm reading many posts of users spending days on simple searches.

Splunk 6.6

[wkelsey]

Here is some example data, the results need to be "C", "X", "Y"

| makeresults 
| eval exp_val="A B C" 
| eval re_val = "" 
| appendpipe 
    [| eval exp_val="X Y Z" 
    | eval re_val="" 
    | appendpipe 
        [| eval exp_val="" 
        | eval re_val="A" 
        | appendpipe 
            [| eval exp_val="" 
            | eval re_val="B" 
            | appendpipe 
                [| eval exp_val="" 
                | eval re_val="D" 
                | appendpipe 
                    [| eval exp_val="" 
                    | eval re_val="Z"]]]]]

Thank you!

[to4kawa · Accepted solution]

Revised:

| makeresults 
| eval exp_val="A B C" 
| eval re_val = "" 
| appendpipe 
    [| eval exp_val="X Y Z" 
    | eval re_val="" 
    | appendpipe 
        [| eval exp_val="" 
        | eval re_val="A" 
        | appendpipe 
            [| eval exp_val="" 
            | eval re_val="B" 
            | appendpipe 
                [| eval exp_val="" 
                | eval re_val="D" 
                | appendpipe 
                    [| eval exp_val="" 
                    | eval re_val="Z"]]]]] 
| eventstats list(re_val) as re_val 
| eval exp_val=split(exp_val," ") 
| stats values(re_val) as re_val by exp_val 
| eval check = if(match(re_val,exp_val),"find","missing")

Recommended:

index="playground1" user="wkelsey" 
| stats latest(exp_val) as exp_val values(re_val) as re_val
| eval exp_val = split(exp_val," ")
| stats values(re_val) as re_val by exp_val 
| where NOT match(re_val,exp_val)

The result is missing value.

The search was the opposite.

---

Previous answer
Sample query:

| makeresults 
| eval exp_val="A B C" 
| eval re_val="A" 
| appendpipe 
    [| eval re_val="B"] 
| appendpipe 
    [| eval re_val="D" 
    | head 1] 
| rename COMMENT as "this is sample, please check it" 
| eval check = if(isnull(mvfind(split(exp_val," "),re_val)),"missing","find")

Recommended:

index="playground1" user="wkelsey" 
| stats latest(exp_val) as exp_val list(re_val) as re_val 
| stats values(exp_val) as exp_val by re_val 
| where isnull(mvfind(split(exp_val," "),re_val))

The result is missing value.

[wkelsey]

In your sample query, the result is includes "D - missing", but I would like the results to include "C - missing" and not any "D - missing".
There could be 1000 "re_val"s, we just want to find what is missing from exp_val

[to4kawa]

Is the search the exact opposite, I'm sorry.
My answer is updated. please confirm,

[wkelsey]

Thanks for the help!
This is closer, but not final.
The results need to include "C", and nothing else. See the additional information I have given to my questions.

[to4kawa]

Is my rivised answer(recommended) good?

additional information I have given to my questions.

sorry, I can't find it.

[wkelsey]

The revised answer is not what I need, sorry.
Here is some example data, the only results given should be "C", "X" and "Y"

 | makeresults 
 | eval exp_val="A B C" 
 | eval re_val = "" 
 | appendpipe 
     [| eval exp_val="X Y Z" 
     | eval re_val="" 
     | appendpipe 
         [| eval exp_val="" 
         | eval re_val="A" 
         | appendpipe 
             [| eval exp_val="" 
             | eval re_val="B" 
             | appendpipe 
                 [| eval exp_val="" 
                 | eval re_val="D" 
                 | appendpipe 
                     [| eval exp_val="" 
                     | eval re_val="Z"]]]]]

[to4kawa]

sorry,I already provide the solution , the display is as you like .

[wkelsey]

Excellent, thank you!

[arjunpkishore5]

Hopefully, I understood your question right

Try this

index=playground1 user=wkelsey 
| stats count by re_val 
| append [ search index="playground1" user="wkelsey"
       | head 1 
       | eval re_val=split(exp_val, " ")
       | mvexpand re_val]
|stats count as total by re_val
| where total==1

[wkelsey]

I didn't get the correct answers when I used this - but before debugging more, it looks like it will put "D" in the final results?
The final result should just be "C"