Splunk Search

stats count for multiple columns in query

bawan
New Member

Hello All,

I have query which is returning below result sets in table :Field1, Field2, Field3 are headers and BLANK,NO-BLANK are respective values

Field1, Field2, Field3
BLANK, NO-BLANK,BLANK
NO-BLANK,NO-BLANK,BLANK
BLANK,NO-BLANK,BLANK
NO-BLANK,NO-BLANK,BLANK
BLANK,BLANK,BLANK

i want to show result as :

COLUMN BLANK_COUNT NO-BLANK_COUNT
Field1 3 2
Field2 1 4
Field3 5 0

Please help me to build query to show output in above format.
Thanks,

Tags (1)
0 Karma
1 Solution

elliotproebstel
Champion

Give this a shot:

your base query
| eval temp=1 
| untable temp column val 
| stats count(eval(val="BLANK")) AS BLANK_COUNT count(eval(val="NO-BLANK")) AS NO-BLANK_COUNT BY column

View solution in original post

elliotproebstel
Champion

Give this a shot:

your base query
| eval temp=1 
| untable temp column val 
| stats count(eval(val="BLANK")) AS BLANK_COUNT count(eval(val="NO-BLANK")) AS NO-BLANK_COUNT BY column

bawan
New Member

GREAT.. it works...

Thanks a lot.

0 Karma

493669
Super Champion

Hi @bawan,
After much trials and errors i finalised below query....have a look

<splunkquery> | table Field1 ,Field2,Field3| replace BLANK WITH 1
| transpose| addtotals row* fieldname="BLANK_COUNT"
| replace 1 with BLANK| replace NO-BLANK WITH 1| addtotals row* fieldname="NO-BLANK_COUNT"
| fillnull
| table column BLANK_COUNT NO-BLANK_COUNT

Let me know if it helps!

0 Karma

bawan
New Member

Thanks for your query,
It showing correct result for No-blank count but Its not showing for Blank count result.

As, may be due to some fields don't have values for Blank count.

I use above solution provided by elliotproebstel.

0 Karma

bawan
New Member

Query is :

splunkqry | table field1,field2,field3
will show below output :
Field1, Field2, Field3
BLANK, NO-BLANK,BLANK
NO-BLANK,NO-BLANK,BLANK
BLANK,NO-BLANK,BLANK
NO-BLANK,NO-BLANK,BLANK
BLANK,BLANK,BLANK
.
.
.

as I have around 5000 values for all fields hence can not use transpose after table query.

| stats count(field1) by field1 will give values for field1 as count for BLANK and NO-BLANK
I want count for all fields. I have tried append as well but didn't work.

0 Karma

Sukisen1981
Champion

Please share your query , meanwhile have you explored the transpose function?
| transpose

0 Karma

bawan
New Member

Query :

splunkquery | table Field1 ,Field2,Field3

It will work for Fileld1 as stats count(Field1) by Field1 . but I want it for Field2 and Field3 as well.

I have used transpose but no luck.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...