Skip to content

Commit 0a55081

Browse files
mtojekmtojek
authored
feat: expose app insights as Prometheus metrics (coder#10346)
1 parent 8441c36 commit 0a55081

File tree

12 files changed

+421
-20
lines changed

12 files changed

+421
-20
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1265,6 +1265,13 @@ func (q *querier) GetTemplateAppInsights(ctx context.Context, arg database.GetTe
12651265
return q.db.GetTemplateAppInsights(ctx, arg)
12661266
}
12671267

1268+
func (q *querier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg database.GetTemplateAppInsightsByTemplateParams) ([]database.GetTemplateAppInsightsByTemplateRow, error) {
1269+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
1270+
return nil, err
1271+
}
1272+
return q.db.GetTemplateAppInsightsByTemplate(ctx, arg)
1273+
}
1274+
12681275
// Only used by metrics cache.
12691276
func (q *querier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
12701277
if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

‎coderd/database/dbmem/dbmem.go

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2365,6 +2365,106 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
23652365
return rows, nil
23662366
}
23672367

2368+
func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg database.GetTemplateAppInsightsByTemplateParams) ([]database.GetTemplateAppInsightsByTemplateRow, error) {
2369+
err := validateDatabaseType(arg)
2370+
if err != nil {
2371+
return nil, err
2372+
}
2373+
2374+
q.mutex.RLock()
2375+
defer q.mutex.RUnlock()
2376+
2377+
type uniqueKey struct {
2378+
TemplateID uuid.UUID
2379+
DisplayName string
2380+
Slug string
2381+
}
2382+
2383+
// map (TemplateID + DisplayName + Slug) x time.Time x UserID x <usage>
2384+
usageByTemplateAppUser := map[uniqueKey]map[time.Time]map[uuid.UUID]int64{}
2385+
2386+
// Review agent stats in terms of usage
2387+
for _, s := range q.workspaceAppStats {
2388+
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
2389+
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
2390+
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
2391+
if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
2392+
(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
2393+
(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
2394+
continue
2395+
}
2396+
2397+
w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
2398+
if err != nil {
2399+
return nil, err
2400+
}
2401+
2402+
app, _ := q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
2403+
AgentID: s.AgentID,
2404+
Slug: s.SlugOrPort,
2405+
})
2406+
2407+
key := uniqueKey{
2408+
TemplateID: w.TemplateID,
2409+
DisplayName: app.DisplayName,
2410+
Slug: app.Slug,
2411+
}
2412+
2413+
t := s.SessionStartedAt.Truncate(time.Minute)
2414+
if t.Before(arg.StartTime) {
2415+
t = arg.StartTime
2416+
}
2417+
for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
2418+
if _, ok := usageByTemplateAppUser[key]; !ok {
2419+
usageByTemplateAppUser[key] = map[time.Time]map[uuid.UUID]int64{}
2420+
}
2421+
if _, ok := usageByTemplateAppUser[key][t]; !ok {
2422+
usageByTemplateAppUser[key][t] = map[uuid.UUID]int64{}
2423+
}
2424+
if _, ok := usageByTemplateAppUser[key][t][s.UserID]; !ok {
2425+
usageByTemplateAppUser[key][t][s.UserID] = 60 // 1 minute
2426+
}
2427+
t = t.Add(1 * time.Minute)
2428+
}
2429+
}
2430+
2431+
// Sort usage data
2432+
usageKeys := make([]uniqueKey, len(usageByTemplateAppUser))
2433+
var i int
2434+
for key := range usageByTemplateAppUser {
2435+
usageKeys[i] = key
2436+
i++
2437+
}
2438+
2439+
slices.SortFunc(usageKeys, func(a, b uniqueKey) int {
2440+
if a.TemplateID != b.TemplateID {
2441+
return slice.Ascending(a.TemplateID.String(), b.TemplateID.String())
2442+
}
2443+
if a.DisplayName != b.DisplayName {
2444+
return slice.Ascending(a.DisplayName, b.DisplayName)
2445+
}
2446+
return slice.Ascending(a.Slug, b.Slug)
2447+
})
2448+
2449+
// Build result
2450+
var result []database.GetTemplateAppInsightsByTemplateRow
2451+
for _, usageKey := range usageKeys {
2452+
r := database.GetTemplateAppInsightsByTemplateRow{
2453+
TemplateID: usageKey.TemplateID,
2454+
DisplayName: sql.NullString{String: usageKey.DisplayName, Valid: true},
2455+
SlugOrPort: usageKey.Slug,
2456+
}
2457+
for _, mUserUsage := range usageByTemplateAppUser[usageKey] {
2458+
r.ActiveUsers += int64(len(mUserUsage))
2459+
for _, usage := range mUserUsage {
2460+
r.UsageSeconds += usage
2461+
}
2462+
}
2463+
result = append(result, r)
2464+
}
2465+
return result, nil
2466+
}
2467+
23682468
func (q *FakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
23692469
if err := validateDatabaseType(arg); err != nil {
23702470
return database.GetTemplateAverageBuildTimeRow{}, err

‎coderd/database/dbmetrics/dbmetrics.go

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 90 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,53 @@ SELECT
218218
FROM app_stats_by_user_and_agent
219219
GROUP BY access_method, slug_or_port, display_name, icon, is_app;
220220

221+
-- name: GetTemplateAppInsightsByTemplate :many
222+
WITH app_stats_by_user_and_agent AS (
223+
SELECT
224+
s.start_time,
225+
60 as seconds,
226+
w.template_id,
227+
was.user_id,
228+
was.agent_id,
229+
was.slug_or_port,
230+
wa.display_name,
231+
(wa.slug IS NOT NULL)::boolean AS is_app
232+
FROM workspace_app_stats was
233+
JOIN workspaces w ON (
234+
w.id = was.workspace_id
235+
)
236+
-- We do a left join here because we want to include user IDs that have used
237+
-- e.g. ports when counting active users.
238+
LEFT JOIN workspace_apps wa ON (
239+
wa.agent_id = was.agent_id
240+
AND wa.slug = was.slug_or_port
241+
)
242+
-- This table contains both 1 minute entries and >1 minute entries,
243+
-- to calculate this with our uniqueness constraints, we generate series
244+
-- for the longer intervals.
245+
CROSS JOIN LATERAL generate_series(
246+
date_trunc('minute', was.session_started_at),
247+
-- Subtract 1 microsecond to avoid creating an extra series.
248+
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
249+
'1 minute'::interval
250+
) s(start_time)
251+
WHERE
252+
s.start_time >= @start_time::timestamptz
253+
-- Subtract one minute because the series only contains the start time.
254+
AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
255+
GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.slug_or_port, wa.display_name, wa.slug
256+
)
257+
258+
SELECT
259+
template_id,
260+
display_name,
261+
slug_or_port,
262+
COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
263+
SUM(seconds) AS usage_seconds
264+
FROM app_stats_by_user_and_agent
265+
WHERE is_app IS TRUE
266+
GROUP BY template_id, display_name, slug_or_port;
267+
221268
-- name: GetTemplateInsightsByInterval :many
222269
-- GetTemplateInsightsByInterval returns all intervals between start and end
223270
-- time, if end time is a partial interval, it will be included in the results and

‎coderd/insights.go

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -452,15 +452,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
452452
{
453453
TemplateIDs: usage.TemplateIDs,
454454
Type: codersdk.TemplateAppsTypeBuiltin,
455-
DisplayName: "Visual Studio Code",
455+
DisplayName: codersdk.TemplateBuiltinAppDisplayNameVSCode,
456456
Slug: "vscode",
457457
Icon: "/icon/code.svg",
458458
Seconds: usage.UsageVscodeSeconds,
459459
},
460460
{
461461
TemplateIDs: usage.TemplateIDs,
462462
Type: codersdk.TemplateAppsTypeBuiltin,
463-
DisplayName: "JetBrains",
463+
DisplayName: codersdk.TemplateBuiltinAppDisplayNameJetBrains,
464464
Slug: "jetbrains",
465465
Icon: "/icon/intellij.svg",
466466
Seconds: usage.UsageJetbrainsSeconds,
@@ -474,15 +474,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
474474
{
475475
TemplateIDs: usage.TemplateIDs,
476476
Type: codersdk.TemplateAppsTypeBuiltin,
477-
DisplayName: "Web Terminal",
477+
DisplayName: codersdk.TemplateBuiltinAppDisplayNameWebTerminal,
478478
Slug: "reconnecting-pty",
479479
Icon: "/icon/terminal.svg",
480480
Seconds: usage.UsageReconnectingPtySeconds,
481481
},
482482
{
483483
TemplateIDs: usage.TemplateIDs,
484484
Type: codersdk.TemplateAppsTypeBuiltin,
485-
DisplayName: "SSH",
485+
DisplayName: codersdk.TemplateBuiltinAppDisplayNameSSH,
486486
Slug: "ssh",
487487
Icon: "/icon/terminal.svg",
488488
Seconds: usage.UsageSshSeconds,

0 commit comments

Comments
 (0)