https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485579#M135853 <P>Hi,</P> <P>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 <EM>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)))</EM> 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):</P> <P>[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</P> <P>I'm new to Splunk. Any help is greatly appreciated.</P> <P>Thanks in advance.</P> Fri, 15 Nov 2019 08:59:29 GMT pratik0807ray 2019-11-15T08:59:29Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485579#M135853 <P>Hi,</P> <P>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 <EM>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)))</EM> 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):</P> <P>[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</P> <P>I'm new to Splunk. Any help is greatly appreciated.</P> <P>Thanks in advance.</P> Fri, 15 Nov 2019 08:59:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485579#M135853 pratik0807ray 2019-11-15T08:59:29Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485580#M135854 <P>@pratik0807ray ,</P> <P>You are filtering the fields by using <CODE>table billValue</CODE> and hence _time is not available for further processing.</P> <P>Give this a try</P> <PRE><CODE>[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) </CODE></PRE> Fri, 15 Nov 2019 09:51:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485580#M135854 renjith_nair 2019-11-15T09:51:56Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485581#M135855 <P>Hi Renjith,</P> <P>Thanks for the help, but '<EM>eval Percentage=Round(((nullBill/notNullBill)*100),2) by _time</EM>' is giving me below error:</P> <P>Error in 'eval' command: The expression is malformed.</P> <P>If I use only '<EM>eval Percentage=Round(((nullBill/notNullBill)*100),2)</EM>', there's no error, but as expected it's not grouped by days.</P> <P>Feel like there's some minor syntax issue here. Any further suggestion you can give me?</P> <P>Thanks in advance.</P> Wed, 30 Sep 2020 02:59:34 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485581#M135855 pratik0807ray 2020-09-30T02:59:34Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485582#M135856 <P>My bad, somehow misplaced by clause. Updated the answer. </P> Fri, 15 Nov 2019 11:14:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485582#M135856 renjith_nair 2019-11-15T11:14:18Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485583#M135857 <P>Thanks so much Rejith. It's working great. Accepting your answer... Thanks again! </P> Fri, 15 Nov 2019 11:17:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485583#M135857 pratik0807ray 2019-11-15T11:17:35Z https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485584#M135858 <P>This would be more efficient like this:</P> <PRE><CODE>[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) </CODE></PRE> Fri, 15 Nov 2019 16:32:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-percentage-of-null-vs-not-null-values-grouped-by-each/m-p/485584#M135858 woodcock 2019-11-15T16:32:43Z