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

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

ashish9433 — Thu, 06 Oct 2016 09:38:40 GMT

Hi Team,

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!

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

inventsekar — Thu, 06 Oct 2016 09:47:50 GMT

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

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

sundareshr — Thu, 06 Oct 2016 11:14:40 GMT

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"

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

ashish9433 — Tue, 29 Sep 2020 11:17:38 GMT

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

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

ashish9433 — Thu, 06 Oct 2016 11:24:25 GMT

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

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

inventsekar — Thu, 06 Oct 2016 11:57:48 GMT

Thanks Ashish.. edited it..

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

vinaykataaig — Wed, 30 Sep 2020 01:41:49 GMT

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"