How to Group and count values by group?

[rpradeep]

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

[elkhalloufi]

| 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

[rpradeep]

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]

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 · Accepted solution]

Hi,

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