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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...