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.
@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)
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"
Alright this should do it:
<your_base_search>
| stats values(Status) as statuses by Test_ID
| where isnull(mvfind(statuses,"Failed"))
| stats count
Explanation:
| 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| 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| stats count
- counts the number of results that passed the where clauseHope 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"))
@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)
Thank you @Vijeta . This worked 🙂