PSI Knowledge Base

PC Fleet Management

8 min readEdit this page

PC Fleet Management

Track, analyze, and manage the 640+ Dell PCs deployed inside PSI machines running PRIMS/CITS HMI software. Provides fleet-wide visibility into PC age, Windows version, Dell warranty status, and replacement history — enabling proactive upgrade planning before customers experience failures.

Overview

Every PSI machine ships with one or more Dell PCs running PRIMS or CITS HMI software. Over time these PCs age out — Windows versions go end-of-life, warranties expire, and hardware fails. Until now, there was no centralized view of the fleet. The PC Fleet Management system solves this by:

  1. Ingesting the PRIMS Installations spreadsheet (640 rows, 619 Dell service tags)
  2. Enriching with Dell warranty data (ship dates, warranty start/end, service levels)
  3. Joining with project data (customer names, machine descriptions, serial numbers)
  4. Presenting an interactive dashboard in PSI Explorer with filtering, sorting, and upgrade letter generation
PropertyValue
AccessPSI Explorer > PC Fleet
Data SourcePRIMS Installations spreadsheet + Dell Command Warranty
PCs Tracked640+ total, 619 with Dell service tags
Data Pipelinepsi-data-pipeline (prims_installations table)
RefreshGitHub Actions PRIMS PC Fleet Sync workflow

Why It Matters

  • Proactive service — Identify machines running Windows 7/XP or PCs past warranty before they fail in production
  • Retrofit planning — Know which customers need PC upgrades and generate personalized upgrade letters
  • Fleet visibility — Answer “How many PCs are running Win 7?” or “What’s our average PC age?” instantly
  • Customer conversations — CS managers can discuss specific PC status during service calls

Accessing the Tool

  1. Open PSI Explorer
  2. Click the Monitor icon (PC Fleet) in the left navigation bar
  3. Or navigate directly to #pc-fleet

Requires VPN or corporate network access. Authenticated via Azure AD.

Features

Machine-Centric View

The default view groups PCs by project (machine). Each project card shows:

  • Project number, customer name, and machine description
  • PC timeline — shows the original PC and any retrofit replacements in chronological order
  • Current PC highlighted with service tag, Windows version, age, and warranty status
  • Prior/replaced PCs shown as historical entries

This view makes it easy to see the full PC history for a machine, including when retrofits replaced older hardware.

Flat View

Toggle to a flat table showing every PC as a single row. All columns are sortable:

  • Project number, customer, description
  • Dell service tag (linked to Dell support)
  • Windows version, SQL Server version
  • Ship date, PC age
  • Warranty status and dates

Best for fleet-wide analysis and finding specific PCs.

Age Color Coding

PC age is calculated from the Dell ship date and displayed with color-coded badges:

ColorAge RangeMeaning
Green< 5 yearsCurrent, no action needed
Amber5-8 yearsAging, plan for replacement
Red8-10 yearsOverdue for replacement
Dark Red> 10 yearsCritical, high failure risk

Windows Version Badges

Badge ColorVersionStatus
GreenWindows 11Current
BlueWindows 10Supported (EOL Oct 2025)
AmberWindows 7End of life
RedWindows XPCritically obsolete

Warranty Status Badges

Each PC shows its Dell warranty status:

  • Active — warranty still valid, with end date shown
  • Expired — warranty lapsed, with expiration date
  • Unknown — no warranty data available (non-Dell or unenriched)

Warranty data includes the Dell service level (ProSupport, Basic, Next Business Day, etc.).

Filters

  • Text search — filter by project number, customer name, description, or service tag
  • Windows version — filter to specific OS versions
  • Warranty status — show only active, expired, or unknown warranty
  • Age range — filter by PC age brackets

Summary Cards

Dashboard header shows fleet-wide metrics (excludes replaced PCs from calculations):

  • Current PCs — count of active (non-replaced) PCs in the fleet
  • Warranty Status — active vs. expired warranty breakdown
  • Legacy OS — count of PCs running Windows 7 or XP
  • Average Age — mean PC age across the current fleet

Generate Upgrade Letter

Click the letter icon on any PC to generate a personalized customer upgrade letter. The template includes:

  • Customer name and machine details
  • Current PC specifications and age
  • Recommended upgrade path
  • PSI contact information

Based on the template at \\ad.ptihome.com\DFS\DATA\ (originally from C:\git\prims-pc-info\PC Upgrade - jmw edits (002).odt).

Data Sources

PRIMS Installations Spreadsheet

The primary data source is an Excel spreadsheet maintained by the controls department:

\\ad.ptihome.com\DFS\DATA\dept\eng\controls\apps\Prims Pro\PRIMS Installations.xls

Contains 640 rows with fields including:

  • Project/SPN (Sales Project Number)
  • Customer name and machine description
  • Dell service tag
  • Windows version, SQL Server version
  • PRIMS/CITS software version
  • Installation notes and dates

Important: SPN (Sales Project Number) is NOT the machine serial number. Machine serial numbers come from the UniData API, not the analytics database.

Dell Command | Warranty

Dell warranty and ship date data is enriched via Dell Command | Warranty CLI:

"C:\Program Files (x86)\Dell\CommandIntegrationSuite\DellWarranty-CLI.exe"

This tool queries the Dell warranty API for all 618 service tags in approximately 2 seconds. Returns:

  • Dell ship date (used for age calculation)
  • Warranty start and end dates
  • Service level / plan type (ProSupport, Basic, Next Business Day, etc.)
  • Service contracts

Note: An earlier approach using Playwright-based web scraping (enrich_dell_support.py) was attempted but Dell blocks headless browsers. The CLI tool is the working method.

Projects Table

The production projects table in Azure SQL PSI_Analytics (published by the data pipeline) provides:

  • Customer names
  • Machine descriptions
  • Project status

The PC Fleet API endpoint joins prims_installations with projects in Azure SQL to show customer and machine context alongside PC data. The upstream SQLite files (psi_ingest.db, psi_analytics.db) are pipeline stages, not the app’s live query target.

Architecture

Data Flow

PRIMS Installations.xls (K: drive)
    ↓
load_prims_standalone.py (parse into durable ingest DB)
    ↓
psi_ingest.db → prims_installations table (durable scraper-owned SQLite, PS-PROXY)
    ↓
enrich_dell_support.py (Dell ship dates + warranty enrichment)
    ↓
publish_ingest_tables.py (copy ingest-owned table into psi_analytics.db)
    ↓
sync_table_to_azure.py (push prims_installations to Azure SQL)
    ↓
Azure SQL: PSI_Analytics.prims_installations
    ↓
PSI Explorer: /api/analytics/pc-fleet (Azure SQL join with projects)
    ↓
PC Fleet page (#pc-fleet) in browser

Key Components

ComponentLocationPurpose
load_prims_standalone.pypsi-data-pipeline repoStandalone loader that parses XLS into psi_ingest.db
enrich_dell_support.pypsi-data-pipeline repoDell Command Warranty enrichment (adds ship dates, warranty data)
publish_ingest_tables.pypsi-data-pipeline repoCopies ingest-owned tables into psi_analytics.db for downstream rebuild/publish
sync_table_to_azure.pypsi-data-pipeline repoSyncs any SQLite table to Azure SQL PSI_Analytics
analytics-routes.jsbom-explorer-web/serverExpress route: /api/analytics/pc-fleet
PcFleetPage.tsxbom-explorer-web/srcReact page component
GitHub Actions workflowpsi-data-pipelinePRIMS PC Fleet Sync — independent data refresh

Infrastructure

  • ODBC Driver 18 installed on PS-PROXY for Azure SQL connectivity
  • Azure SQL PSI_Analytics database hosts the production copy of prims_installations
  • PSI Explorer backend queries Azure SQL directly via the analytics routes
  • Workflow verification now checks that prims_installations row counts match across ingest, analytics, and Azure SQL before the refresh job succeeds

How to Refresh Data

Full Refresh (Spreadsheet + Warranty)

  1. Update the spreadsheet — Controls department updates PRIMS Installations.xls on the K: drive
  2. Run the loader — Execute load_prims_standalone.py on PS-PROXY. This loads prims_installations into psi_ingest.db
  3. Enrich with Dell data — Run enrich_dell_support.py to query Dell Command Warranty for any new/changed service tags in psi_ingest.db
  4. Publish to analytics — Run publish_ingest_tables.py prims_installations so the analytics rebuild/publish path sees the current ingest-owned table
  5. Sync to Azure — Run sync_table_to_azure.py prims_installations to push to Azure SQL
  6. Verify — Check the PC Fleet page in PSI Explorer

Automated Refresh

The GitHub Actions workflow PRIMS PC Fleet Sync can be triggered to run the spreadsheet load, analytics publish, Azure sync, and row-count verification. This runs as an independent job, not part of the monolithic nightly pipeline.

Dell Warranty Enrichment Only

To refresh just the Dell warranty data without reloading the spreadsheet:

cd /path/to/psi-data-pipeline
python enrich_dell_support.py
python publish_ingest_tables.py prims_installations
python sync_table_to_azure.py prims_installations

Key Concepts

Current vs. Replaced PCs

When a project number appears multiple times in the PRIMS spreadsheet, it means the machine has had PC replacements via retrofit work orders. The system determines:

  • Current PC — the latest/newest PC for a given project number (shown prominently)
  • Prior/Replaced PC — older PCs that were swapped out (shown as historical entries)

Only current PCs are included in fleet summary metrics (average age, warranty counts, etc.). Replaced PCs are still visible in the machine-centric view for historical context.

Retrofit Work Orders

Duplicate project numbers indicate a retrofit — a return visit to upgrade or replace hardware on an existing machine. The PC Fleet timeline shows these as sequential entries, making it clear when a PC was replaced and what it was replaced with.

Age Thresholds

PSI uses the following guidelines for PC replacement planning:

  • < 5 years — No action needed, hardware is current
  • 5-8 years — Begin planning replacement, discuss with customer during service calls
  • 8-10 years — Actively recommend replacement, generate upgrade letter
  • > 10 years — Critical risk of hardware failure, prioritize replacement

SPN vs. Serial Number

The SPN (Sales Project Number) in the PRIMS spreadsheet is a PSI internal project number, NOT the machine serial number that appears on the nameplate. Machine serial numbers are sourced from the UniData API and are not currently included in the PC Fleet data (planned improvement).

Graph View