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

Commit689d1c9

Browse files
committed
feat(coderd/database): rewriteGetTemplateAppInsightsByTemplate to usetemplate_usage_stats
1 parentc45caeb commit689d1c9

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
@@ -3086,7 +3086,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
30863086
for_,usageKey:=rangeusageKeys {
30873087
r:= database.GetTemplateAppInsightsByTemplateRow{
30883088
TemplateID:usageKey.TemplateID,
3089-
DisplayName:sql.NullString{String:usageKey.DisplayName,Valid:true},
3089+
DisplayName:usageKey.DisplayName,
30903090
SlugOrPort:usageKey.Slug,
30913091
}
30923092
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
@@ -271,51 +271,54 @@ GROUP BY
271271
t.template_ids,ai.app_name,ai.display_name,ai.icon,ai.is_app;
272272

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

320323
-- name: GetTemplateInsightsByInterval :many
321324
-- 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