OpenEye - Time-shifting to compare metrics over time (side by side last 7 days)

Comparing metrics over time and plotting each series on the same time interval and then understand the recent trend is important for all IT professional. The time shifting comparison is critical for any professional to find out anomalies and to understand unpredicted behavior of a system.

As OpenEye is built on PostgreSQL and using Timescaldb Extension, it is quite easy to draw such graph using Grafana. Following is one of the sample query that uses PostgreSQL LATERAL JOIN and generate_series functions from PostgreSQL and time_bucket function from Timescaldb to accomplish such useful graph.

select
	time,
	avg_sql_cpu as "Avg CPU",
	case
		when step = 0 then 'Today'
		else grp_day
	end as grp_day
from
	(
	select
		step,
		(step || 'day')::interval as interval
	from generate_series(0, 6) g(step)) g_offsets
join lateral (
	select
		time_bucket('10m',utc_time + interval)::timestamp as time,
		TO_CHAR(local_time, 'Day') as grp_day,
		AVG(sql_percent_processor_time) as avg_sql_cpu
	from tbl_sql_process
	where utc_time between ($__timeFrom()::timestamp - interval) 

                        and ($__timeTo()::timestamp - interval)
		and sql_server = '$server'
	group by 1, 2
	order by 1 ) as l on true
order by step

Time-shifted Graph in OpenEye Dashboard:

Take a look at:

• Timescaldb: https://blog.timescale.com/blog/grafana-postgres-timeshift/
• OpenEye: https://sqltouch.blogspot.com/2021/04/openeye-seeing-what-others-cannot-see.html