topic Re: stats count for multiple columns in query in Splunk Search

stats count for multiple columns in query

bawan — Tue, 29 Sep 2020 17:42:19 GMT

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,

Re: stats count for multiple columns in query

Sukisen1981 — Mon, 22 Jan 2018 13:04:40 GMT

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

Re: stats count for multiple columns in query

bawan — Mon, 22 Jan 2018 13:13:50 GMT

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.

Re: stats count for multiple columns in query

bawan — Mon, 22 Jan 2018 13:16:04 GMT

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.

Re: stats count for multiple columns in query

493669 — Mon, 22 Jan 2018 15:17:49 GMT

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!

Re: stats count for multiple columns in query

elliotproebstel — Mon, 22 Jan 2018 15:29:19 GMT

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

Re: stats count for multiple columns in query

bawan — Tue, 23 Jan 2018 05:25:15 GMT

GREAT.. it works...

Thanks a lot.

Re: stats count for multiple columns in query

bawan — Tue, 23 Jan 2018 05:31:04 GMT

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.