feat(coderd): support weekly aggregated insights #9684
Conversation
| @@ -90,7 +90,7 @@ ORDER BY | |||
| date ASC; | |||
|
|
|||
| -- name: DeleteOldWorkspaceAgentStats :exec | |||
| DELETE FROM workspace_agent_stats WHERE created_at < NOW() - INTERVAL '30 days'; | |||
| DELETE FROM workspace_agent_stats WHERE created_at < NOW() - INTERVAL '6 months'; | |||
There was a problem hiding this comment.
There was a problem hiding this comment.
I think it could be an acceptable new default value if we stopped writing "zero" data for agent stats, or if we deleted the "zero" data every 7 days and non-zero data every 6 months.
With "zero" data, I mean:
WHERE rx_bytes = 0
AND rx_packets = 0
AND tx_bytes = 0
AND tx_packets = 0
AND connections_by_proto = '{}'
AND connection_count = 0
AND connection_median_latency_ms = -0.001
AND session_count_vscode = 0
AND session_count_jetbrains = 0
AND session_count_reconnecting_pty = 0
AND session_count_ssh = 0These rows don't tell us anything other than the agent is alive/sending data.
There was a problem hiding this comment.
I could extend the DELETE query to remove "zero" data, but then need to list all columns unless it is fine to mention only representatives like rx_bytes and tx_bytes? Otherwise, I'm afraid that somebody will and extra column and the DELETE query will stop working at all.
| @@ -90,7 +90,7 @@ ORDER BY | |||
| date ASC; | |||
|
|
|||
| -- name: DeleteOldWorkspaceAgentStats :exec | |||
| DELETE FROM workspace_agent_stats WHERE created_at < NOW() - INTERVAL '30 days'; | |||
| DELETE FROM workspace_agent_stats WHERE created_at < NOW() - INTERVAL '6 months'; | |||
There was a problem hiding this comment.
I think it could be an acceptable new default value if we stopped writing "zero" data for agent stats, or if we deleted the "zero" data every 7 days and non-zero data every 6 months.
With "zero" data, I mean:
WHERE rx_bytes = 0
AND rx_packets = 0
AND tx_bytes = 0
AND tx_packets = 0
AND connections_by_proto = '{}'
AND connection_count = 0
AND connection_median_latency_ms = -0.001
AND session_count_vscode = 0
AND session_count_jetbrains = 0
AND session_count_reconnecting_pty = 0
AND session_count_ssh = 0These rows don't tell us anything other than the agent is alive/sending data.
Related: #9495
This PR exposes insights with
interval=weekaggregation via coderd API./insightsrequests similarly tointerval=day:http://localhost:3000/api/v2/insights/templates?start_time=2023-09-09T00%3A00%3A00%2B02%3A00&end_time=2023-09-15T16%3A00%3A00%2B02%3A00&interval=week
(clock: 2023-09-15 16:34)
start_time: 2023-09-09 00:00
end_time: 2023-09-15 16:00
TODO:
interval=weekGetTemplateDailyInsights