log-steam-playtime

# Steam Playtime Logging Track Steam gaming activity across three layers, all landing in a single Microsoft Fabric Lakehouse: | Table | Granularity | Source | |---|---|---| | `playtime_snapshots` | Full library, one row per (game × snapshot) | Daily Steam Web API call | | `playtime_deltas` | Only games where `playtime_total` increased since last snapshot | Computed from prior snapshot | | `sessions_log` | Per-session start/end/duration, multi-machine | Parsed from `gameprocess_log.txt` | A Direct Lake `steam_playtime.SemanticModel` exposes all three for reporting. ## Local config Before doing anything else, load the local config so the actual Steam ID, machine label, vault names, and lakehouse name are in context: @.local/steam-config.md If that file is missing, copy `.local/steam-config.md.example` to `.local/steam-config.md` and have the user fill it in. The `.local/` directory is gitignored. For full details on credential storage (1Password / Azure Key Vault / OS keyring), see `references/api-key.md`. For directory layout and Delta table schemas, see `references/workspace-structure.md`. For log file locations, format, and parsing rules, see `references/log-files.md`. For Steam Deck specifics (SSH setup, mesh-router gotchas), see `references/steam-deck.md`. ## Prerequisites - Steam Web API key (free at <https://steamcommunity.com/dev/apikey>) stored per `references/api-key.md` - Microsoft Fabric workspace with a Lakehouse. Direct Lake requires an F2 (or higher) capacity; the daily notebook itself runs on any Fabric SKU - Python 3.10+ (`uv add requests keyring`) - `fab` CLI logged in (`fab auth login`) - 1Password CLI (`op`) or `az` CLI if using those backends for the API key ## 1 — Local snapshots `scripts/snapshot.py` captures a point-in-time JSON + CSV of all owned games. It reads `STEAM_ID` and `STEAM_API_KEY` from the environment; falls back to the OS keyring (`service=steam`, `username=api_key`) if `STEAM_API_KEY` is unset. Do not edit tracked source — keep config in `.local/steam-config.md`. Run a snapshot: ```bash export STEAM_ID=<your-steam64-id> # Pick one — see references/api-key.md for all options export STEAM_API_KEY=$(op read "op://Private/Steam Web API/credential") # 1Password # or rely on keyring (no env var needed) python snapshot.py # Writes snapshots/YYYY-MM-DDTHH-MM-SSZ.{json,csv} ``` Compare two local snapshots with `scripts/delta.py`: ```bash python delta.py # last two snapshots python delta.py --latest # same python delta.py old.json new.json ``` Always use `playtime_total = playtime_forever + playtime_disconnected` — `playtime_forever` alone misses offline / Steam Deck time and will not match the Steam UI. ## 2 — Fabric cloud pipeline ### 2a — Provision workspace + lakehouse The lakehouse name comes from `.local/steam-config.md` (`steam_lh` is the default in this skill). ```bash fab ls .capacities fab mkdir "steam.Workspace" -P capacityName=<your-capacity> fab mkdir "steam.Workspace/steam_lh.Lakehouse" fab get "steam.Workspace/steam_lh.Lakehouse" -q "properties.sqlEndpointProperties" ``` This skill assumes the **legacy (non-schema-enabled) Lakehouse** — tables at `Tables/<name>`, exposed via SQL endpoint as `dbo.<name>`. See `examples/lakehouse/README.md`. ### 2b — Daily notebook (`get_steam_playtime`) Scheduled daily. Two writes per run (see `examples/notebook_daily.py`): 1. Append a full snapshot row per game to `playtime_snapshots` 2. Compute the delta vs the previous snapshot; append rows where `playtime_total` increased to `playtime_deltas` On the very first run every game appears in `playtime_deltas` because there's no prior snapshot. ### 2c — Manual baseline (`get_steam_total_playtime`) Same as the daily notebook but only the snapshot half. Use for an extra checkpoint outside the schedule. See `examples/notebook_baseline.py`. ### 2d — Sessions reload (`load_sessions_log`) See `examples/notebook_load_sessions_log.py`. Globs every `*.json` in `Files/sessions/`, unions the rows, **overwrites** `sessions_log`. Idempotent — re-running with new JSONs just refreshes the table. ### 2e — Import notebooks Generate `.Notebook` directories with Python (never PowerShell here-strings — encoding issues on Windows). Write `.ipynb` via `json.dumps(nb, ensure_ascii=True)` + `encoding="utf-8"`: ```bash fab import "steam.Workspace/get_steam_total_playtime.Notebook" -i ./get_steam_total_playtime.Notebook -f fab import "steam.Workspace/get_steam_playtime.Notebook" -i ./get_steam_playtime.Notebook -f fab import "steam.Workspace/load_sessions_log.Notebook" -i ./load_sessions_log.Notebook -f ``` ### 2f — Schedule the daily notebook (automatic deltas) The schedule on `get_steam_playtime` is what makes deltas land automatically every day — no local cron required: ```bash WS="steam.Workspace" NB="get_steam_playtime" START=$(date -u +"%Y-%m-%dT06:00:00Z") END="2099-12-31T06:00:00Z" fab job run-sch "$WS/$NB.Notebook" \ --type Daily --interval 1 \ --start "$START" --end "$END" ``` Both `--start` and `--end` are required; omitting either causes `[NotRunnable]`. Verify the schedule: ```bash fab job run-sch "$WS/$NB.Notebook" --list ``` Check the most recent runs (status, duration, fail reason): ```bash fab job run-list "steam.Workspace/get_steam_playtime.Notebook" ``` ### Alternative: local cron / systemd timer If running snapshots locally instead of (or in addition to) the Fabric schedule, wrap `scripts/snapshot.py` in cron / systemd / Windows Task Scheduler. Then upload to `Files/snapshots/` and trigger `get_steam_playtime`: ```bash fab cp ./snapshots/<latest>.json "steam.Workspace/steam_lh.Lakehouse/Files/snapshots/" -f fab job run "steam.Workspace/get_steam_playtime.Notebook" ``` ## 3 — Direct Lake semantic model Build a Direct Lake model over all three tables in one shot: ```bash python scripts/build_direct_lake_model.py \ --workspace "steam.Workspace" \ --lakehouse "steam_lh.Lakehouse" \ --model-name "steam_playtime" \ --tables playtime_snapshots playtime_deltas sessions_log \ --schema dbo \ --out ./steam_playtime.SemanticModel fab import "steam.Workspace/steam_playtime.SemanticModel" -i ./steam_playtime.SemanticModel -f ``` The script resolves the lakehouse SQL endpoint host + ID via `fab get`, pulls each table's schema via `fab table schema`, and emits `model.tmdl`, `expressions.tmdl`, `database.tmdl`, `definition.pbism`, `.platform`, and one `tables/<name>.tmdl` per table. See `examples/semantic_model/` for the resulting layout. ## 4 — Historical session recovery Per-session reconstruction from `gameprocess_log.txt`. Locations, format, rotation behaviour, and edge cases live in `references/log-files.md`. Steam Deck specifics (KDE Connect vs SSH, mesh-router fixes, password recovery) live in `references/steam-deck.md`. ```bash python scripts/parse_sessions.py \ --machine-id <MACHINE_ID> \ --log-path "<platform-specific path to gameprocess_log.txt>" \ --format json \ --output sessions_historical_<MACHINE_ID>.json ``` Upload to the lakehouse and reload `sessions_log`: ```bash fab cp ./sessions_historical_<MACHINE_ID>.json \ "steam.Workspace/steam_lh.Lakehouse/Files/sessions/sessions_historical_<MACHINE_ID>.json" -f fab job run "steam.Workspace/load_sessions_log.Notebook" ``` Each additional machine = drop another JSON in `Files/sessions/` and re-run the notebook. The notebook globs `*.json` so the table is multi-machine ready. Edge cases handled by the parser: - Multiple PIDs per session (DLSS updater, child processes): only the first `adding PID` per AppID marks session start - No start (log truncated): `session_start_local` left blank, row still written - Session open at end of file (game running): `session_end_local` left blank - Sessions longer than `min(8h, playtime_total_hours)` are capped via `duration_minutes_capped` / `duration_hours_capped` (raw values preserved) ## Resources ### References (`references/`) - **`api-key.md`** — get the Steam Web API key; store it in 1Password, Azure Key Vault, or the OS keyring - **`workspace-structure.md`** — directory tree, Delta table schemas, semantic model layout - **`log-files.md`** — Steam log file locations per OS, format, rotation behaviour, parser usage - **`steam-deck.md`** — getting `gameprocess_log.txt` off the Deck (KDE Connect vs SSH), mesh-router and password-recovery gotchas ### Examples (`examples/`) - **`notebook_baseline.py`** — `get_steam_total_playtime` (manual / one-off) - **`notebook_daily.py`** — `get_steam_playtime` (scheduled daily; snapshots + deltas) - **`notebook_load_sessions_log.py`** — `load_sessions_log` (multi-machine reload) - **`lakehouse/`** — `.platform` + provisioning README - **`semantic_model/`** — Direct Lake TMDL skeleton ### Scripts (`scripts/`) - **`snapshot.py`** — local snapshot (JSON + CSV) - **`delta.py`** — compare two local snapshots - **`parse_sessions.py`** — parse `gameprocess_log.txt` to session CSV/JSON; takes `--machine-id` and `--log-path` - **`build_direct_lake_model.py`** — generate the TMDL package for a multi-table Direct Lake semantic model - **`pull_deck_logs.sh`** / **`pull_deck_logs.ps1`** — scp Steam logs off the Deck via SSH key auth (reads host from keyring `steam/deck_host`); see `references/steam-deck.md` for one-time key setup ### Local config (`.local/`) - **`steam-config.md.example`** — template; copy to `.local/steam-config.md` (gitignored)