topic Re: Matching data across columns in Splunk Search

Matching data across columns

robwx — Wed, 30 Sep 2020 04:34:28 GMT

Hi,
I'm trying to work out how I can display values from a column based on a unique number appearing in another column. Here's what I'm working with:

Col1    Col2
Item_0  Monkey
Item_1  Tiger
Item_2  Gorilla
Type_0  1
Type_1  1
Type_2  0

For example, Item_0 and Type_0 need to line up, resulting in Monkey 1, Item_2 and Type_2 is Gorilla 0 etc.

Thanks in advance, Splunk ninjas!

Re: Matching data across columns

to4kawa — Mon, 16 Mar 2020 12:39:54 GMT

| makeresults 
| eval _raw="Col1,Col2
Item_0,Monkey
Item_1,Tiger
Item_2 ,Gorilla
Type_0,1
Type_1,1
Type_2,0"
| multikv forceheader=1
| table Col1 Col2
| rex field=Col1 "(?<digit>\d)"
| stats values(*) as * by digit
| fields - digit

Re: Matching data across columns

woodcock — Mon, 16 Mar 2020 13:31:39 GMT

Like this:

| makeresults 
| eval _raw="Col1,Col2
Item_0,Monkey
Item_1,Tiger
Item_2,Gorilla
Type_0,1
Type_1,1
Type_2,0" 
| multikv forceheader=1 
| table Col1 Col2

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=Col1 "_(?<which>\d+)$"
| stats values(*) AS * BY which
| foreach Item Type [ eval <<FIELD>> = mvindex(Col2, mvfind(Col1, "<<FIELD>>*")) ]
| table which Type Item