stats count for multiple columns in query

[bawan]

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,

[elliotproebstel · Accepted solution]

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]

GREAT.. it works...

Thanks a lot.

[493669]

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!

[bawan]

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.

[bawan]

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.

[Sukisen1981]

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

[bawan]

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.