topic How to find missing values from a field in Splunk Search

How to find missing values from a field

wkelsey — Wed, 30 Sep 2020 03:34:23 GMT

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

Re: How to find missing values from a field

arjunpkishore5 — Sun, 12 Jan 2020 01:22:54 GMT

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

Re: How to find missing values from a field

to4kawa — Sun, 12 Jan 2020 09:26:55 GMT

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.

Re: How to find missing values from a field

wkelsey — Wed, 30 Sep 2020 03:39:04 GMT

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

Re: How to find missing values from a field

wkelsey — Mon, 13 Jan 2020 17:51:40 GMT

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"

Re: How to find missing values from a field

wkelsey — Mon, 13 Jan 2020 18:15:11 GMT

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!

Re: How to find missing values from a field

to4kawa — Mon, 13 Jan 2020 19:31:53 GMT

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

Re: How to find missing values from a field

wkelsey — Mon, 13 Jan 2020 20:01:57 GMT

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.

Re: How to find missing values from a field

to4kawa — Mon, 13 Jan 2020 20:50:29 GMT

Is my rivised answer(recommended) good?

additional information I have given to my questions.

sorry, I can't find it.

Re: How to find missing values from a field

wkelsey — Mon, 13 Jan 2020 21:46:13 GMT

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"]]]]]

Re: How to find missing values from a field

to4kawa — Mon, 13 Jan 2020 22:00:27 GMT

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

Re: How to find missing values from a field

wkelsey — Mon, 13 Jan 2020 22:04:18 GMT

Excellent, thank you!