topic Re: How to lookup the same field from another search and different fields in Splunk Search

How to lookup the same field from another search and different fields

mia — Wed, 22 May 2024 12:00:39 GMT

my search as below, the two <my search command for list user rating list> search command is the same, how to reduce this search command.

I want to use once time <my search command for list user rating list>, mean share the same search results for queries. The transaction sellerId and buyerId could look up user of rating list to get the rating data.

transaction records maybe like as below

orderId	sellerId	buyerId
123	John	Marry
456	Alex	Josh

user rating (all user)

user	rating
Josh	10
Alex	-2
Lisa	1
Marry	3
John	0
Tim	0

excepted result

orderId	sellerId	buyerId	sellerRating	buyerRating
123	John	Marry	0	3
456	Alex	Josh	-2	10

Re: How to lookup the same field from another search and different fields

ITWhisperer — Wed, 22 May 2024 12:16:12 GMT

Try something like this

Re: How to lookup the same field from another search and different fields

mia — Wed, 22 May 2024 17:19:33 GMT

Hi,

thanks for your solution, it's very useful.

I also have a question about

1. when to use join and append

2. when to use search and where, like search oid!="" v.s. where isnotnull(orderId)

Thanks.

Re: How to lookup the same field from another search and different fields

ITWhisperer — Wed, 22 May 2024 17:26:59 GMT

1) avoid join where possible - it is expensive on resources and slow

2) it depends on your comparison
https://docs.splunk.com/Documentation/SCS/current/SearchReference/WhereCommandUsage#Comparing_two_fields

Re: How to lookup the same field from another search and different fields

mia — Thu, 23 May 2024 01:41:15 GMT

Hi,

thanks for you explication,I'll review and rewrite my others search query statements.

thanks.