Splunk Search

Splunk Query Help

NAVEEN_CTS
Path Finder

I have a table like below

Test_ID Test_Name Status
123 Test1 Passed
123 Test2 Passed
123 Test3 Failed
123 Test4 Passed
345 Test1 Failed
345 Test2 Passed
567 Test1 Passed
567 Test2 Passed
567 . Test3 Passed

So above table has for each Test ID , there are multiple test name and results for each test name.

Now i need to get the unique count of Test_ID only if all the all the test_name in each id are passed. So ideally from above table i should get count as only 1 ....because only Test ID = 567 has passed in all test.

Tags (1)
0 Karma
1 Solution

Vijeta
Influencer

@NAVEEN_CTS - Try something like below-

index=<yourindex>| stats count(eval(Status="Passed")) as P, count(eval(Status="Failed")) as F by Test_ID| where F=0 and P>0| stats count(Test_ID)

View solution in original post

0 Karma

vnravikumar
Champion

Hi

Give a try

 yourquery....| stats  values(Test_Name) as Test_Name, values(Status) as Status by Test_ID | where mvcount(Status)=1 AND Status="Passed"
0 Karma

aholzer
Motivator

Alright this should do it:

<your_base_search>
| stats values(Status) as statuses by Test_ID
| where isnull(mvfind(statuses,"Failed"))
| stats count

Explanation:

  1. | stats values(Status) as statuses by Test_ID - creates a multivalue field named "statuses" which contains all the possible values of Status for each Test_ID
  2. | where isnull(mvfind(statuses,"Failed")) - excludes any results of the multivalue field statuses that contain a value = to "Failed" - note this uses REGEX so it's case sensitive
  3. | stats count - counts the number of results that passed the where clause

Hope this helps

What I ran to test:

| makeresults 1 | eval Test_ID=123, Test_Name="Test1", Status="Passed"
| append [| makeresults 1 | eval Test_ID=123, Test_Name="Test2", Status="Passed"]
| append [| makeresults 1 | eval Test_ID=123, Test_Name="Test3", Status="Failed"]
| append [| makeresults 1 | eval Test_ID=123, Test_Name="Test4", Status="Passed"]
| append [| makeresults 1 | eval Test_ID=345, Test_Name="Test1", Status="Failed"]
| append [| makeresults 1 | eval Test_ID=345, Test_Name="Test2", Status="Passed"]
| append [| makeresults 1 | eval Test_ID=567, Test_Name="Test1", Status="Passed"]
| append [| makeresults 1 | eval Test_ID=567, Test_Name="Test2", Status="Passed"]
| append [| makeresults 1 | eval Test_ID=567, Test_Name="Test3", Status="Passed"]
| stats values(Status) as statuses by Test_ID
| where isnull(mvfind(statuses,"Failed"))
0 Karma

Vijeta
Influencer

@NAVEEN_CTS - Try something like below-

index=<yourindex>| stats count(eval(Status="Passed")) as P, count(eval(Status="Failed")) as F by Test_ID| where F=0 and P>0| stats count(Test_ID)
0 Karma

NAVEEN_CTS
Path Finder

Thank you @Vijeta . This worked :slightly_smiling_face:

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...