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.
@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 ,
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)
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.
My bad, somehow misplaced by clause. Updated the answer.
Thanks so much Rejith. It's working great. Accepting your answer... Thanks again!
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)