How to get percentage of null vs not-null values grouped by each day?

[pratik0807ray]

Hi,

I've written a query to get percentage of null vs not-null values of a particular field (i.e. billValue). However, it gives me a complete result for the entire month or week (depending on the time-period selected). How do I get the result grouped by each day (or each hour etc). I tried a lot of different things from Google like 'eventstats', 'bin time span=1d', 'bucket _time span=day, 'timechart'' etc., but the problem is that none of them work when I use by _time after count. I believe it may be due to the use of _count(eval(isnull(fieldName))) in my query, but not sure. Please see below query which correctly gives me the percentage for complete month (whereas I need day by day):

[Base Query] | extract pairdelim="{,}" kvdelim=":"| table billValue| stats count(eval(isnotnull(billValue))) AS notNullBill, count(eval(isnull(billValue))) AS nullBill| eval Percentage=Round(((nullBill/notNullBill)*100),2) | table Percentage

I'm new to Splunk. Any help is greatly appreciated.

Thanks in advance.

[renjith_nair · Accepted solution]

@pratik0807ray ,

You are filtering the fields by using table billValue and hence _time is not available for further processing.

Give this a try

[Base Query] | extract pairdelim="{,}" kvdelim=":"|bucket span=1d _time|stats count(eval(isnotnull(billValue))) AS notNullBill, count(eval(isnull(billValue))) AS nullBill by _time| eval Percentage=Round(((nullBill/notNullBill)*100),2)

[pratik0807ray]

Hi Renjith,

Thanks for the help, but 'eval Percentage=Round(((nullBill/notNullBill)*100),2) by _time' is giving me below error:

Error in 'eval' command: The expression is malformed.

If I use only 'eval Percentage=Round(((nullBill/notNullBill)*100),2)', there's no error, but as expected it's not grouped by days.

Feel like there's some minor syntax issue here. Any further suggestion you can give me?

Thanks in advance.

[renjith_nair]

My bad, somehow misplaced by clause. Updated the answer.

[pratik0807ray]

Thanks so much Rejith. It's working great. Accepting your answer... Thanks again!

[woodcock]

This would be more efficient like this:

[Base Query]
| extract pairdelim="{,}" kvdelim=":"
| timechart span=1d count(billValue) AS notNullBill, count AS total
| eval nullBill = total - notNullBill
| eval Percentage=round(((nullBill / notNullBill) * 100), 2)