coder · ammario · Sep 2, 2022 · Sep 1, 2022 · Sep 1, 2022 · Sep 1, 2022
diff --git a/agent/agent_test.go b/agent/agent_test.go
@@ -105,7 +105,7 @@ func TestAgent(t *testing.T) {
 						var ok bool
 						s, ok = (<-stats)
 						return ok && s.NumConns > 0 && s.RxBytes > 0 && s.TxBytes > 0
-					}, testutil.WaitShort, testutil.IntervalFast,
+					}, testutil.WaitLong, testutil.IntervalFast,
 						"never saw stats: %+v", s,
 					)
 				})

diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -2208,6 +2208,22 @@ func (q *fakeQuerier) UpdateWorkspaceTTL(_ context.Context, arg database.UpdateW
 	return sql.ErrNoRows
 }
 
+func (q *fakeQuerier) UpdateWorkspaceLastUsedAt(_ context.Context, arg database.UpdateWorkspaceLastUsedAtParams) error {
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+
+	for index, workspace := range q.workspaces {
+		if workspace.ID != arg.ID {
+			continue
+		}
+		workspace.LastUsedAt = arg.LastUsedAt
+		q.workspaces[index] = workspace
+		return nil
+	}
+
+	return sql.ErrNoRows
+}
+
 func (q *fakeQuerier) UpdateWorkspaceBuildByID(_ context.Context, arg database.UpdateWorkspaceBuildByIDParams) error {
 	q.mutex.Lock()
 	defer q.mutex.Unlock()

diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000043_workspace_last_used.down.sql b/coderd/database/migrations/000043_workspace_last_used.down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE workspaces
+    DROP COLUMN last_used_at;
diff --git a/coderd/database/migrations/000043_workspace_last_used.up.sql b/coderd/database/migrations/000043_workspace_last_used.up.sql
@@ -0,0 +1,2 @@
+ALTER TABLE workspaces
+    ADD COLUMN last_used_at timestamp NOT NULL DEFAULT '0001-01-01 00:00:00+00:00';
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -137,3 +137,11 @@ SET
 	ttl = $2
 WHERE
 	id = $1;
+
+-- name: UpdateWorkspaceLastUsedAt :exec
+UPDATE
+	workspaces
+SET
+	last_used_at = $2
+WHERE
+	id = $1;
diff --git a/coderd/templates_test.go b/coderd/templates_test.go
@@ -608,6 +608,10 @@ func TestTemplateDAUs(t *testing.T) {
 		Entries: []codersdk.DAUEntry{},
 	}, daus, "no DAUs when stats are empty")
 
+	workspaces, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{})
+	require.NoError(t, err)
+	assert.Zero(t, workspaces[0].LastUsedAt)
+
 	conn, err := client.DialWorkspaceAgent(ctx, resources[0].Agents[0].ID, opts)
 	require.NoError(t, err)
 	defer func() {
@@ -641,4 +645,10 @@ func TestTemplateDAUs(t *testing.T) {
 		testutil.WaitShort, testutil.IntervalFast,
 		"got %+v != %+v", daus, want,
 	)
+
+	workspaces, err = client.Workspaces(ctx, codersdk.WorkspaceFilter{})
+	require.NoError(t, err)
+	assert.WithinDuration(t,
+		time.Now(), workspaces[0].LastUsedAt, time.Minute,
+	)
 }
diff --git a/coderd/workspaceagents.go b/coderd/workspaceagents.go
@@ -830,18 +830,20 @@ func (api *API) workspaceAgentReportStats(rw http.ResponseWriter, r *http.Reques
 		// We will see duplicate reports when on idle connections
 		// (e.g. web terminal left open) or when there are no connections at
 		// all.
-		var insert = !reflect.DeepEqual(lastReport, rep)
+		// We also don't want to update the workspace last used at on duplicate
+		// reports.
+		var updateDB = !reflect.DeepEqual(lastReport, rep)
 
 		api.Logger.Debug(ctx, "read stats report",
 			slog.F("interval", api.AgentStatsRefreshInterval),
 			slog.F("agent", workspaceAgent.ID),
 			slog.F("resource", resource.ID),
 			slog.F("workspace", workspace.ID),
-			slog.F("insert", insert),
+			slog.F("update_db", updateDB),
 			slog.F("payload", rep),
 		)
 
-		if insert {
+		if updateDB {
 			lastReport = rep
 
 			_, err = api.Database.InsertAgentStat(ctx, database.InsertAgentStatParams{
@@ -860,6 +862,18 @@ func (api *API) workspaceAgentReportStats(rw http.ResponseWriter, r *http.Reques
 				})
 				return
 			}
+
+			err = api.Database.UpdateWorkspaceLastUsedAt(ctx, database.UpdateWorkspaceLastUsedAtParams{
+				ID:         build.WorkspaceID,
+				LastUsedAt: time.Now(),
+			})
+			if err != nil {
+				httpapi.Write(rw, http.StatusBadRequest, codersdk.Response{
+					Message: "Failed to update workspace last used at.",
+					Detail:  err.Error(),
+				})
+				return
+			}
 		}
 
 		select {

diff --git a/coderd/workspaces.go b/coderd/workspaces.go
@@ -941,6 +941,7 @@ func convertWorkspace(
 		Name:              workspace.Name,
 		AutostartSchedule: autostartSchedule,
 		TTLMillis:         ttlMillis,
+		LastUsedAt:        workspace.LastUsedAt,
 	}
 }
 

diff --git a/codersdk/workspaces.go b/codersdk/workspaces.go
@@ -30,6 +30,7 @@ type Workspace struct {
 	Name              string         `json:"name"`
 	AutostartSchedule *string        `json:"autostart_schedule,omitempty"`
 	TTLMillis         *int64         `json:"ttl_ms,omitempty"`
+	LastUsedAt        time.Time      `json:"last_used_at"`
 }
 
 // CreateWorkspaceBuildRequest provides options to update the latest workspace build.

diff --git a/enterprise/audit/table.go b/enterprise/audit/table.go
@@ -95,6 +95,7 @@ var AuditableResources = auditMap(map[any]map[string]Action{
 		"name":               ActionTrack,
 		"autostart_schedule": ActionTrack,
 		"ttl":                ActionTrack,
+		"last_used_at":       ActionIgnore,
 	},
 })
 

diff --git a/site/src/api/typesGenerated.ts b/site/src/api/typesGenerated.ts
@@ -514,6 +514,7 @@ export interface Workspace {
   readonly name: string
   readonly autostart_schedule?: string
   readonly ttl_ms?: number
+  readonly last_used_at: string
 }
 
 // From codersdk/workspaceresources.go

diff --git a/site/src/components/WorkspacesTable/WorkspaceLastUsed.tsx b/site/src/components/WorkspacesTable/WorkspaceLastUsed.tsx
@@ -0,0 +1,39 @@
+import { Theme, useTheme } from "@material-ui/core/styles"
+import { FC } from "react"
+
+import dayjs from "dayjs"
+import relativeTime from "dayjs/plugin/relativeTime"
+
+dayjs.extend(relativeTime)
+
+interface WorkspaceLastUsedProps {
+  lastUsedAt: string
+}
+
+export const WorkspaceLastUsed: FC<WorkspaceLastUsedProps> = ({ lastUsedAt }) => {
+  const theme: Theme = useTheme()
+
+  const t = dayjs(lastUsedAt)
+  const now = dayjs()
+
+  let color = theme.palette.text.secondary
+  let message = t.fromNow()
+
+  if (t.isAfter(now.subtract(1, "hour"))) {
+    color = theme.palette.success.main
+    // Since the agent reports on a 10m interval,
+    // the last_used_at can be inaccurate when recent.
+    message = "In the last hour"
+  } else if (t.isAfter(now.subtract(1, "day"))) {
+    color = theme.palette.primary.main
+  } else if (t.isAfter(now.subtract(1, "month"))) {
+    color = theme.palette.text.secondary
+  } else if (t.isAfter(now.subtract(100, "year"))) {
+    color = theme.palette.warning.light
+  } else {
+    color = theme.palette.error.light
+    message = "Never"
+  }
+
+  return <span style={{ color: color }}>{message}</span>
+}
diff --git a/site/src/components/WorkspacesTable/WorkspacesRow.tsx b/site/src/components/WorkspacesTable/WorkspacesRow.tsx
@@ -15,6 +15,7 @@ import {
 } from "../TableCellData/TableCellData"
 import { TableCellLink } from "../TableCellLink/TableCellLink"
 import { OutdatedHelpTooltip } from "../Tooltips"
+import { WorkspaceLastUsed } from "./WorkspaceLastUsed"
 
 const Language = {
   upToDateLabel: "Up to date",
@@ -64,6 +65,12 @@ export const WorkspacesRow: FC<
           }
         />
       </TableCellLink>
+      <TableCellLink to={workspacePageLink}>
+        <TableCellData>
+          <WorkspaceLastUsed lastUsedAt={workspace.last_used_at} />
+        </TableCellData>
+      </TableCellLink>
+
       <TableCellLink to={workspacePageLink}>
         {workspace.outdated ? (
           <span className={styles.outdatedLabel}>

diff --git a/site/src/components/WorkspacesTable/WorkspacesTable.tsx b/site/src/components/WorkspacesTable/WorkspacesTable.tsx
@@ -11,6 +11,7 @@ import { WorkspacesTableBody } from "./WorkspacesTableBody"
 const Language = {
   name: "Name",
   template: "Template",
+  lastUsed: "Last Used",
   version: "Version",
   status: "Status",
   lastBuiltBy: "Last Built By",
@@ -34,6 +35,7 @@ export const WorkspacesTable: FC<React.PropsWithChildren<WorkspacesTableProps>>
           <TableRow>
             <TableCell width="25%">{Language.name}</TableCell>
             <TableCell width="35%">{Language.template}</TableCell>
+            <TableCell width="20%">{Language.lastUsed}</TableCell>
             <TableCell width="20%">{Language.version}</TableCell>
             <TableCell width="20%">{Language.status}</TableCell>
             <TableCell width="1%"></TableCell>
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE workspaces
		DROP COLUMN last_used_at;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE workspaces
		ADD COLUMN last_used_at timestamp NOT NULL DEFAULT '0001-01-01 00:00:00+00:00';