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

[sshubh]

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.

[richgalloway]

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

[sshubh]

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!