Help with count of specific string value of all the row and all the fields in table

[ashish9433]

Hi Team,

I

With reference to the screenshot, the part of the table which is highlighted in yellow is what I have and the part which is highlighted in red is what I need to add to the table.

Basically, I want the count of "Yes" for each row in the Splunk table. Some fields may not contain Yes or No. So I would only be interested in all the fields which have Yes and count of it.

Can anyone please guide me on how I should proceed with this?

Thanks!

[sundareshr]

Try like this

base search | eval count=0 | foreach factor* [eval count=count + if(<<FIELD>>="Yes", 1, 0) ] |  table factor* count | rename count AS "Count of Yes"

[ashish9433]

Cool, thanks for providing this alternative. Even this works for me.

[inventsekar · Accepted solution]

Please check this one -

base search |eval count=if(Factor1="Yes ",1,0) |eval count=count+if(Factor2="Yes ",1,0)
|eval count=count+if(Factor3="Yes ",1,0) |eval count=count+if(Factor4="Yes ",1,0) 
|eval count=count+if(Factor5="Yes ",1,0) |eval count=count+if(Factor6="Yes ",1,0) 
|table Host IP field1 Factor1 Factor2 Factor3 Factor4 Factor5 Factor6 count

Best Regards,
Sekar

[ashish9433]

Thanks, This works as required. Only there is a typo - after base search | eval count*Yes*, whereas it should be only count.

[inventsekar]

Thanks Ashish.. edited it..

[vinaykataaig]

Hey Sekar,
I am working on the similar use case and I am impressed with your answer. But my table has Dynamic column names i.e Month names and I only need to use previous 3 months names like lets say we are in august then I have to show data for may, june, july. So how can I use your search to show up the month names. and also i wanted to use chart command instead of table. Below is my query I've tried and I am failing with that. I am not able to include my dashboard screenshot here. Below is how my table looks like and eventually i wanted to add those months values and show up total count in Total Column.

Server_name JUNE JULY AUGUST Total
Host1 Patched Patched Patched 3
host2 NotPatched Patched NotPatched 1
host3 NotPatched NotPatched NotPatched 0

index="win" sourcetype="windowsupdatelog" | search "Deployment" AND "AGENT_INSTALLING_SUCCEEDED"
| rex field=_raw "^(?:[^:\n]:){9}\s+(?P.+)"
| eval servers = Upper(mvindex(split(host,"."),-0))
| eval start=strptime(Time, "%Y-%m-%d %H:%M:%S.%N")
| eval day = strftime(start, "%a")
| eval month = Upper(date_month)
| replace "*KB" WITH "Patched" IN ApplicablePatch
|eval count=if(JANUARY="Patched ",1,0)
|eval count=count+if(FEBRUARY="Patched ",1,0)
|eval count=count+if(MARCH="Patched ",1,0)
|eval count=count+if(APRIL="Patched ",1,0)
|eval count=count+if(MAY="Patched ",1,0)
|eval count=count+if(JUNE="Patched ",1,0)
|eval count=count+if(JULY="Patched ",1,0)
|eval count=count+if(AUGUST="Patched ",1,0)
|eval count=count+if(SEPTEMBER="Patched ",1,0)
|eval count=count+if(OCTOBER="Patched ",1,0)
|eval count=count+if(NOVEMBER="Patched ",1,0)
|eval count=count+if(DECEMBER="Patched ",1,0)
| stats values(ApplicablePatch) as ApplicablePatch sum(count) as Total by servers month | mvexpand servers
| chart values(ApplicablePatch) as InstalledPatch values(Total) as Totals by servers month | fillnull value="Not Patched"