Skip to content

feat(coderd/database): rewrite GetTemplateAppInsightsByTemplate to use template_usage_stats #12670

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
wants to merge 19 commits into from
Closed

feat(coderd/database): rewrite GetTemplateAppInsightsByTemplate to use template_usage_stats #12670

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
19 commits
Select commit Hold shift + click to select a range
9052117
feat(coderd/database): add `template_usage_stats` table and rollup query
mafredri Mar 18, 2024
c9bd6af
test(coderd): fix insights tests
mafredri Mar 19, 2024
9037b25
feat(coderd/database): rewrite `GetTemplateInsights` to use `template…
mafredri Mar 18, 2024
e626ab0
fix: implement `UpsertTemplateUsageStats` in dbmem
mafredri Mar 20, 2024
4da99d8
fix: implement `GetTemplateInsights` in dbmem
mafredri Mar 20, 2024
3651cc9
feat(coderd/database): rewrite `GetTemplateInsightsByInterval` to use…
mafredri Mar 18, 2024
af55118
fix: implement `GetTemplateInsightsByInterval` in dbmem
mafredri Mar 20, 2024
e5da393
feat(coderd/database): rewrite `GetTemplateInsightsByTemplate` to use…
mafredri Mar 18, 2024
a234c5d
fix: implement `GetTemplateInsightsByTemplate` in dbmem
mafredri Mar 20, 2024
23f382c
feat(coderd/database): rewrite `GetTemplateAppInsights` to use `templ…
mafredri Mar 18, 2024
803de96
fix gen
mafredri Mar 20, 2024
fcaaff8
simplify query
mafredri Mar 20, 2024
ce9890e
fix: implement `GetTemplateAppInsights` in dbmem
mafredri Mar 20, 2024
385e019
fix dbauthz
mafredri Mar 20, 2024
b11b3b6
update join comment
mafredri Mar 20, 2024
460c244
update join comment
mafredri Mar 20, 2024
af3fc3e
feat(coderd/database): rewrite `GetTemplateAppInsightsByTemplate` to …
mafredri Mar 18, 2024
310f545
add fixture
mafredri Mar 20, 2024
7145189
feat(coderd/database): add `dbrollup` service to rollup insights
mafredri Mar 18, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
Next Next commit
feat(coderd/database): rewrite GetTemplateInsightsByTemplate to use… 
… `template_usage_stats`
  • Loading branch information
@mafredri
mafredri committed Mar 18, 2024
commit e5da3931d91c103fa6fb928f507d7eecb9a35a6e
47 changes: 20 additions & 27 deletions coderd/database/queries.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

35 changes: 12 additions & 23 deletions coderd/database/queries/insights.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -162,31 +162,20 @@ FROM
insights;

-- name: GetTemplateInsightsByTemplate :many
WITH agent_stats_by_interval_and_user AS (
SELECT
date_trunc('minute', was.created_at) AS created_at_trunc,
was.template_id,
was.user_id,
CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
FROM workspace_agent_stats was
WHERE
was.created_at >= @start_time::timestamptz
AND was.created_at < @end_time::timestamptz
AND was.connection_count > 0
GROUP BY created_at_trunc, was.template_id, was.user_id
)

SELECT
template_id,
COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
COALESCE(SUM(usage_vscode_seconds), 0)::bigint AS usage_vscode_seconds,
COALESCE(SUM(usage_jetbrains_seconds), 0)::bigint AS usage_jetbrains_seconds,
COALESCE(SUM(usage_reconnecting_pty_seconds), 0)::bigint AS usage_reconnecting_pty_seconds,
COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
FROM agent_stats_by_interval_and_user
COUNT(DISTINCT user_id) AS active_users,
(SUM(usage_mins) * 60)::bigint AS usage_total_seconds, -- Includes app usage.
(SUM(ssh_mins) * 60)::bigint AS usage_ssh_seconds,
(SUM(sftp_mins) * 60)::bigint AS usage_sftp_seconds,
(SUM(reconnecting_pty_mins) * 60)::bigint AS usage_reconnecting_pty_seconds,
(SUM(vscode_mins) * 60)::bigint AS usage_vscode_seconds,
(SUM(jetbrains_mins) * 60)::bigint AS usage_jetbrains_seconds
FROM
template_usage_stats
WHERE
start_time >= @start_time::timestamptz
AND end_time <= @end_time::timestamptz
GROUP BY template_id;

-- name: GetTemplateAppInsights :many
Expand Down