Splunk Search

Group by two or many fields fields

New Member

Hi

This is my data :

alt text

I want to group result by two fields like that :

alt text

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

0 Karma
1 Solution

Revered Legend

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

View solution in original post

SplunkTrust
SplunkTrust

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
0 Karma

New Member

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

0 Karma

SplunkTrust
SplunkTrust

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       
0 Karma

Revered Legend

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

View solution in original post

New Member

Hi,

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

0 Karma

New Member

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

0 Karma

SplunkTrust
SplunkTrust

Hi,

try the following.

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

New Member

It tried this command but I have the same result

alt text

0 Karma

New Member
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!