topic Re: Part2: How to join two different result sharing common field? in Splunk Search

Part2: How to join two different result sharing common field?

yohhpark — Tue, 28 Mar 2023 13:22:36 GMT

Let say I have a result below
index = indextest
source=stest

bunch of evals = evals

sourcetype=sttext
| table ID Status Remark Values

ID Status Remark Values

11 PASS CHECKED something something hello

371 FAILED CONFIRMED someting hello SOME

let say I want to input another field from a inputlookup that is correlated with the ID number.

ex)

| inputlookup test

|table ID ActualName

ID ActualName

11 McDonald

371 BurgerKing

HOW TO simply input that result into the first query so that I can get a result as below?

ID ActualValue Status Remark Values

11 McDonald PASS CHECKED something something hello

371 BurgerKing FAILED CONFIRMED someting hello SOME

NOTE

when I try this,

index = indextest
source=stest

bunch of evals = evals

sourcetype=sttext
|append [ | inputlookup test]
|stats values("ID") as ID, values ("Actual Value") as "Actual Value" ...and so on... by System

result comes out

ID ActualValue Status Remark Values

11 , 371 McDonald , BurgerKing PASS, FAILED CHECKED ,CONFIRMED something something hello , someting hello SOME

it's not separated.

Simply how to insert a inputlookup result to a table that shares a one common field.

Re: Part2: How to join two different result sharing common field?

ITWhisperer — Tue, 28 Mar 2023 13:56:15 GMT

index = indextest source=stest bunch of evals = evals sourcetype=sttext | lookup test ID | table ID ActualName Status Remark Values

Re: Part2: How to join two different result sharing common field?

yohhpark — Tue, 28 Mar 2023 14:24:34 GMT

I've tried. it's not working.

index=test

|table System Status

simple need to input

|inputlookup test123

|table System IDnumber

so that result show

System IDnumber Status

struggling 😞

Re: Part2: How to join two different result sharing common field?

ITWhisperer — Tue, 28 Mar 2023 14:27:23 GMT

Why are you using inputlookup when all you appear to need is lookup?

Is there something else in your usecase that requires you to use inputlookup?

Re: Part2: How to join two different result sharing common field?

yohhpark — Tue, 28 Mar 2023 15:33:27 GMT

because it's subsearch.

inputlookup is required.

index does not contain such information.

Re: Part2: How to join two different result sharing common field?

ITWhisperer — Tue, 28 Mar 2023 15:47:02 GMT

From your example, i.e.

ID Status Remark Values

11 PASS CHECKED something something hello

371 FAILED CONFIRMED someting hello SOME

becoming

ID ActualValue Status Remark Values

11 McDonald PASS CHECKED something something hello

371 BurgerKing FAILED CONFIRMED someting hello SOME

using a lookup called test with these contents

ID ActualName

11 McDonald

371 BurgerKing

a lookup as I showed would do this

Exactly how is your actual situation different from the above example which makes a simple lookup not work?

Re: Part2: How to join two different result sharing common field?

yohhpark — Tue, 28 Mar 2023 16:18:39 GMT

I have used your idea.

I got the table first

ID Status Remark

then use lookup to match the ID and output the ActualName

then print the table again.

REASON why didn't work first time was that the lookup table did not have the field as "ID" it had it as such as 'title'. and the original SPL search. therefore, I have renamed ID to title, then did the lookup, and THEN switched the title back to ID and table them out.

worked!! thank you so much. learned a lot