Splunk Search
Highlighted

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

Explorer

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.

Highlighted

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

SplunkTrust
SplunkTrust

@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)

View solution in original post

Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Esteemed Legend

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)
0 Karma