Splunk Search

Compare value in each row from column B with all values in column A

seva98
Path Finder

Hi, it probably very simple problem but looks like I am using wrong queries on Google and can't find a solution. I need to compare value in each row with all the value from the different column.

For example:

Col A    Col B
1        2
2        14
3        -
4        abc
5        a
6        6
7        c
8        33
9        -

And leave only rows where value exists in Col A or is set to "-", so the result in this case will be:

Col A    Col B
1        2
3        -
6        6
9        -
Tags (3)
0 Karma

dmarling
Builder

I'm failing to understand how 3 and 9 in column A would return since there is no 3 or 9 value in column B if you goal is to only return results in A that exist in B. Based on your requirements only COLA=1 and COLB=6 would return, unless you want an OR statement to also include any hyphens in COL B in addition to your initial requirements. If that's the case, this will accomplish that. You are essentially making a recursive query where you limit the results of Col B by what's in Col A first with this query:

| makeresults count=1 
        | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
        | fields - _time 
        | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
        | eval data=trim(data) 
        | mvexpand data 
        | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
        | fields - data 
        | eval search="ColB=\"".ColA."\"" 
        | stats values(search) as search 
        | eval search="(".mvjoin(search, " OR ").")"

This returns results that look like this (ColB="1" OR ColB="2" OR ColB="3" OR ColB="4" OR ColB="5" OR ColB="6" OR ColB="7" OR ColB="8" OR ColB="9")

You then take your main query and put a sub query after a where clause to limit your results. You can add that OR statement to grab the hyphenated Col B values on this:

| makeresults count=1 
    | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
    | fields - _time 
    | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
    | eval data=trim(data) 
    | mvexpand data 
    | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
    | fields - data 
    | where 
        [| makeresults count=1 
        | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
        | fields - _time 
        | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
        | eval data=trim(data) 
        | mvexpand data 
        | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
        | fields - data 
        | eval search="ColB=\"".ColA."\"" 
        | stats values(search) as search 
        | eval search="(".mvjoin(search, " OR ").")"] OR ColB="-"

Your results will then look like this:

ColA    ColB
1        2
3        -
6        6
9        -
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

Sukisen1981
Champion

not clear at least to me.
why is 2 , 14 not expected output? Can you elaborate more on the needed output?

0 Karma

seva98
Path Finder

Because it gets value from row in Col B and checks it against all values in Col A. 14 is not in Col A.

0 Karma

Sukisen1981
Champion

then 6,6 should come since 6 exists in column A and not 6,14? Since 14 does not exist in column A?
I am sorry, I am unable to understand the requirement

0 Karma

seva98
Path Finder

Ah, you are right, that was typo. I corrected the example

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...