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.
I am new to splunk search and any suggestions would be really appreciated.
Thanks
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
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
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?
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.
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.