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,
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
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
GREAT.. it works...
Thanks a lot.
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!
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.
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.
Please share your query , meanwhile have you explored the transpose function?
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.