Splunk Search

How to Group and count values by group?

rpradeep
Path Finder

I have a table like below:

Servername  Category                 Status
Server_1        C_1             Completed
Server_2        C_2             Completed
Server_3        C_2             Completed
Server_4        C_3             Completed
Server_5        C_3             Pending
Server_6        C_3             Completed
Server_7        C_4             Completed
Server_8        C_4             Pending
Server_9        C_4             Pending
Server_10       C_4             Pending

I want to get count like below:

Category            Status          Count
C_1             Completed            1
                        Pending                  0
C_2             Completed            2
                        Pending                  0
C_3             Completed            2
                        Pending                  1
C_4             Completed            1
                        Pending                  3

Can this be done in Splunk? Please help

Tags (2)
0 Karma
1 Solution

p_gurav
Champion

Hi,

You can try below query:
| stats count(eval(Status=="Completed")) AS Completed count(eval(Status=="Pending")) AS Pending by Category

View solution in original post

0 Karma

elkhalloufi
Loves-to-Learn

| makeresults | eval _raw="Servername,Category,Status
Server_1,C_1,Completed
Server_2,C_2,Completed
Server_3,C_2,Completed
Server_4,C_3,Completed
Server_5,C_3,Pending
Server_6,C_3,Completed
Server_7,C_4,Completed
Server_8,C_4,Pending
Server_9,C_4,Pending
Server_10,C_4,Pending"
| multikv forceheader=1
| stats count by Category,Status
| stats values(Status) AS Status, values(count) AS Count by Category

0 Karma

rpradeep
Path Finder

Thanks a lot Harshil and Gurav for quick response. Both the answers worked for me, although with a bit different results 🙂
In fact, I found another way too:

  • | chart count over Category by Status

harsmarvania57
SplunkTrust
SplunkTrust

Hi @rpradeep,

You can try below run anywhere search (first ten lines are used to generated dummy data only)

| makeresults | eval Servername="Server_1", Category="C_1", Status="Completed"
| append [ makeresults | eval Servername="Server_2", Category="C_2", Status="Completed"  ]
| append [ makeresults | eval Servername="Server_3", Category="C_2", Status="Completed"  ]
| append [ makeresults | eval Servername="Server_4", Category="C_3", Status="Completed"  ]
| append [ makeresults | eval Servername="Server_5", Category="C_3", Status="Pending"    ]
| append [ makeresults | eval Servername="Server_6", Category="C_3", Status="Completed"  ]
| append [ makeresults | eval Servername="Server_7", Category="C_4", Status="Completed"  ]
| append [ makeresults | eval Servername="Server_8", Category="C_4", Status="Pending"    ]
| append [ makeresults | eval Servername="Server_9", Category="C_4", Status="Pending"    ]
| append [ makeresults | eval Servername="Server_10", Category="C_4", Status="Pending"   ]
| stats count by Category,Status
| stats values(Status) AS Status, values(count) AS Count by Category

So based on this your query will be

<yourBaseSearch>
| stats count by Category,Status
| stats values(Status) AS Status, values(count) AS Count by Category

Thanks,
Harshil

p_gurav
Champion

Hi,

You can try below query:
| stats count(eval(Status=="Completed")) AS Completed count(eval(Status=="Pending")) AS Pending by Category

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