Get ratios with time shift in Google Cloud Monitoring

Question

I'm running a query which compares data to that from 7 days previously. The query only shows if there is a difference. It doesn't reflect if the difference is an increase or a decrease.

What I'm looking for is a centre 0 line with a line graph showing if the current data is an increase or a decrease on last week's data, (ideally) as a percentage.

fetch generic_node
| metric 'custom.googleapis.com/myCustomMetric'
| group_by 4h, [row_count: row_count()]
| {value [v_now: val()] ; time_shift 1w}
| join | div

user_11928136 · Answer

What about:

fetch generic_node
| metric 'custom.googleapis.com/myCustomMetric'
| group_by [], [row_count: row_count()]
| {value [v_now: val()] ; time_shift 1w}
| join | div

Or

fetch generic_node
| metric 'custom.googleapis.com/myCustomMetric'
| group_by [], mean(val())
| {
    ident
  ;
    time_shift 1w
  }
| join | div

Docs:

user_16031240 · Answer

Simply change the last table operation to sub:

fetch generic_node
| metric 'custom.googleapis.com/myCustomMetric'
| group_by 4h, [row_count: row_count()]
| {value [v_now: val()] ; time_shift 1w}
| join | sub

Note that after the join table operation, you get a time series table with two value columns - the first column for current data and the second for last week's data. A following arithmetic function(textual name for an arithmetic operator) acts as a table operation shortcut for generating a single value column by performing that arithmetic function on the two value columns.

div here means

| value div(val(0), val(1))

That's why you get a ratio. And sub means

| value sub(val(0), val(1))

where you can get the difference.

https://cloud.google.com/monitoring/mql/reference?hl=en#div-func https://cloud.google.com/monitoring/mql/reference?hl=en#sub-func

Get ratios with time shift in Google Cloud Monitoring

2 Answers