Splunk Search

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

sirsyedian
New Member

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

Tags (1)
0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

0 Karma

cmerriman
Super Champion

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

0 Karma

sirsyedian
New Member

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?

0 Karma

cmerriman
Super Champion

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.

0 Karma

sirsyedian
New Member

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...