Hi,
Here is my Data in 2 logs having 3 fields
Log1 :
AccountName | books bought | bookName |
ABC | 4 | book1, book2, book3, book1 |
DEF | 3 | book1, book2, book2 |
MNO | 1 | book3 |
Log 2 :
AccountName | books sold | bookName |
ABC | 1 | book3 |
DEF | 2 | book2, book2 |
MNO | 1 | book3 |
Result I want :
AccountName | Total Books | bookName | bought | sold |
ABC | 4 | book1 book2 book3 |
2 1 1 |
0 0 1 |
DEF | 3 | book1 book2 |
1 2 |
0 2 |
MNO | 1 | book3 | 1 | 1 |
Can anyone please help me in this.
| eval bookName=split(bookName,", ")
| eval bought_sold=if(isnotnull("books bought"),"bought","sold")
| stats count by AccountName bookName bought_sold
| eval {bought_sold}=count
| fields - bought_sold count
| stats sum(*) as * by AccountName bookName
| fillnull value=0
| stats list(*) as * by AccountName
Hi, I tried following :
index="test" "Books bought" | rex field=_raw "Books\sbought\sAccountName=(?P<accountName>[^,]*),\sBooks=\[(?P<books>[^\]]*)]" | join type=left accountName
[search index="test" "Books Sold" | rex field=_raw "Books\sSold\sAccountName=(?P<accountName>[^,]*),\sBook=(?P<book>.*)"] | eval bookUsed = split(books, ",") | stats count as bought count(book) as sold by accountName bookUsed
I get following result:
accountName | bookUsed | bought | sold |
ABC | Book1 | 2 | 1 |
ABC | Book2 | 1 | 1 |
DEF | Book1 | 3 | 1 |
Now, here the issue is I am getting multiple rows for same accountName but I want only one row for accountName like:
accountName | bookUsed | bought | sold |
ABC | Book1 Book2 | 2 1 | 1 1 |
DEF | Book1 | 3 | 1 |
Anyone please help. I want to learn and apologies for tagging explicitly.
To convert your table to the format you want, use the list aggregator function
| stats list(*) as * by accountName
Thanks for the taking out time and helping.
| stats list(*) as * by accountName
worked out. and i got the following result
accountName | bookUsed | bought | sold |
ABC | Book1 Book2 | 2 1 | 1 1 |
DEF | Book1 | 3 | 1 |
I used below query
index="test" "Books bought" | rex field=_raw "Books\sbought\sAccountName=(?P<accountName>[^,]*),\sBooks=\[(?P<books>[^\]]*)]" | join type=left accountName
[search index="test" "Books Sold" | rex field=_raw "Books\sSold\sAccountName=(?P<accountName>[^,]*),\sBook=(?P<book>.*)"] | eval bookUsed = split(books, ",") | stats count as bought count(book) as sold by accountName bookUsed | stats list(*) as * by accountName
Now, I want to add one more column (total Bought) that contains sum of column bought to get following result:
accountName | bookUsed | Total Bought | bought | sold |
ABC | Book1 Book2 | 3 | 2 1 | 1 1 |
DEF | Book1 | 3 | 3 | 1 |
Can anyone suggest how I can get this. I tried but not able to get the intended result. Thanks in advance.
I used following and it worked.
| stats sum(Bought) as "Total Bought" list(*) as * by accountName