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

[mia]

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.

<my search command for transaction records>
| dedup orderId
| table  orderId, sellerId, buyerId
| join type=left sellerId
[ search <my search command for list user rating list>
| table sellerId, sellerRating]
| search orderId!=""
| table orderId, sellerId, buyerId, sellerRating
| join type=left buyerId
[ search <my search command for list user rating list>
| table buyerId, buyerRating]
| search orderId!=""
| table orderId, sellerId, buyerId, sellerRating,buyerRating

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

[ITWhisperer · Accepted solution]

Try something like this

<my search command for transaction records>
| dedup orderId
| table  orderId, sellerId, buyerId
| append
[ search <my search command for list user rating list>
| table user, rating]
| eval user=if(isnull(sellerId), user, sellerId)
| eventstats values(rating) as sellerRating by user
| eval user=if(isnull(buyerId), user, buyerId)
| eventstats values(rating) as buyerRating by user
| where isnotnull(orderId)
| table orderId, sellerId, buyerId, sellerRating,buyerRating

[mia]

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.

[ITWhisperer]

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_fi...

[mia]

Hi, 

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

thanks.