Splunk Search

How do you get a distinct count of one field based on the value of another?

weidertc
Communicator

I need to count the total based on status, but also the number of sessions for each status. The number of sessions is the distinct count of the sessionId field.

| makeresults 1 | eval status="FAILURE", sessionId=2345 
| append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
| append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
| append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
| append [| makeresults 1 | eval status="SUCCESS", sessionId=1234] 
| stats count AS Total, dc(case(match(status, "FAILURE"), sessionId)) as "TotalSessions" by status

this gives me

Status, Total, TotalSessions
FAILURE, 4, 0
SUCCESS, 1, 0

but it needs to be

Status, Total, TotalSessions
FAILURE, 4, 2
SUCCESS, 1, 1

What am i doing incorrectly?

-Chris

0 Karma
1 Solution

chrisyounger
SplunkTrust
SplunkTrust

Hi Chris,

Try this

 | makeresults 1 | eval status="FAILURE", sessionId=2345 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="SUCCESS", sessionId=1234]
 | stats count AS Total, dc(sessionId) as "TotalSessions" by status

All the best

Edit: sorry updated

View solution in original post

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Hi Chris,

Try this

 | makeresults 1 | eval status="FAILURE", sessionId=2345 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="FAILURE", sessionId=1234] 
 | append [| makeresults 1 | eval status="SUCCESS", sessionId=1234]
 | stats count AS Total, dc(sessionId) as "TotalSessions" by status

All the best

Edit: sorry updated

0 Karma

weidertc
Communicator

ah, I was over-thinking it. Long day. It works. thanks!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...