Splunk Search

Group by two or many fields fields

Naaba
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

somesoni2
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

DalJeanis
Legend

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

Naaba
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

DalJeanis
Legend

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

somesoni2
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

Naaba
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

Naaba
New Member

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

0 Karma

horsefez
Motivator

Hi,

try the following.

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

Naaba
New Member

It tried this command but I have the same result

alt text

0 Karma

Naaba
New Member
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

 Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

What's New in Splunk Observability - August 2025

What's New We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what is ...

Introduction to Splunk AI

How are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. Lucky for ...