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!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...