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

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...