topic Count Single Occurrence Based On Value in Splunk Search

Count Single Occurrence Based On Value

fward92 — Thu, 08 Oct 2020 15:14:31 GMT

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!

Re: Count Single Occurrence Based On Value

gcusello — Thu, 08 Oct 2020 15:21:02 GMT

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

Re: Count Single Occurrence Based On Value

fward92 — Thu, 08 Oct 2020 15:30:09 GMT

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

Re: Count Single Occurrence Based On Value

gcusello — Fri, 09 Oct 2020 06:33:06 GMT

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