Splunk Query Help

[NAVEEN_CTS]

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.

[vnravikumar]

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"

[aholzer]

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"))

[Vijeta · Accepted solution]

@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)

[NAVEEN_CTS]

Thank you @Vijeta . This worked 🙂