Skip to content

Commit 03453b1

Browse files
mafredrimafredri
authored
feat(coderd): add template app usage to insights (#9138)
Fixes #8658
1 parent 4de7de4 commit 03453b1

File tree

15 files changed

+740
-117
lines changed

15 files changed

+740
-117
lines changed

‎coderd/apidoc/docs.go

+4-2
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/apidoc/swagger.json

+2-2
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbauthz/dbauthz.go

+19
Original file line numberDiff line numberDiff line change
@@ -1173,6 +1173,25 @@ func (q *querier) GetTailnetClientsForAgent(ctx context.Context, agentID uuid.UU
11731173
return q.db.GetTailnetClientsForAgent(ctx, agentID)
11741174
}
11751175

1176+
func (q *querier) GetTemplateAppInsights(ctx context.Context, arg database.GetTemplateAppInsightsParams) ([]database.GetTemplateAppInsightsRow, error) {
1177+
for _, templateID := range arg.TemplateIDs {
1178+
template, err := q.db.GetTemplateByID(ctx, templateID)
1179+
if err != nil {
1180+
return nil, err
1181+
}
1182+
1183+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, template); err != nil {
1184+
return nil, err
1185+
}
1186+
}
1187+
if len(arg.TemplateIDs) == 0 {
1188+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
1189+
return nil, err
1190+
}
1191+
}
1192+
return q.db.GetTemplateAppInsights(ctx, arg)
1193+
}
1194+
11761195
// Only used by metrics cache.
11771196
func (q *querier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
11781197
if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

‎coderd/database/dbfake/dbfake.go

+176-14
Original file line numberDiff line numberDiff line change
@@ -549,6 +549,19 @@ func (q *FakeQuerier) getWorkspaceAgentsByResourceIDsNoLock(_ context.Context, r
549549
return workspaceAgents, nil
550550
}
551551

552+
func (q *FakeQuerier) getWorkspaceAppByAgentIDAndSlugNoLock(_ context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
553+
for _, app := range q.workspaceApps {
554+
if app.AgentID != arg.AgentID {
555+
continue
556+
}
557+
if app.Slug != arg.Slug {
558+
continue
559+
}
560+
return app, nil
561+
}
562+
return database.WorkspaceApp{}, sql.ErrNoRows
563+
}
564+
552565
func (q *FakeQuerier) getProvisionerJobByIDNoLock(_ context.Context, id uuid.UUID) (database.ProvisionerJob, error) {
553566
for _, provisionerJob := range q.provisionerJobs {
554567
if provisionerJob.ID != id {
@@ -1966,6 +1979,125 @@ func (*FakeQuerier) GetTailnetClientsForAgent(context.Context, uuid.UUID) ([]dat
19661979
return nil, ErrUnimplemented
19671980
}
19681981

1982+
func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.GetTemplateAppInsightsParams) ([]database.GetTemplateAppInsightsRow, error) {
1983+
err := validateDatabaseType(arg)
1984+
if err != nil {
1985+
return nil, err
1986+
}
1987+
1988+
q.mutex.RLock()
1989+
defer q.mutex.RUnlock()
1990+
1991+
type appKey struct {
1992+
AccessMethod string
1993+
SlugOrPort string
1994+
Slug string
1995+
DisplayName string
1996+
Icon string
1997+
}
1998+
type uniqueKey struct {
1999+
TemplateID uuid.UUID
2000+
UserID uuid.UUID
2001+
AgentID uuid.UUID
2002+
AppKey appKey
2003+
}
2004+
2005+
appUsageIntervalsByUserAgentApp := make(map[uniqueKey]map[time.Time]int64)
2006+
for _, s := range q.workspaceAppStats {
2007+
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
2008+
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
2009+
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
2010+
if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
2011+
(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
2012+
(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
2013+
continue
2014+
}
2015+
2016+
w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
2017+
if err != nil {
2018+
return nil, err
2019+
}
2020+
2021+
app, _ := q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
2022+
AgentID: s.AgentID,
2023+
Slug: s.SlugOrPort,
2024+
})
2025+
2026+
key := uniqueKey{
2027+
TemplateID: w.TemplateID,
2028+
UserID: s.UserID,
2029+
AgentID: s.AgentID,
2030+
AppKey: appKey{
2031+
AccessMethod: s.AccessMethod,
2032+
SlugOrPort: s.SlugOrPort,
2033+
Slug: app.Slug,
2034+
DisplayName: app.DisplayName,
2035+
Icon: app.Icon,
2036+
},
2037+
}
2038+
if appUsageIntervalsByUserAgentApp[key] == nil {
2039+
appUsageIntervalsByUserAgentApp[key] = make(map[time.Time]int64)
2040+
}
2041+
2042+
t := s.SessionStartedAt.Truncate(5 * time.Minute)
2043+
if t.Before(arg.StartTime) {
2044+
t = arg.StartTime
2045+
}
2046+
for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
2047+
appUsageIntervalsByUserAgentApp[key][t] = 300 // 5 minutes.
2048+
t = t.Add(5 * time.Minute)
2049+
}
2050+
}
2051+
2052+
appUsageTemplateIDs := make(map[appKey]map[uuid.UUID]struct{})
2053+
appUsageUserIDs := make(map[appKey]map[uuid.UUID]struct{})
2054+
appUsage := make(map[appKey]int64)
2055+
for uniqueKey, usage := range appUsageIntervalsByUserAgentApp {
2056+
for _, seconds := range usage {
2057+
if appUsageTemplateIDs[uniqueKey.AppKey] == nil {
2058+
appUsageTemplateIDs[uniqueKey.AppKey] = make(map[uuid.UUID]struct{})
2059+
}
2060+
appUsageTemplateIDs[uniqueKey.AppKey][uniqueKey.TemplateID] = struct{}{}
2061+
if appUsageUserIDs[uniqueKey.AppKey] == nil {
2062+
appUsageUserIDs[uniqueKey.AppKey] = make(map[uuid.UUID]struct{})
2063+
}
2064+
appUsageUserIDs[uniqueKey.AppKey][uniqueKey.UserID] = struct{}{}
2065+
appUsage[uniqueKey.AppKey] += seconds
2066+
}
2067+
}
2068+
2069+
var rows []database.GetTemplateAppInsightsRow
2070+
for appKey, usage := range appUsage {
2071+
templateIDs := make([]uuid.UUID, 0, len(appUsageTemplateIDs[appKey]))
2072+
for templateID := range appUsageTemplateIDs[appKey] {
2073+
templateIDs = append(templateIDs, templateID)
2074+
}
2075+
slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
2076+
return slice.Ascending(a.String(), b.String())
2077+
})
2078+
activeUserIDs := make([]uuid.UUID, 0, len(appUsageUserIDs[appKey]))
2079+
for userID := range appUsageUserIDs[appKey] {
2080+
activeUserIDs = append(activeUserIDs, userID)
2081+
}
2082+
slices.SortFunc(activeUserIDs, func(a, b uuid.UUID) int {
2083+
return slice.Ascending(a.String(), b.String())
2084+
})
2085+
2086+
rows = append(rows, database.GetTemplateAppInsightsRow{
2087+
TemplateIDs: templateIDs,
2088+
ActiveUserIDs: activeUserIDs,
2089+
AccessMethod: appKey.AccessMethod,
2090+
SlugOrPort: appKey.SlugOrPort,
2091+
DisplayName: sql.NullString{String: appKey.DisplayName, Valid: appKey.DisplayName != ""},
2092+
Icon: sql.NullString{String: appKey.Icon, Valid: appKey.Icon != ""},
2093+
IsApp: appKey.Slug != "",
2094+
UsageSeconds: usage,
2095+
})
2096+
}
2097+
2098+
return rows, nil
2099+
}
2100+
19692101
func (q *FakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
19702102
if err := validateDatabaseType(arg); err != nil {
19712103
return database.GetTemplateAverageBuildTimeRow{}, err
@@ -2093,12 +2225,15 @@ func (q *FakeQuerier) GetTemplateDAUs(_ context.Context, arg database.GetTemplat
20932225
return rs, nil
20942226
}
20952227

2096-
func (q *FakeQuerier) GetTemplateDailyInsights(_ context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
2228+
func (q *FakeQuerier) GetTemplateDailyInsights(ctx context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
20972229
err := validateDatabaseType(arg)
20982230
if err != nil {
20992231
return nil, err
21002232
}
21012233

2234+
q.mutex.RLock()
2235+
defer q.mutex.RUnlock()
2236+
21022237
type dailyStat struct {
21032238
startTime, endTime time.Time
21042239
userSet map[uuid.UUID]struct{}
@@ -2133,6 +2268,37 @@ func (q *FakeQuerier) GetTemplateDailyInsights(_ context.Context, arg database.G
21332268
}
21342269
}
21352270

2271+
for _, s := range q.workspaceAppStats {
2272+
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
2273+
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
2274+
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
2275+
if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
2276+
(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
2277+
(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
2278+
continue
2279+
}
2280+
2281+
for _, ds := range dailyStats {
2282+
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
2283+
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
2284+
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
2285+
if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
2286+
(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
2287+
(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
2288+
continue
2289+
}
2290+
2291+
w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
2292+
if err != nil {
2293+
return nil, err
2294+
}
2295+
2296+
ds.userSet[s.UserID] = struct{}{}
2297+
ds.templateIDSet[w.TemplateID] = struct{}{}
2298+
break
2299+
}
2300+
}
2301+
21362302
var result []database.GetTemplateDailyInsightsRow
21372303
for _, ds := range dailyStats {
21382304
templateIDs := make([]uuid.UUID, 0, len(ds.templateIDSet))
@@ -2201,9 +2367,14 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
22012367
slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
22022368
return slice.Ascending(a.String(), b.String())
22032369
})
2370+
activeUserIDs := make([]uuid.UUID, 0, len(appUsageIntervalsByUser))
2371+
for userID := range appUsageIntervalsByUser {
2372+
activeUserIDs = append(activeUserIDs, userID)
2373+
}
2374+
22042375
result := database.GetTemplateInsightsRow{
2205-
TemplateIDs: templateIDs,
2206-
ActiveUsers: int64(len(appUsageIntervalsByUser)),
2376+
TemplateIDs: templateIDs,
2377+
ActiveUserIDs: activeUserIDs,
22072378
}
22082379
for _, intervals := range appUsageIntervalsByUser {
22092380
for _, interval := range intervals {
@@ -3075,24 +3246,15 @@ func (q *FakeQuerier) GetWorkspaceAgentsInLatestBuildByWorkspaceID(ctx context.C
30753246
return agents, nil
30763247
}
30773248

3078-
func (q *FakeQuerier) GetWorkspaceAppByAgentIDAndSlug(_ context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
3249+
func (q *FakeQuerier) GetWorkspaceAppByAgentIDAndSlug(ctx context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
30793250
if err := validateDatabaseType(arg); err != nil {
30803251
return database.WorkspaceApp{}, err
30813252
}
30823253

30833254
q.mutex.RLock()
30843255
defer q.mutex.RUnlock()
30853256

3086-
for _, app := range q.workspaceApps {
3087-
if app.AgentID != arg.AgentID {
3088-
continue
3089-
}
3090-
if app.Slug != arg.Slug {
3091-
continue
3092-
}
3093-
return app, nil
3094-
}
3095-
return database.WorkspaceApp{}, sql.ErrNoRows
3257+
return q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, arg)
30963258
}
30973259

30983260
func (q *FakeQuerier) GetWorkspaceAppsByAgentID(_ context.Context, id uuid.UUID) ([]database.WorkspaceApp, error) {

‎coderd/database/dbmetrics/dbmetrics.go

+7
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

+15
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

+6-1
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)