topic Re: Group by two or many fields fields in Splunk Search

How to group by two or many fields fields?

sshubh — Wed, 15 Mar 2023 16:29:09 GMT

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.

Re: Group by two or many fields fields

ITWhisperer — Tue, 14 Mar 2023 18:10:03 GMT

| 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

Re: Group by two or many fields fields

sshubh — Tue, 21 Mar 2023 06:56:47 GMT

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.

Re: Group by two or many fields fields

ITWhisperer — Tue, 21 Mar 2023 07:04:35 GMT

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

| stats list(*) as * by accountName

Re: Group by two or many fields fields

sshubh — Wed, 26 Apr 2023 16:35:29 GMT

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.

Re: Group by two or many fields fields

sshubh — Wed, 26 Apr 2023 16:57:28 GMT

I used following and it worked.

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