calculating ratio of fields, grouping and plotting over time - cannot handle 'no results' searches

[sirsyedian]

Hi All,
We are using splunk to periodically index (every 5 mins) some CSV files containing the following type of data.

Time, Trunk, Event_Code, Total
timestamp, Trunk1, 0, 2
timestamp,Trunk2,100, 30
timestamp,Trunk3, 0, 3
timestamp,Trunk1, 1, 3
timestamp,Trunk2, 0, 4
timestamp,Trunk3, 50, 5

I want to calculate the following Ratio using this data and plot it over time for each Trunk.

For each Trunk and within each 5 min time bucket:
RATIO = [Sum (Total) for events with EventCode of zero] / [Sum(Total) for all events for that trunk]

I have tried search like following

sourcetype=test-csv |bucket span=5mins _time | stats sum(Total) as Total_Events by _time, Trunk| appendcols [search sourcetype=test-csv Event_Code=0| bucket span=5mins _time | stats sum(Total) as Total_EC0 by _time,Trunk] |eval RATIO = Total_EC0/Total_Events*100 | timechart span=5m values(RATIO) by Trunk

It works fine as long as there are some Events with Event_Code of zero for each trunk, However, i get into issues when there are NO events with Eventcode=0 within a 5min timebucket (for any trunk). This results in the subsearch to give 'no result' and hense i dont get any results.

• Is there a way to force Total_EC0 to have ZERO value for each time bucket and for each Trunk?
• Am i following the right approach or is there a simpler way of accomplishing this task.?

I am new to splunk search and any suggestions would be really appreciated.

Thanks

[cmerriman · Accepted solution]

since you're using the same sourcetype, you could probably avoid using appendcols.

sourcetype=test-csv |bucket span=5mins _time |eval Total_EC0=if(Event_Code=0,Total,0)| stats sum(Total) as Total_Events sum(Total_EC0) as Total_EC0 by _time, Trunk|eval RATIO = Total_EC0/Total_Events*100 | timechart span=5m values(RATIO) by Trunk

see where that gets you. you could also add in a |makecontinuous|fillnull value=0 in there to see if that helps

[sirsyedian]

Thanks a lot cmerriman, A combination of both (updated search query, and |makecontinuous|fillnull value=0) did the trick. I can now see zero values and the timechart looks nice and continuous 🙂

For scenarios where I now get RATIO = 0/0 (ie where both Total_Events and Total_ECO are zero/null), I would like to update/show the RATIO to be equal to 100. Any idea how to accomplish it within the same query?

[cmerriman]

you could change your eval to |eval RATIO = if(Total_EC0=0 OR Total_Events=0,100,Total_EC0/Total_Events*100)
and see if that works.

[sirsyedian]

I think i was just overcomplicating it.
Using fillnull value=100 instead of fillnull value=0 would ensure all null/null RATIO are reported at 100.

Really appreciate your help. Thanks.