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

Group by two or many fields fields

Naaba — Tue, 28 Feb 2017 18:33:21 GMT

This is my data :

I want to group result by two fields like that :

I follow the instructions on this topic link text , but I did not get the fields grouped as I want. They are grouped but I don't have the count for each row.

Can anyone help me?

Thanks

Re: Group by two or many fields fields

Naaba — Tue, 28 Feb 2017 18:35:33 GMT

this is the link https://answers.splunk.com/answers/186874/how-to-use-group-by-with-two-fields-1.html

Re: Group by two or many fields fields

horsefez — Tue, 28 Feb 2017 18:44:07 GMT

Hi,

try the following.

your-base-search | stats count by location, book

Re: Group by two or many fields fields

somesoni2 — Tue, 28 Feb 2017 19:29:50 GMT

Give this a try

your base search giving fields Location, Book and Count 
| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location

Re: Group by two or many fields fields

DalJeanis — Tue, 28 Feb 2017 19:56:19 GMT

Your data actually IS grouped the way you want. You just want to report it in such a way that the Location doesn't appear. So, here's one way you can mask the RealLocation with a display "location" by checking to see if the RealLocation is the same as the prior record, using the autoregress function.

This part just generates some test data-

| makeresults | eval mydata = "NYC,book1,3 NYC,book2,5 NYC,book3,3 Boston,book1,32 Boston,book2,51 Boston,book3,32 Dallas,book1,13 Dallas,book3,13 Dallas,book2,15" 
|makemv mydata| mvexpand mydata |makemv delim="," mydata 
| eval Location=mvindex(mydata,0), Book=mvindex(mydata,1), Count=mvindex(mydata,2) 
| table Location, Book, Count

This part sorts it and masks the RealLocation

| sort 0 Location, Book 
| autoregress Location 
| rename Location as RealLocation 
| eval Location=if(RealLocation==Location_p1,"-",RealLocation) 
| table Location Book Count RealLocation

With these results

Location Book   Count RealLocation
Boston   book1   32   Boston
-        book2   51   Boston
-        book3   32   Boston
Dallas   book1   13   Dallas
-        book2   15   Dallas
-        book3   13   Dallas
NYC      book1    3   NYC
-        book2    5   NYC
-        book3    3   NYC

Re: Group by two or many fields fields

DalJeanis — Tue, 28 Feb 2017 20:00:39 GMT

However, you might want to consider this format instead -

|chart sum(Count) over Book by Location

...which gives this result

Book      Boston    Dallas    NYC       
book1     32        13        3         
book2     51        15        5         
book3     32        13        3

Re: Group by two or many fields fields

Naaba — Wed, 01 Mar 2017 08:33:00 GMT

It tried this command but I have the same result

Re: Group by two or many fields fields

Naaba — Wed, 01 Mar 2017 08:36:19 GMT

Hi,

I tried your command but. The data are listed as I want but the count column is empty.
Do you know why?

Re: Group by two or many fields fields

Naaba — Wed, 01 Mar 2017 08:40:12 GMT

I made a mistake in my command.
It worked. thanks for your help

Re: Group by two or many fields fields

Naaba — Wed, 01 Mar 2017 08:57:43 GMT

Hi,

Thanks for your help.
This worked for me :

| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location