Other Usage

How to join multiple search result and show as a single table for reporting?

sshubh
Explorer

Hi, 

First of all thanks for taking out time and answering doubts. I tried to join 2 search results and show as output in a single table.

Here is the data from 2 logs:

 Log1: 

Book bought by AccountId=Account1, BookName=Book1

Book bought by AccountId=Account1, BookName=Book1

Book bought by AccountId=Account2, BookName=Book3

 

Log2: 

Book sold by AccountId=Account1, BookName=Book1

 

Output wanted:

AccountId bookBoughtName Bought Sold
Account1 Book1 2 1
Account2 Book3 1 0

 

output getting:

AccountId bookBoughtName Bought Sold
Account1 Book1 2 2
Account2 Book3 1 0


Splunk Query I am using:

 

 

 

"Book bought by" | rex field=_raw "Book\sbought\sby\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookBoughtName>.*)"| join type=left Account
[search "Book Sold By" | rex field=_raw "Book\ssold\sbuy\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookSoldName>.*)"]| stats  count as "Bought" count(bookSoldName) as "Sold" by Account bookBoughtName

 

 

 



Please help what i am doing wrong and thanks again for taking out precious time and helping.

 

 

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

There's an error in the query that counts all transactions as both a "Bought" and a "Sold".  See if this fixes it.

"Book bought by" 
| rex field=_raw "Book\sbought\sby\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookBoughtName>.*)"
| join type=left Account
[search "Book Sold By" 
  | rex field=_raw "Book\ssold\sbuy\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookSoldName>.*)"]
| stats  count(bookBoughtName) as "Bought" count(bookSoldName) as "Sold" by Account bookBoughtName
---
If this reply helps you, Karma would be appreciated.
0 Karma

sshubh
Explorer

I tried this as well

"Book bought by" 
| rex field=_raw "Book\sbought\sby\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookBoughtName>.*)"
| join type=left Account
[search "Book Sold By" 
  | rex field=_raw "Book\ssold\sbuy\sAccountId=(?P<Account>\d+),\sBookName=(?P<bookSoldName>.*)"]
| stats  count(bookBoughtName) as "Bought" count(bookSoldName) as "Sold" by Account bookBoughtName

but it's not working, giving the same old result.

I issue I am getting is in the sold count not in the bought count. 

What's happening is. Suppose bought  count is 3 for book1, if sold count for book1 is 0 than it shows 0. but if sold count is more than 0 (let say 1) than instead of showing 1 sold count is showing same as bought count i.e. 3 although it should show bought-3 sold-1 but showing bought-3 sold-3.

In short sold count is either 0 or same as bought count that's the issue i am facing.

Please help!

 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...