Splunk Search

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

mia
Explorer

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
Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

mia
Explorer

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

mia
Explorer

Hi, 

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

 

thanks.

0 Karma
Get Updates on the Splunk Community!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...