Splunk Search

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

ashish9433
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
1 Solution

inventsekar
Ultra 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

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

ashish9433
Communicator

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

0 Karma

inventsekar
Ultra 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

ashish9433
Communicator

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

0 Karma

inventsekar
Ultra Champion

Thanks Ashish.. edited it..

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...