Timeseries Rate display

[Kaayush]

data record(timestamp = now(), duration = 60000, event = "A"),
     record(timestamp = now()-10m, duration = 30000, event = "B"),
     record(timestamp = now()-20m, duration = 30000, event = "C"),
     record(timestamp = now()-30m, duration = 45000, event = "A"),
     record(timestamp = now()-40m, duration = 15000, event = "B"),
     record(timestamp = now()-50m, duration = 10000, event = "C")
| summarize by: { event }, { Duration = toLong(sum(toDouble(duration)/1000)) }
| summarize Aduration = toLong(sum(if(event == "A", Duration, else:0))),
  Bduration = toLong(sum(if(event == "B", Duration, else:0))),
  Cduration = toLong(sum(if(event == "C", Duration, else:0)))
| fieldsAdd Brate = toDouble(Bduration) / Aduration * 100, Crate = toDouble(Cduration) / Bduration * 100

How can I get the Rate displayed on line chart via makeTimeseries, interval: 10m?

[JeanBlanc]

Hi,

I'm not sure I understand what you're trying to do.

If you want a time series, you need a timestamp for each record.

When you summarize your data records, you lose that kind of information.

Here is an example :

data record(timestamp = now(), duration = 60000, event = "A"),
     record(timestamp = now()-10m, duration = 30000, event = "B"),
     record(timestamp = now()-20m, duration = 30000, event = "C"),
     record(timestamp = now()-30m, duration = 45000, event = "A"),
     record(timestamp = now()-40m, duration = 15000, event = "B"),
     record(timestamp = now()-50m, duration = 10000, event = "C")
| makeTimeseries avg(duration), interval: 10m, time: timestamp

Regards,

[Kaayush]

I have updated the query with timestamp column. 
As per this example, I will get average of duration column: 

makeTimeseries avg(duration), interval: 10m, time: timestamp

My question is for rate timeseries graph which is calculated as `(sum Of duration of event B / sum of duration of event A) * 100`.  

Sample graph for the same in NewRelic:

[sinisa_zubic]

hi @Kaayush , I still can't understand what you want to achieve.

So I have additional questions

• Why are you mentioning then the other event (event C) in your example if it is not used for the calculation
• regarding the calculation "sum Of duration of event B / sum of duration of event A) * 100", what is the timeframe for "sum of duration", is it 10min? or maybe more? if it is 10min then the calculation won't work with the provided data sample

[krzysztof_hoja · Accepted solution]

When you add timestamp like this, the rates you want to calculate over time will be "null", because only one event of one type exist in each 10 minute intervals. To calculate rate using formula you provided you need different kinds of event in same interval. I prepared sample data fulfilling this condition using you example as a base:

data 
     record(timestamp = now(), duration = 60000, event = "A"),
     record(timestamp = now(), duration = 30000, event = "B"),
     record(timestamp = now(), duration = 30000, event = "C"),
     record(timestamp = now(), duration = 45000, event = "A"),
     record(timestamp = now(), duration = 15000, event = "B"),
     record(timestamp = now(), duration = 10000, event = "C"),

     record(timestamp = now()-10m, duration = 60000, event = "A"),
     record(timestamp = now()-10m, duration = 30000, event = "B"),
     record(timestamp = now()-10m, duration = 30000, event = "C"),
     record(timestamp = now()-10m, duration = 45000, event = "A"),
     record(timestamp = now()-10m, duration = 15000, event = "B"),
     record(timestamp = now()-10m, duration = 10000, event = "C"),

     record(timestamp = now()-20m, duration = 60000, event = "A"),
     record(timestamp = now()-20m, duration = 30000, event = "B"),
     record(timestamp = now()-20m, duration = 30000, event = "C"),
     record(timestamp = now()-20m, duration = 45000, event = "A"),
     record(timestamp = now()-20m, duration = 15000, event = "B"),
     record(timestamp = now()-20m, duration = 10000, event = "C")

| makeTimeseries duration=sum(duration), by: {event}, interval: 10m
| summarize {
  Brate=takeAny(if(event=="B", duration))[]*100/takeAny(if(event=="A", duration))[],
  Crate=takeAny(if(event=="C", duration))[]*100/takeAny(if(event=="B", duration))[]
}, by: {interval, timeframe}

In this case we can successfully use makeTimeseries and split by event type. To execute some math in controlled way way between records identified by specific value of a filed you can use takeAny in combination with if like in example above.

Also, please not iterative expression notation ( [] ) in formula as we are calculation "rate" for timeseries, not a single value.

Kris

[Kaayush]

Thanks for the solution