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