Count Single Occurrence Based On Value

[fward92]

Hello, 

Need some help with the below. We have multiple entries for a single IP that has multiple results as the Status Field - I want to know 'How many Hosts Passed, How many Failed and how any were Not Attempted'

IP	Status
10.50.50.50	Passed
10.50.50.50	Failed
10.50.50.50	Not Attempted
10.60.60.60	Passed
10.60.60.60	Failed
10.70.70.70	Passed

If I simply do Stats count by Status, i get the below:

Passed: 3
Failed: 2
Not Attempted: 1

But i know there are only 3 IP's so i need a way to know if a host Passed once - Mark it as Passed only

• Count if an IP has a Status of Passed, mark it as 'Passed' 
• If an IP has a status of Failed and Failed only, then count it as failed
• If an IP has a status of Not Attempted and Not Attempted only then mark it as Not Attempted   

So the output should be the same as the below (Because once an IP has a 'Passed', it shouldn't count towards the other values):
Passed: 3
Failed: 0
Not Attempted: 0

Hope the above makes sense and appreciate the help!

[gcusello]

Hi @fward92,

if it's acceptable for you to take the last value for each IP, try something like this:

your_search
| stats last(Status) AS Status BY IP
| stats count BY Status

Ciao.

Giuseppe

[fward92]

Unfortunately, the values in the Status field appear in different orders - Is there a way to do multiple eval commands that take both a 'contains failed' AND Does not contain 'Passed' AS Count_Failed

Something like that might work

[gcusello]

Hi @fward92,

I haven't your data so I cannot test your conditions, but anyway, you can apply the approch of this sample:

index=_internal
| stats values(source) AS source dc(source) AS dc_source BY user
| eval status=if(dc_source=1 OR match(source,"scripted"),"OK","NOK")

In few words:

• if you have only one value you take it,
• if you have more values you takes the ones that match a word,
• then you can exclude the "NOK".

Ciao.

Giuseppe