Splunk Search

How to use 'group by' with two fields?

harish_ka
Communicator

I have 5 books. I have to show the count of these 5 books for different location.
I am getting the report like

Location             Book           Count
-------------      --------       ----------
NYC                  Book1            3
NYC                  Book2            5
.
.
.
Boston               Book1            22
Boston               Book2            99
.
.
Dallas               Book1            44
.
.

I have to generate report like below

Location             Book           Count
-------------      --------       ----------
NYC                  Book1            3
-----                Book2            5
-----                Book3            3
-----                Book4            1
-----                Book5            8

Boston               Book1            32
-----                Book2            51
-----                Book3            32
-----                Book4            11
-----                Book5            88

Dallas               Book1            13
-----                Book2            15
-----                Book3            13
-----                Book4            11
-----                Book5            18

Can anyone help me with the query??

Tags (3)
1 Solution

aljohnson_splun
Splunk Employee
Splunk Employee

It sounds like you need a nested stats, like this:

| stats count by book location
| sort count 
| stats list(book), list(count) by location

Breaking down the search

  1. Get a count of books by location | stats count by book location, so now we have the values.
  2. Then we sort by ascending count of books | sort count
  3. Lastly, we list the book titles, then the count values separately by location |stats list(book), list(count) by location

View solution in original post

harish_ka
Communicator

it worked, Thanks.

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

It sounds like you need a nested stats, like this:

| stats count by book location
| sort count 
| stats list(book), list(count) by location

Breaking down the search

  1. Get a count of books by location | stats count by book location, so now we have the values.
  2. Then we sort by ascending count of books | sort count
  3. Lastly, we list the book titles, then the count values separately by location |stats list(book), list(count) by location

View solution in original post

alcastic
New Member

Thank you to much! I was useful for me too! but one question more: for each "Location", how i can get the "Book" with max "count"? Ex:

Location       Book       Count
-------------  --------   ----------
 NYC            Book5       3
 Boston         Book5       32
 Dallas         Book5       13
0 Karma

harish_ka
Communicator

Thanks AlJohnson. It worked 🙂

This answer also helped me..
(http://answers.splunk.com/answers/73898/removing-duplicates-in-exported-report-results.html)

harish_ka
Communicator

In the first report,
NYC Book1 3
NYC Book2 5

NYC are repeated in each row.I want report in which location are not repeated.
i used query as eval count as ABC by location,Book

0 Karma

aweitzman
Motivator

That's not a valid search. I would have expected stats count as ABC by location, Book. And that search would return a column ABC, not Count as you've shown here.

Anyways, my best guess is that it will be difficult to do exactly what you're asking. You really shouldn't expend a lot of effort trying to make the search language change the presentation of tables. Presentation is what charts are for!

If you change your search to chart count by location, Book you'll get a result where one of each location is in the first column, plus one column for each book with the appropriate count. If you're just trying to compress the presentation, that might help.

Plus, you can easily turn this into a column or bar chart.

harish_ka
Communicator

Can anyone help me????

0 Karma

aweitzman
Motivator

I'm not sure I understand the question. I don't see a difference between the two reports except that the top one has fewer entries. What exactly is the difference that you are trying to accomplish?

Also, what is the search you are using to generate the top table?

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.