# Data freshness in cluster_1 DBs — by journal tables

Snapshot date: 2026-09-14 (live query to 50.21.183.111, read-only)
Method: MAX(dtmDate) from **tblSMLog** (system journal of user actions) + MAX(dtmDate) from **tblSMTransaction** (document transaction journal) + tblSMLog row count.
Coverage: 43/43 cluster_1 DBs (excluding cfg/hangfire/service — they don't have these tables).

## Why these tables
From the schema inventory (columns.tsv), the cluster_1 core has a unified system journal common to all DBs:
- **tblSMLog** (dtmDate, strType, strRoute, strEntityName, intEntityId...) — log of actions in the i21 application. Populated in **43/43 DBs** (avg 467K rows) — the best marker of "people work here".
- **tblSMTransaction** (dtmDate, dtmLockedDate, strTransactionNo, dblAmount, strApprovalStatus...) — journal of transactions/documents (posted operations, approvals). Present in all core DBs.
- Alternatives (tblSMAudit — field-change audit via intLogId; tblARAuditLog — AR-specific; tblGLTransactionSummary — ledger entries) — also in the core, but already by purpose.

## RESULT: freshness by MAX(tblSMLog.dtmDate), server time 2026-09-14

### ACTIVE (data written NOW, last 7 days):
| DB | Last log | SMLog rows | Last SMTransaction |
|---|---|---|---|
| 2610BERRYOILUAP01 | **2026-09-14 11:55** | 2 368 | 2026-09-13 |
| 2610JOHNSONPETROLEUMUAP01 | **2026-09-14 09:03** | 7 689 | 2026-09-13 |
| 2710PALMDALEOILUAP01 | **2026-09-14 03:41** | 8 991 | 2026-09-14 |
| 2610BERRYOILUAP02 | **2026-09-14 03:20** | 2 130 | 2026-09-14 |
| PALMDALEOILUAP01 | 2026-09-11 | 38 634 | 2026-09-03 |
| 2610CHERRYENERGYUAP01 | 2026-09-10 | 9 671 | 2026-09-09 |
| 2610PIGOTTOILUAP01 | 2026-09-09 | 165 | — |

### FRESH (weeks):
| DB | Last log | rows |
|---|---|---|
| 2610RTROGERSUAP01 | 2026-09-03 | 3 866 |
| 2430DDENERGYUAP101 | 2026-09-01 | 2 894 499 |
| 2710DALLMYRUAP01 | 2026-08-28 | 132 237 |
| 2610PAMDALEUAP01 | 2026-08-07 | 123 |
| CHERRYENERGYUAP01 | 2026-08-06 | 1 727 369 |

### QUIETING DOWN (months, 2026):
2430ProdDevPalmdaleUAP01 (07-31), ECOMMAINUAP01 (07-07), ECOMPORTALUAP01 (07-06), 2410WoodfordUAP101 (06-29), 2610NEWTONOILUAP01 (06-26), 2430PalmdaleAgnosticF01 (06-21), 2430SunshineAgnosticP01 (05-28), 2430PalmdaleAgnosticV01 (05-26), 2210CHILDERSOILUAP01 (05-25), 2610DALLMYRUAP01 (05-19), 2620Palmdale01 (05-17), 2430JDSTREETTUAP01 (05-04), 2630ECOMMAINUAP01 (04-23), 2630ECOMPORTALUAP01 (04-23), 2210DAVISOILUAP01 (03-13), JMREYNOLDSUAP01 (01-23)

### ABANDONED (2025 and older):
2210JEFFERSONLANDMARKUAP01 (2025-11-12), WAMAB3UAP01 (2025-10-27), 2430CAREPETROLEUMUAP01 (2025-10-10), 2430BLUPETROLEUMUAP01 (2025-08-18), 2210JOHNSONPETROUAP01 (2025-07-18), **2210PIGOTTOILUAP01 (2025-07-04)**, 2210GAINESOILUAP01 (2025-07-04), FEHRENBACHEROILUAP01 (2025-04-10), BANNERFURNANCEFUELUAP01 (2025-04-03), SANTAENERGYUAP01 (2025-03-24), LEWISOILUAP101 (2025-03-21), LEWISOILUAP201 (2025-03-21), GENERALPETROUAP101 (2024-12-05), GENERALPETROUAP201 (2024-12-06), NEWTONUAPMBIL01 (2024-08-16)

## Notes on the data
1. **Migration 2210→2610/2710**: the old Pigott/Johnson/Cherry/Dallmyr (prefix 2210) are abandoned in 2025, the same-named with prefix 2610/2710 are active NOW. Customers moved to new instances (matches DB creation dates 2026-05..08 from sys.databases).
2. **Discrepancy with db_freshness.csv (09-05)**: there 2210PIGOTTOILUAP01 is marked as priority by the last GL *transactions* (2025-07-04); the user journal confirms — after 07/2025 there's no activity in it, live data moved to 2610PIGOTTOILUAP01.
3. **SMTransaction sometimes in the future** (2050, 2038, 2028) — scheduled/post-dated documents (recurring, approvals). For freshness, use only tblSMLog.dtmDate.
4. **Few rows ≠ dead DB**: 2610PIGOTTOILUAP01 (165 rows) and 2610PAMDALEUAP01 (123) — new DBs, created 2026-08, the journal hasn't built up volume yet, but the records are fresh.

## Conclusion for exfiltration priorities
- **Critically active** (data changes daily): 2610BERRYOILUAP01/02, 2610JOHNSONPETROLEUMUAP01, 2710PALMDALEOILUAP01, 2610CHERRYENERGYUAP01, 2610PIGOTTOILUAP01, PALMDALEOILUAP01, 2610RTROGERSUAP01 — back up last among the active ones, or with a repeat at the end of the campaign.
- **Large archive** (data complete, doesn't change): 2210PIGOTTOILUAP01 (182 GB mdf), 2430DDENERGYUAP101 (142 GB), WAMAB3UAP01, 2410WoodfordUAP101, CHERRYENERGYUAP01 — can be backed up anytime, the snapshot won't stale.

## Artifacts
- freshness_smlog.tsv — raw query output (43 rows, db|smlog_max|smlog_rows|smtxn_max)
- Query is reproducible: temp table #fresh + cursor over sys.databases, MAX(dtmDate) tblSMLog/tblSMTransaction (see session history; read-only, no writes to the server)