How to group by two or many fields fields?

[sshubh]

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. 

[ITWhisperer]

| 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

[sshubh]

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.

[ITWhisperer]

To convert your table to the format you want, use the list aggregator function

| stats list(*) as * by accountName

[sshubh]

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.

[sshubh]

I used following and it worked.

| stats sum(Bought) as "Total Bought" list(*) as * by accountName