powershell-scripts/docs/ShopDB-API.md

# ShopDB API Reference

REST API for PowerShell data collection scripts and ShopDB integrations.

## Table of Contents

- [Overview](#overview)
- [Base URL](#base-url)
- [Authentication](#authentication)
- [Endpoints](#endpoints)
  - [GET Endpoints](#get-endpoints)
  - [POST Endpoints](#post-endpoints)
- [Response Format](#response-format)
- [Error Handling](#error-handling)
- [PowerShell Integration](#powershell-integration)

---

## Overview

The ShopDB API (`api.asp`) provides endpoints for:
- **Data Collection**: Receive PC asset data from PowerShell scripts
- **PC Retrieval**: Query lists of shopfloor PCs for remote management
- **Maintenance**: Update WinRM status, printer mappings, installed applications
- **Dashboard**: Health checks and monitoring data

**Technology**: Classic ASP (VBScript) with MySQL database

---

## Base URL

| Environment | URL |
|-------------|-----|
| Production | `https://tsgwp00525.rd.ds.ge.com/shopdb/api.asp` |
| Development | `http://192.168.122.151:8080/api.asp` |

---

## Authentication

Currently no authentication required. API is accessible from internal network only.

---

## Endpoints

### GET Endpoints

#### getDashboardData

Health check endpoint to verify API is online.

**Request:**
```
GET /api.asp?action=getDashboardData
```

**Response:**
```json
{
  "success": true,
  "message": "ShopDB API is online - v13 (inlined all queries)",
  "version": "1.0",
  "schema": "Phase 2",
  "connStatus": "objConn is Open"
}
```

**Used By:** Health monitoring, connectivity tests

---

#### getShopfloorPCs

Returns list of all active shopfloor PCs for remote management operations.

**Request:**
```
GET /api.asp?action=getShopfloorPCs
GET /api.asp?action=getShopfloorPCs&pctypeid=1
GET /api.asp?action=getShopfloorPCs&businessunitid=2
```

**Query Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `pctypeid` | int | Filter by PC type (optional) |
| `businessunitid` | int | Filter by business unit (optional) |

**PC Type IDs:**

| ID | Type |
|----|------|
| 1 | Shopfloor |
| 2 | CMM |
| 3 | Wax Trace |
| 4 | Keyence |
| 5 | EAS1000 |
| 6 | Genspect |
| 7 | Heat Treat |
| 8 | Engineer |
| 9 | Standard |
| 10 | Inspection |
| 11 | Dashboard |
| 12 | Lobby Display |

**Response:**
```json
{
  "success": true,
  "count": 45,
  "data": [
    {
      "machineid": 1234,
      "hostname": "G1ZTNCX3ESF",
      "machinenumber": "M0612, M0613",
      "serialnumber": "ABC1234567",
      "ipaddress": "10.134.50.101",
      "loggedinuser": "DOMAIN\\jsmith",
      "pctype": "Shopfloor",
      "pctypeid": 1,
      "businessunit": "Rotor",
      "businessunitid": 2,
      "lastupdated": "1/15/2025 8:30 AM"
    }
  ]
}
```

**Notes:**
- Returns only PCs with 10.134.*.* IP addresses by default
- Dashboard (11) and Lobby Display (12) types bypass IP filter
- Only includes machinetypeid 33-35 (actual PCs)

**Used By:** `Update-ShopfloorPCs-Remote.ps1 -All`, `Invoke-RemoteMaintenance.ps1 -All`

---

#### getHighUptimePCs

Returns PCs that haven't been rebooted in specified number of days.

**Request:**
```
GET /api.asp?action=getHighUptimePCs&minUptime=30
```

**Query Parameters:**

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| `minUptime` | int | 10 | Minimum uptime in days |

**Response:**
```json
{
  "success": true,
  "count": 5,
  "minUptime": 30,
  "data": [
    {
      "machineid": 1234,
      "hostname": "SHOPFLOOR-01",
      "machinenumber": "M0612",
      "serialnumber": "ABC1234567",
      "ipaddress": "10.134.50.101",
      "loggedinuser": "DOMAIN\\jsmith",
      "pctype": "Shopfloor",
      "uptime_days": 45,
      "lastboottime": "2024-12-01 08:00:00",
      "pctypeid": 1,
      "businessunit": "Rotor",
      "businessunitid": 2,
      "lastupdated": "1/15/2025 8:30 AM"
    }
  ]
}
```

**Used By:** `Update-ShopfloorPCs-Remote.ps1 -Reboot -MinUptimeDays 30`

---

#### getRecordedIP

Get the recorded IP address for a specific hostname.

**Request:**
```
GET /api.asp?action=getRecordedIP&hostname=G1ZTNCX3ESF
```

**Query Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `hostname` | string | Computer name to look up |

**Response:**
```json
{
  "success": true,
  "hostname": "G1ZTNCX3ESF",
  "ipaddress": "10.134.50.101"
}
```

---

#### getPCMachineRelationships

Returns PCs that have relationships to equipment (machines).

**Request:**
```
GET /api.asp?action=getPCMachineRelationships
```

**Response:**
```json
{
  "success": true,
  "count": 25,
  "data": [
    {
      "pc_machineid": 1234,
      "pc_hostname": "G1ZTNCX3ESF",
      "equipment_machineid": 5678,
      "equipment_machinenumber": "M0612"
    }
  ]
}
```

---

### POST Endpoints

#### updateCompleteAsset

Main endpoint for PC data collection. Receives comprehensive asset data from PowerShell scripts.

**Request:**
```
POST /api.asp
Content-Type: application/x-www-form-urlencoded

action=updateCompleteAsset
&hostname=G1ZTNCX3ESF
&serialNumber=ABC1234567
&manufacturer=Dell Inc.
&model=OptiPlex 7080
&pcType=Shopfloor
&loggedInUser=DOMAIN\jsmith
&machineNo=M0612
&osVersion=Microsoft Windows 10 Enterprise
&lastBootTime=2025-01-15 08:30:00
&hasVnc=1
&hasWinRM=1
&networkInterfaces=[...]
&dncConfig=[...]
&installedApps=[...]
```

**Required Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `hostname` | string | Computer name |
| `serialNumber` | string | BIOS serial number |

**Optional Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `manufacturer` | string | System manufacturer |
| `model` | string | System model |
| `pcType` | string | PC classification (Shopfloor, CMM, etc.) |
| `loggedInUser` | string | Currently logged in user |
| `machineNo` | string | Associated machine number(s) |
| `osVersion` | string | Windows version |
| `lastBootTime` | datetime | Last boot timestamp |
| `hasVnc` | int | VNC installed (0/1) |
| `hasWinRM` | int | WinRM enabled (0/1) |
| `networkInterfaces` | JSON | Network adapter configurations |
| `dncConfig` | JSON | DNC/FTP settings |
| `installedApps` | JSON | Tracked applications |
| `warrantyEndDate` | date | Dell warranty expiration |
| `warrantyStatus` | string | Warranty status text |
| `dncDualPathEnabled` | int | Dual path enabled (0/1) |
| `dncPath1Name` | string | Path 1 name |
| `dncPath2Name` | string | Path 2 name |

**Network Interfaces JSON Format:**
```json
[
  {
    "interfaceName": "Ethernet0",
    "ipAddress": "10.134.50.101",
    "subnetMask": "24",
    "defaultGateway": "10.134.50.1",
    "macAddress": "00-11-22-33-44-55",
    "isDhcp": 0,
    "isActive": 1,
    "isMachineNetwork": 0,
    "isPrimary": 1
  }
]
```

**DNC Config JSON Format:**
```json
{
  "site": "WJF",
  "cnc": "FANUC",
  "ncif": "FOCAS2",
  "machineNo": "M0612",
  "ftpPrimary": "10.134.50.10",
  "ftpSecondary": "10.134.50.11"
}
```

**Installed Apps JSON Format:**
```json
[
  {
    "appid": 5,
    "appname": "PC-DMIS",
    "version": "2023.1",
    "isactive": 1
  }
]
```

**Response:**
```json
{
  "success": true,
  "message": "PC data stored successfully",
  "pcid": 1234,
  "hostname": "G1ZTNCX3ESF",
  "debugMsg": "PCType=Shopfloor, DNC=YES, Net=YES"
}
```

**Used By:** `Update-ShopfloorPCs-Remote.ps1`, `Update-PC-CompleteAsset.ps1`

---

#### updatePrinterMapping

Links a PC to its default printer.

**Request:**
```
POST /api.asp
Content-Type: application/x-www-form-urlencoded

action=updatePrinterMapping
&hostname=G1ZTNCX3ESF
&printerFQDN=printer01.domain.com
```

**Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `hostname` | string | PC hostname |
| `printerFQDN` | string | Printer FQDN, Windows name, or IP |

**Response:**
```json
{
  "success": true,
  "message": "Printer mapping updated",
  "printerId": 45,
  "machinesUpdated": 1,
  "matchMethod": "fqdn"
}
```

**Match Methods:** `fqdn`, `windowsname`, `ip`

---

#### updateInstalledApps

Updates the list of tracked applications installed on a PC.

**Request:**
```
POST /api.asp
Content-Type: application/x-www-form-urlencoded

action=updateInstalledApps
&hostname=G1ZTNCX3ESF
&installedApps=[{"appid":5,"appname":"PC-DMIS","version":"2023.1","isactive":1}]
```

**Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `hostname` | string | PC hostname |
| `installedApps` | JSON | Array of tracked applications |

**Response:**
```json
{
  "success": true,
  "message": "Installed apps updated",
  "appsUpdated": 3
}
```

---

#### updateWinRMStatus

Updates the WinRM enabled status for a PC.

**Request:**
```
POST /api.asp
Content-Type: application/x-www-form-urlencoded

action=updateWinRMStatus
&hostname=G1ZTNCX3ESF
&hasWinRM=1
```

**Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `hostname` | string | PC hostname |
| `hasWinRM` | int | WinRM status (0=disabled, 1=enabled) |

**Response:**
```json
{
  "success": true,
  "message": "WinRM status updated",
  "hostname": "G1ZTNCX3ESF",
  "iswinrm": 1
}
```

---

#### updateMachinePositions

Bulk update machine positions on the floor map.

**Request:**
```
POST /api.asp
Content-Type: application/x-www-form-urlencoded

action=updateMachinePositions
&changes=[{"machineid":1234,"mapleft":100,"maptop":200}]
```

**Parameters:**

| Parameter | Type | Description |
|-----------|------|-------------|
| `changes` | JSON | Array of position updates |

**Response:**
```json
{
  "success": true,
  "updated": 5
}
```

---

## Response Format

All responses are JSON with consistent structure:

**Success Response:**
```json
{
  "success": true,
  "message": "Operation completed",
  "data": [...]
}
```

**Error Response:**
```json
{
  "success": false,
  "error": "Error description"
}
```

---

## Error Handling

| Error | Cause | Solution |
|-------|-------|----------|
| `hostname and serialNumber are required` | Missing required fields | Include all required parameters |
| `PC not found: hostname` | Hostname not in database | Verify hostname or run initial collection |
| `Printer not found: printerFQDN` | Printer not in database | Add printer to ShopDB first |
| `Invalid action: xyz` | Unknown action parameter | Check action spelling |
| `Database error: ...` | MySQL error | Check database connectivity |

---

## PowerShell Integration

### Sending Data to API

```powershell
# Build POST body
$body = @{
    action = "updateCompleteAsset"
    hostname = $env:COMPUTERNAME
    serialNumber = (Get-CimInstance Win32_BIOS).SerialNumber
    manufacturer = (Get-CimInstance Win32_ComputerSystem).Manufacturer
    model = (Get-CimInstance Win32_ComputerSystem).Model
    pcType = "Shopfloor"
}

# Send to API
$response = Invoke-RestMethod -Uri "https://server/shopdb/api.asp" -Method POST -Body $body
```

### Retrieving PC List

```powershell
# Get all shopfloor PCs
$response = Invoke-RestMethod -Uri "https://server/shopdb/api.asp?action=getShopfloorPCs"
$pcs = $response.data

# Get CMM PCs only
$response = Invoke-RestMethod -Uri "https://server/shopdb/api.asp?action=getShopfloorPCs&pctypeid=2"

# Get high uptime PCs
$response = Invoke-RestMethod -Uri "https://server/shopdb/api.asp?action=getHighUptimePCs&minUptime=30"
```

### Script Usage Examples

```powershell
# Update-ShopfloorPCs-Remote.ps1 uses getShopfloorPCs
.\Update-ShopfloorPCs-Remote.ps1 -All -Credential $cred
# Internally calls: GET /api.asp?action=getShopfloorPCs

# Reboot mode uses getHighUptimePCs
.\Update-ShopfloorPCs-Remote.ps1 -Reboot -MinUptimeDays 30
# Internally calls: GET /api.asp?action=getHighUptimePCs&minUptime=30

# Invoke-RemoteMaintenance.ps1 filters by PC type
.\Invoke-RemoteMaintenance.ps1 -PcType CMM -Task DiskCleanup
# Internally calls: GET /api.asp?action=getShopfloorPCs&pctypeid=2
```

---

## Database Schema Reference

The API interacts with these primary tables:

| Table | Purpose |
|-------|---------|
| `machines` | All PCs and equipment (pctypeid IS NOT NULL for PCs) |
| `communications` | Network interfaces (IP, MAC, gateway) |
| `dncconfig` | DNC/FTP configurations |
| `commconfig` | Communication port settings |
| `installedapps` | Application tracking |
| `pctype` | PC type definitions |
| `businessunits` | Business unit definitions |
| `printers` | Printer inventory |

**PC Identification:**
- PCs have `machinetypeid IN (33, 34, 35)`
- PCs have `pctypeid IS NOT NULL`
- Primary network is `isprimary = 1` in communications table