DQL Timeseries Average of multiple time shift metrics

[36Krazyfists]

So, I have a DQL Query that returns the current metrics and then the previous 7, 14, and 21 days for that metric.  I want to average those 3 previous weeks of metrics together to get a "baseline average" of what is "normal" for this metric.  (Yes, I know Dynatrace does seasonal baseline using anomaly detection, but I'm not a fan of the way they do it and would much rather prefer this method).

My problem is, I can't figure out how to average the three previous metrics into a single average value.

The end result I'm looking for is a chart that has just two lines on it.  The current metrics value (which I've labelled "curLogins") and a line that is the average of the three timeshifted metrics (I've labelled each of those with "last#Logins".

Here is the DQL that returns the current login metric value and the three time shifted values for that same metric:

timeseries curLogins = avg(custom.db.query, rate: 1m), by: { query_name }, interval:1m
| filter query_name == "Login Count"
| append [
timeseries last7Logins = avg(custom.db.query, rate: 1m), by: { query_name }, interval:1m, shift:-7d
| filter query_name == "Login Count"
]
| append [
timeseries last14Logins = avg(custom.db.query, rate: 1m), by: { query_name }, interval:1m, shift:-14d
| filter query_name == "Login Count"
]
| append [
timeseries last21Logins = avg(custom.db.query, rate: 1m), by: { query_name }, interval:1m, shift:-21d
| filter query_name == "Login Count"
]

Easy enough and this results in a chart that looks like this:

So, now I just need to get those 3 "last#Logins" metric values averaged together into a single "baselineLogins" line.  If this was SQL, I'd have no problem, but me and DQL are just not jiving, and I have a hard time with anything other than the simplest of queries.

I thought I could just add this to the end of it:

| fieldsRemove query_name
| fieldsAdd baselineAvg = (last7Logins[] + last14Logins[] + last21Logins[])/3
| fieldsRemove last7Logins, last14Logins, last21Logins

But for some reason that results in a chart that has only a single line and a legend that has curLogins listed 4 times...:

Can somebody please educate me on what I'm doing wrong here?  It seems like it should be pretty simple.  Add the 3 values together and divide by 3 to get their average.  But that doesn't appear to work here...

[36Krazyfists · Accepted solution]

For anyone else wanting to do something similar, the issue was the append.  I should have been using join.  This is what I ended up with and it works perfectly:

timeseries curLogins = avg(custom.db.query), by: { query_name }, interval:1m
| filter query_name == "Login Count"
| join [timeseries prev7Days = avg(custom.db.query), by: { query_name }, interval:1m, shift: -7d
| filter query_name == "Login Count"], on: {query_name}, fields: {prev7Days}
| join [timeseries prev14Days = avg(custom.db.query), by: { query_name }, interval:1m, shift: -14d
| filter query_name == "Login Count"], on: {query_name}, fields: {prev14Days}
| join [timeseries prev21Days = avg(custom.db.query), by: { query_name }, interval:1m, shift: -21d
| filter query_name == "Login Count"], on: {query_name}, fields: {prev21Days}
| fieldsAdd baseline = (prev7Days[] + prev14Days[] + prev21Days[])/3
| fieldsRemove prev7Days, prev14Days, prev21Days

This produces a chart that shows the current metric value and a baseline average for that metric that is based on the same time frame for each of the last 3 weeks (so for each minute you can see what the average was for that same minute on that same day for the last 3 weeks). 

I really wish the Dynatrace seasonal baseline thresholds worked more like this instead of just looking at the last 14 days of data.  Comparing 1pm on a Monday to the average of 1pm on Sunday, Saturday, Friday, Thursday, etc... is not super helpful for most businesses.  I want to compare 1pm on a Monday to last Monday at 1pm and the previous Monday at 1pm and so on.  That's how seasonal baseline should work.  Or it should at least be an option for it to work that way. 

Until then, I have this query.

[krzysztof_hoja]

Just for reference: it is possible to make this query more generic so it is possible to easily configure it to look more into the past without need to add additional joins.

I will use single host's CPU usage in my example. First query just takes CPU usage for a host for past week. In subquery I get data for whole period of previous 3 weeks (from -4*7d until -7d). Then I expand it, so each row represents individual timestamp (new function start() in timeseries command can produce timestamp corresponding to each element of array of timeseries) and calculate offset field represents time since beginning of the big timeframe. Final step is calculation of averages but grouping by offset modulo a week (so all e.g. Mondays 1:00pm get into same bucket). This time I need to map historical data into last week manually: in my query the reference time ends when current time starts:

timeseries cpu=avg(dt.host.cpu.system), interval:10m, by: {dt.entity.host}, filter:dt.entity.host == "HOST-1305211FB24AEE24" , from: -7d
| append [

    timeseries {cpu_ref=avg(dt.host.cpu.system), timestamp=start()}, interval:30m, by: {dt.entity.host}, filter:dt.entity.host == "HOST-1305211FB24AEE24" , from: -4*7d, to:-7d
    | fields cpu_ref=record(timestamp=timestamp[], cpu_ref=cpu_ref[]), interval, timeframe, dt.entity.host
    | expand cpu_ref
    | fieldsAdd offset=cpu_ref[timestamp]-timeframe[start]
    | summarize { cpu_ref=avg(cpu_ref[cpu_ref]) }, by: {dt.entity.host, timestamp= timeframe[end]+offset % 7d , interval}
    | makeTimeseries cpu_ref=avg(cpu_ref), by:{dt.entity.host}, interval:30m, from:-7d

]

To extend baseline calculation from: parameter needs to be just adjusted.

Result looks like this:

Please not that intervals for both queries is different: basic one has 10minutes granular intervals while the reference timeseries is with 30min granularity which also introduces additional smoothing 

Of course you comment seasonal is still valid.