Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3e05712

Browse files
committed
feat(coderd/database): rewriteGetTemplateInsights to usetemplate_usage_stats
1 parentf2200fb commit3e05712

File tree

16 files changed

+172
-169
lines changed

16 files changed

+172
-169
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3286,8 +3286,8 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
32863286
}
32873287

32883288
result:= database.GetTemplateInsightsRow{
3289-
TemplateIDs:templateIDs,
3290-
ActiveUserIDs:activeUserIDs,
3289+
TemplateIDs:templateIDs,
3290+
ActiveUsers:int64(len(activeUserIDs)),
32913291
}
32923292
for_,intervals:=rangeappUsageIntervalsByUser {
32933293
for_,interval:=rangeintervals {

‎coderd/database/querier.go

Lines changed: 9 additions & 3 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 79 additions & 38 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 59 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -99,40 +99,67 @@ GROUP BY users.id, username, avatar_url
9999
ORDER BY user_idASC;
100100

101101
-- name: GetTemplateInsights :one
102-
-- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
103-
-- in use during a minute, we will add 5 minutes to the total usage for that
104-
-- session/app (per user).
105-
WITH agent_stats_by_interval_and_userAS (
106-
SELECT
107-
date_trunc('minute',was.created_at),
108-
was.user_id,
109-
array_agg(was.template_id)AS template_ids,
110-
CASE WHENSUM(was.session_count_vscode)>0 THEN60 ELSE0 ENDAS usage_vscode_seconds,
111-
CASE WHENSUM(was.session_count_jetbrains)>0 THEN60 ELSE0 ENDAS usage_jetbrains_seconds,
112-
CASE WHENSUM(was.session_count_reconnecting_pty)>0 THEN60 ELSE0 ENDAS usage_reconnecting_pty_seconds,
113-
CASE WHENSUM(was.session_count_ssh)>0 THEN60 ELSE0 ENDAS usage_ssh_seconds
114-
FROM workspace_agent_stats was
115-
WHERE
116-
was.created_at>= @start_time::timestamptz
117-
ANDwas.created_at< @end_time::timestamptz
118-
ANDwas.connection_count>0
119-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENwas.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
120-
GROUP BY date_trunc('minute',was.created_at),was.user_id
121-
), template_idsAS (
122-
SELECT array_agg(DISTINCT template_id)AS ids
123-
FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id
124-
WHERE template_idIS NOT NULL
125-
)
102+
-- GetTemplateInsights returns the aggregate user-produced usage of all
103+
-- workspaces in a given timeframe. The template IDs, active users, and
104+
-- usage_seconds all reflect any usage in the template, including apps.
105+
--
106+
-- When combining data from multiple templates, we must make a guess at
107+
-- how the user behaved for the 30 minute interval. In this case we make
108+
-- the assumption that if the user used two workspaces for 15 minutes,
109+
-- they did so sequentially, thus we sum the usage up to a maximum of
110+
-- 30 minutes with LEAST(SUM(n), 30).
111+
WITH
112+
insightsAS (
113+
SELECT
114+
user_id,
115+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
116+
LEAST(SUM(usage_mins),30)AS usage_mins,
117+
LEAST(SUM(ssh_mins),30)AS ssh_mins,
118+
LEAST(SUM(sftp_mins),30)AS sftp_mins,
119+
LEAST(SUM(reconnecting_pty_mins),30)AS reconnecting_pty_mins,
120+
LEAST(SUM(vscode_mins),30)AS vscode_mins,
121+
LEAST(SUM(jetbrains_mins),30)AS jetbrains_mins
122+
FROM
123+
template_usage_stats
124+
WHERE
125+
start_time>= @start_time::timestamptz
126+
AND end_time<= @end_time::timestamptz
127+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THEN template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
128+
GROUP BY
129+
start_time, user_id
130+
),
131+
templatesAS (
132+
SELECT
133+
array_agg(DISTINCT template_id)AS template_ids,
134+
array_agg(DISTINCT template_id) FILTER (WHERE ssh_mins>0)AS ssh_template_ids,
135+
array_agg(DISTINCT template_id) FILTER (WHERE sftp_mins>0)AS sftp_template_ids,
136+
array_agg(DISTINCT template_id) FILTER (WHERE reconnecting_pty_mins>0)AS reconnecting_pty_template_ids,
137+
array_agg(DISTINCT template_id) FILTER (WHERE vscode_mins>0)AS vscode_template_ids,
138+
array_agg(DISTINCT template_id) FILTER (WHERE jetbrains_mins>0)AS jetbrains_template_ids
139+
FROM
140+
template_usage_stats
141+
WHERE
142+
start_time>= @start_time::timestamptz
143+
AND end_time<= @end_time::timestamptz
144+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THEN template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
145+
)
126146

127147
SELECT
128-
COALESCE((SELECT idsFROM template_ids),'{}')::uuid[]AS template_ids,
129-
-- Return IDs so we can combine this with GetTemplateAppInsights.
130-
COALESCE(array_agg(DISTINCT user_id),'{}')::uuid[]AS active_user_ids,
131-
COALESCE(SUM(usage_vscode_seconds),0)::bigintAS usage_vscode_seconds,
132-
COALESCE(SUM(usage_jetbrains_seconds),0)::bigintAS usage_jetbrains_seconds,
133-
COALESCE(SUM(usage_reconnecting_pty_seconds),0)::bigintAS usage_reconnecting_pty_seconds,
134-
COALESCE(SUM(usage_ssh_seconds),0)::bigintAS usage_ssh_seconds
135-
FROM agent_stats_by_interval_and_user;
148+
COALESCE((SELECT template_idsFROM templates),'{}')::uuid[]AS template_ids,-- Includes app usage.
149+
COALESCE((SELECT ssh_template_idsFROM templates),'{}')::uuid[]AS ssh_template_ids,
150+
COALESCE((SELECT sftp_template_idsFROM templates),'{}')::uuid[]AS sftp_template_ids,
151+
COALESCE((SELECT reconnecting_pty_template_idsFROM templates),'{}')::uuid[]AS reconnecting_pty_template_ids,
152+
COALESCE((SELECT vscode_template_idsFROM templates),'{}')::uuid[]AS vscode_template_ids,
153+
COALESCE((SELECT jetbrains_template_idsFROM templates),'{}')::uuid[]AS jetbrains_template_ids,
154+
COALESCE(COUNT(DISTINCT user_id),0)::bigintAS active_users,-- Includes app usage.
155+
COALESCE(SUM(usage_mins)*60,0)::bigintAS usage_total_seconds,-- Includes app usage.
156+
COALESCE(SUM(ssh_mins)*60,0)::bigintAS usage_ssh_seconds,
157+
COALESCE(SUM(sftp_mins)*60,0)::bigintAS usage_sftp_seconds,
158+
COALESCE(SUM(reconnecting_pty_mins)*60,0)::bigintAS usage_reconnecting_pty_seconds,
159+
COALESCE(SUM(vscode_mins)*60,0)::bigintAS usage_vscode_seconds,
160+
COALESCE(SUM(jetbrains_mins)*60,0)::bigintAS usage_jetbrains_seconds
161+
FROM
162+
insights;
136163

137164
-- name: GetTemplateInsightsByTemplate :many
138165
WITH agent_stats_by_interval_and_userAS (

‎coderd/insights.go

Lines changed: 6 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -395,8 +395,8 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
395395
resp.Report=&codersdk.TemplateInsightsReport{
396396
StartTime:startTime,
397397
EndTime:endTime,
398-
TemplateIDs:convertTemplateInsightsTemplateIDs(usage,appUsage),
399-
ActiveUsers:convertTemplateInsightsActiveUsers(usage,appUsage),
398+
TemplateIDs:usage.TemplateIDs,
399+
ActiveUsers:usage.ActiveUsers,
400400
AppsUsage:convertTemplateInsightsApps(usage,appUsage),
401401
ParametersUsage:parametersUsage,
402402
}
@@ -416,55 +416,22 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
416416
httpapi.Write(ctx,rw,http.StatusOK,resp)
417417
}
418418

419-
funcconvertTemplateInsightsTemplateIDs(usage database.GetTemplateInsightsRow,appUsage []database.GetTemplateAppInsightsRow) []uuid.UUID {
420-
templateIDSet:=make(map[uuid.UUID]struct{})
421-
for_,id:=rangeusage.TemplateIDs {
422-
templateIDSet[id]=struct{}{}
423-
}
424-
for_,app:=rangeappUsage {
425-
for_,id:=rangeapp.TemplateIDs {
426-
templateIDSet[id]=struct{}{}
427-
}
428-
}
429-
templateIDs:=make([]uuid.UUID,0,len(templateIDSet))
430-
forid:=rangetemplateIDSet {
431-
templateIDs=append(templateIDs,id)
432-
}
433-
slices.SortFunc(templateIDs,func(a,b uuid.UUID)int {
434-
returnslice.Ascending(a.String(),b.String())
435-
})
436-
returntemplateIDs
437-
}
438-
439-
funcconvertTemplateInsightsActiveUsers(usage database.GetTemplateInsightsRow,appUsage []database.GetTemplateAppInsightsRow)int64 {
440-
activeUserIDSet:=make(map[uuid.UUID]struct{})
441-
for_,id:=rangeusage.ActiveUserIDs {
442-
activeUserIDSet[id]=struct{}{}
443-
}
444-
for_,app:=rangeappUsage {
445-
for_,id:=rangeapp.ActiveUserIDs {
446-
activeUserIDSet[id]=struct{}{}
447-
}
448-
}
449-
returnint64(len(activeUserIDSet))
450-
}
451-
452419
// convertTemplateInsightsApps builds the list of builtin apps and template apps
453420
// from the provided database rows, builtin apps are implicitly a part of all
454421
// templates.
455422
funcconvertTemplateInsightsApps(usage database.GetTemplateInsightsRow,appUsage []database.GetTemplateAppInsightsRow) []codersdk.TemplateAppUsage {
456423
// Builtin apps.
457424
apps:= []codersdk.TemplateAppUsage{
458425
{
459-
TemplateIDs:usage.TemplateIDs,
426+
TemplateIDs:usage.VscodeTemplateIds,
460427
Type:codersdk.TemplateAppsTypeBuiltin,
461428
DisplayName:codersdk.TemplateBuiltinAppDisplayNameVSCode,
462429
Slug:"vscode",
463430
Icon:"/icon/code.svg",
464431
Seconds:usage.UsageVscodeSeconds,
465432
},
466433
{
467-
TemplateIDs:usage.TemplateIDs,
434+
TemplateIDs:usage.JetbrainsTemplateIds,
468435
Type:codersdk.TemplateAppsTypeBuiltin,
469436
DisplayName:codersdk.TemplateBuiltinAppDisplayNameJetBrains,
470437
Slug:"jetbrains",
@@ -478,15 +445,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
478445
// condition finding the corresponding app entry in appUsage is:
479446
// !app.IsApp && app.AccessMethod == "terminal" && app.SlugOrPort == ""
480447
{
481-
TemplateIDs:usage.TemplateIDs,
448+
TemplateIDs:usage.ReconnectingPtyTemplateIds,
482449
Type:codersdk.TemplateAppsTypeBuiltin,
483450
DisplayName:codersdk.TemplateBuiltinAppDisplayNameWebTerminal,
484451
Slug:"reconnecting-pty",
485452
Icon:"/icon/terminal.svg",
486453
Seconds:usage.UsageReconnectingPtySeconds,
487454
},
488455
{
489-
TemplateIDs:usage.TemplateIDs,
456+
TemplateIDs:usage.SshTemplateIds,
490457
Type:codersdk.TemplateAppsTypeBuiltin,
491458
DisplayName:codersdk.TemplateBuiltinAppDisplayNameSSH,
492459
Slug:"ssh",

‎coderd/testdata/insights/template/multiple_users_and_workspaces_three_weeks_second_template.json.golden

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -18,9 +18,7 @@
1818
"seconds": 3600
1919
},
2020
{
21-
"template_ids": [
22-
"00000000-0000-0000-0000-000000000002"
23-
],
21+
"template_ids": [],
2422
"type": "builtin",
2523
"display_name": "JetBrains",
2624
"slug": "jetbrains",

‎coderd/testdata/insights/template/multiple_users_and_workspaces_three_weeks_second_template_only_report.json.golden

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -18,9 +18,7 @@
1818
"seconds": 3600
1919
},
2020
{
21-
"template_ids": [
22-
"00000000-0000-0000-0000-000000000002"
23-
],
21+
"template_ids": [],
2422
"type": "builtin",
2523
"display_name": "JetBrains",
2624
"slug": "jetbrains",

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp