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 🙂

0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...