How to accelerate a report and use fillnull or usenull with stats?

[yacht_rock]

Splunk 6.2.2 ... I want to build an accelerated daily report. The search I want to power this daily report is...

index=myapp | iplocation dip | fillnull value=- | stats sum(eval(c2s_bytes+s2c_bytes)) AS "total_bytes" count by app, sip, sip_host, dip, Country

sip_host is a populated by an automatic lookup that links "sip" (source IP) to a host name. Country is populated by the iplocation lookup provided by Splunk.

Sometimes one or both of these fields will be blank, so by default, I need a way for stats to do its thing even when a field is blank/null. I've traced the reason to why Splunk says I can't accelerate this report to the fillnull command. Googling for stats info says there is a usenull flag for stats, but I couldn't find it in the documentation or get it to work.

How can I make stats use null/blank fields and/or make Splunk accelerate reports that use the fillnull command?

[woodcock · Accepted solution]

Try this:

index=myapp | iplocation dip | eval sip_host=coalesce(sip_host, "-") | eval Country=coalesce(Country, "-") | stats sum(eval(c2s_bytes+s2c_bytes)) AS "total_bytes" count by app, sip, sip_host, dip, Country

[yacht_rock]

This worked perfectly - the results are identical to my query, and it's accelerating like I wanted. Thank you!