Splunk Search
Highlighted

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

Communicator

Hi Team,

Ialt text

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!

0 Karma
Highlighted

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

Champion

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

View solution in original post

Highlighted

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

Communicator

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

0 Karma
Highlighted

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

Champion

Thanks Ashish.. edited it..

0 Karma
Highlighted

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

Explorer

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 "AGENTINSTALLINGSUCCEEDED"
| 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"

0 Karma
Highlighted

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

Legend

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"
Highlighted

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

Communicator

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

0 Karma