powershell-scripts/docs/ShopDB-API.md

ShopDB API Reference

REST API for PowerShell data collection scripts and ShopDB integrations.

Table of Contents

• Overview
• Base URL
• Authentication
• Endpoints
  • GET Endpoints
  • POST Endpoints
• Response Format
• Error Handling
• 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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

[
  {
    "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:

{
  "site": "WJF",
  "cnc": "FANUC",
  "ncif": "FOCAS2",
  "machineNo": "M0612",
  "ftpPrimary": "10.134.50.10",
  "ftpSecondary": "10.134.50.11"
}

Installed Apps JSON Format:

[
  {
    "appid": 5,
    "appname": "PC-DMIS",
    "version": "2023.1",
    "isactive": 1
  }
]

Response:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "success": true,
  "updated": 5
}

---

Response Format

All responses are JSON with consistent structure:

Success Response:

{
  "success": true,
  "message": "Operation completed",
  "data": [...]
}

Error Response:

{
  "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

# 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

# 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

# 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