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

Commit884c4de

Browse files
committed
feat(coderd/database): rewriteGetTemplateAppInsightsByTemplate to usetemplate_usage_stats
1 parent8a9eb15 commit884c4de

File tree

4 files changed

+101
-95
lines changed

4 files changed

+101
-95
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3275,7 +3275,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
32753275
for_,usageKey:=rangeusageKeys {
32763276
r:= database.GetTemplateAppInsightsByTemplateRow{
32773277
TemplateID:usageKey.TemplateID,
3278-
DisplayName:sql.NullString{String:usageKey.DisplayName,Valid:true},
3278+
DisplayName:usageKey.DisplayName,
32793279
SlugOrPort:usageKey.Slug,
32803280
}
32813281
for_,mUserUsage:=rangeusageByTemplateAppUser[usageKey] {

‎coderd/database/queries.sql.go

Lines changed: 53 additions & 50 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: 46 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -273,51 +273,54 @@ GROUP BY
273273
t.template_ids,ai.app_name,ai.display_name,ai.icon,ai.is_app;
274274

275275
-- name: GetTemplateAppInsightsByTemplate :many
276-
WITH app_stats_by_user_and_agentAS (
276+
SELECT
277+
tus.template_id,
278+
COUNT(DISTINCTtus.user_id)AS active_users,
279+
app_usage.key::textAS slug_or_port,
280+
COALESCE(wa.display_name,'')AS display_name,
281+
(SUM(app_usage.value::int)*60)::bigintAS usage_seconds
282+
FROM
283+
template_usage_statsAS tus, jsonb_each(app_usage_mins)AS app_usage
284+
LEFT JOIN LATERAL (
285+
-- Fetch the latest app info for each app based on slug and template.
277286
SELECT
278-
s.start_time,
279-
60as seconds,
280-
w.template_id,
281-
was.user_id,
282-
was.agent_id,
283-
was.slug_or_port,
284-
wa.display_name,
285-
(wa.slugIS NOT NULL)::booleanAS is_app
286-
FROM workspace_app_stats was
287-
JOIN workspaces wON (
288-
w.id=was.workspace_id
289-
)
290-
-- We do a left join here because we want to include user IDs that have used
291-
-- e.g. ports when counting active users.
292-
LEFT JOIN workspace_apps waON (
293-
wa.agent_id=was.agent_id
294-
ANDwa.slug=was.slug_or_port
295-
)
296-
-- This table contains both 1 minute entries and >1 minute entries,
297-
-- to calculate this with our uniqueness constraints, we generate series
298-
-- for the longer intervals.
299-
CROSS JOIN LATERAL generate_series(
300-
date_trunc('minute',was.session_started_at),
301-
-- Subtract 1 microsecond to avoid creating an extra series.
302-
date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),
303-
'1 minute'::interval
304-
) s(start_time)
287+
app.display_name,
288+
app.slug
289+
FROM
290+
workspace_appsAS app
291+
JOIN
292+
workspace_agentsAS agent
293+
ON
294+
agent.id=app.agent_id
295+
JOIN
296+
workspace_resourcesAS resource
297+
ON
298+
resource.id=agent.resource_id
299+
JOIN
300+
workspace_buildsAS build
301+
ON
302+
build.job_id=resource.job_id
303+
JOIN
304+
workspacesAS workspace
305+
ON
306+
workspace.id=build.workspace_id
305307
WHERE
306-
s.start_time>= @start_time::timestamptz
307-
-- Subtract one minute because the series only contains the start time.
308-
ANDs.start_time< (@end_time::timestamptz)-'1 minute'::interval
309-
GROUP BYs.start_time,w.template_id,was.user_id,was.agent_id,was.slug_or_port,wa.display_name,wa.slug
310-
)
311-
312-
SELECT
313-
template_id,
314-
display_name,
315-
slug_or_port,
316-
COALESCE(COUNT(DISTINCT user_id))::bigintAS active_users,
317-
SUM(seconds)AS usage_seconds
318-
FROM app_stats_by_user_and_agent
319-
WHERE is_app IS TRUE
320-
GROUP BY template_id, display_name, slug_or_port;
308+
-- Requires lateral join.
309+
app.slug=app_usage.key
310+
ANDworkspace.owner_id=tus.user_id
311+
ANDworkspace.template_id=tus.template_id
312+
ORDER BY
313+
app.created_atDESC
314+
LIMIT1
315+
) wa
316+
ON
317+
true
318+
WHERE
319+
tus.start_time>= @start_time::timestamptz
320+
ANDtus.end_time<= @end_time::timestamptz
321+
ANDwa.slugIS NOT NULL-- Check is_app.
322+
GROUP BY
323+
tus.template_id,app_usage.key::text,wa.display_name;
321324

322325
-- name: GetTemplateInsightsByInterval :many
323326
-- GetTemplateInsightsByInterval returns all intervals between start and end

‎coderd/prometheusmetrics/insights/metricscollector.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -194,7 +194,7 @@ func (mc *MetricsCollector) Collect(metricsCh chan<- prometheus.Metric) {
194194
// Custom apps
195195
for_,appRow:=rangedata.apps {
196196
metricsCh<-prometheus.MustNewConstMetric(applicationsUsageSecondsDesc,prometheus.GaugeValue,float64(appRow.UsageSeconds),data.templateNames[appRow.TemplateID],
197-
appRow.DisplayName.String,appRow.SlugOrPort)
197+
appRow.DisplayName,appRow.SlugOrPort)
198198
}
199199

200200
// Built-in apps

0 commit comments

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