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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...