Splunk Search

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

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

1 Solution

renjith_nair
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)
Happy Splunking!

View solution in original post

renjith_nair
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)
Happy Splunking!

pratik0807ray
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

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

pratik0807ray
Explorer

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

0 Karma

woodcock
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...