topic Re: Need to display 0 if the count is 0 in Splunk Search

Need to display 0 if the count is 0

harish_ka — Thu, 06 Nov 2014 15:08:40 GMT

i have 5 columns in my report. i am using appendcols to append columns (to get data of different time range). My report have 7 rows (static) and if the count is 0 for the second row, third row result is added into second row(so seventh row shows no data,as the count are showing in the second row even it is zero).
Please help me, as i tried wilth fillnull also..

Re: Need to display 0 if the count is 0

somesoni2 — Thu, 06 Nov 2014 15:11:15 GMT

Use "|join type=left commonField" instead on appendcols. Please post some sample query for full answer.

Re: Need to display 0 if the count is 0

harish_ka — Mon, 28 Sep 2020 18:06:32 GMT

index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-9d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| |eval TimeStamp=tostring(strftime(_time,"%m/%d/%y - %H:%M:%S %p"))| stats first(TimeStamp) as "Last_Alert_Time" by Alert_Type

|appendcols [search index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-0d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| stats count(Alert_Type) as "# of times occurred today" by Alert_Type ]

Re: Need to display 0 if the count is 0

harish_ka — Thu, 06 Nov 2014 15:29:48 GMT

This is how my query looks...
i can write a common query or for seperate appendcols result

Re: Need to display 0 if the count is 0

somesoni2 — Thu, 06 Nov 2014 16:24:45 GMT

This is how it can be done. Assuming your static Alert_Type values are Alert1, Alert2,...Alert7, then try like this

| gentimes start=-1 | eval Alert_Type="Alert1, Alert2, Alert3, Alert4, Alert5, Alert6, Alert7" | table Alert_Type | makemv Alert_Type delim="," | mvexpand Alert_Type 
| join type=left Alert_Type [search index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-9d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| |eval TimeStamp=tostring(strftime(_time,"%m/%d/%y - %H:%M:%S %p"))| stats first(TimeStamp) as "Last_Alert_Time" by Alert_Type ] 
| join type=left Alert_Type [search index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-0d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| stats count(Alert_Type) as "# of times occurred today" by Alert_Type ] | join type=left Alert_Type [search index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-5d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| stats count(Alert_Type) as "# of times occurred Last 5 DAys" by Alert_Type ]
| join type=left Alert_Type [search index=yyy sourcetype="xxx" "org.apache.axis2.AxisFault: Read timed out" OR "188~-230~no contained exception~Exception during" earliest=-7d@d latest=now| rex "Major org.apache.axis2.AxisFault: (?.)" | rex "188~-230~no contained exception~Exception during (?.) Error from sabre:"| stats count(Alert_Type) as "# of times occurred Last 7 DAys" by Alert_Type ] | fillnull value=0

Re: Need to display 0 if the count is 0

harish_ka — Thu, 06 Nov 2014 19:00:57 GMT

Its Working 🙂 Thank You So much...
I replaced "appendcols" with "join type=left Alert_Type". And added " | fillnull value=0" in the end of my query to display zero.