Need to display 0 if the count is 0

[harish_ka]

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..

[somesoni2 · Accepted solution]

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

[harish_ka]

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.

[harish_ka]

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

[harish_ka]

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 ]

[somesoni2]

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