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
SplunkTrust
SplunkTrust

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

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

somesoni2
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...