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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...