shopdb/docs/archive/INVENTORY_COLUMN_MAPPING.md

# Inventory.xlsx Column Mapping to Database

**Date:** 2025-11-06
**Coverage:** 100% (35/35 columns)

---

## Complete Column Mapping

| # | Inventory Column | Database Location | Notes |
|---|------------------|-------------------|-------|
| 1 | Operational Status | `machines.machinestatusid` → `machinestatus.machinestatus` | |
| 2 | OT Location Name | `machines.businessunitid` → `businessunits.businessunit` | |
| 3 | OT Location Liasion or Site OT Asset Manager | `businessunits.liaisonname` | |
| 4 | System Name | `machines.machinenumber` | |
| 5 | Hostname | `machines.hostname` | For PCs |
| 6 | OS Name | `machines.osid` → `operatingsystems.osname` (PCs) OR `machines.controllerosid` → `operatingsystems.osname` (CNCs) | Same table for both! |
| 7 | Serial # | `machines.serialnumber` | |
| 8 | Manufacturer | `machines.modelnumberid` → `models.vendorid` → `vendors.vendor` | |
| 9 | Model | `machines.modelnumberid` → `models.modelnumber` | |
| 10 | Ge Coreload | `compliance.gecoreload` | |
| 11 | Device Description | `machines.alias` OR `machinetypes.machinetype` | |
| 12 | Device Type | `machines.machinetypeid` → `machinetypes.machinetype` | |
| 13 | IP Address | `communications.address` (WHERE `comstypeid=1`) | First IP record |
| 14 | IP Address (interface 2) | `communications.address` | Second IP record |
| 15 | IP Address (Interface 3) | `communications.address` | Third IP record |
| 16 | MAC Address (interface1) | `communications.macaddress` | First MAC record |
| 17 | MAC address (Interface 2) | `communications.macaddress` | Second MAC record |
| 18 | MAC Address (Interface 3) | `communications.macaddress` | Third MAC record |
| 19 | On GE Network | `compliance.ongenetwork` | |
| 20 | Vlan | `communications.settings` JSON | `{"vlan": "100"}` |
| 21 | Asset Criticality(L/M/H) | `compliance.assetcriticality` | |
| 22 | CUI/CMMC Data Classification | `compliance.cuidataclassification` | |
| 23 | DoD Asset Type | `compliance.dodassettype` | |
| 24 | DoD Asset Sub-Type | `compliance.dodassetsubtype` | |
| 25 | OT Environment | `compliance.otenvironment` | |
| 26 | 3rd Party (Other) Managed (Y/N) | `compliance.managedbyvendorid` → `vendors.vendor` | FK to vendors table |
| 27 | Change Restricted (Y/N) | `compliance.changerestricted` | |
| 28 | Other Deployment Notes | `machines.machinenotes` | |
| 29 | Jump_Box | `compliance.jumpbox` | |
| 30 | MFT | `compliance.mft` | Managed File Transfer |
| 31 | Scan_Date_Status | `compliancescans.scanstatus` (latest) | Most recent scan |
| 32 | OT Asset Fields | `machinerelationships` table | "WJ 2023 WJ 2024" = dualpath relationship |
| 33 | Controller_Type1 | `machines.controllertypeid` → `controllertypes.controllertype` | For CNC machines |
| 34 | Controller_OS1 | `machines.controllerosid` → `operatingsystems.osname` | For CNC machines |
| 35 | PC_Type1 | `machines.machinetypeid` → `machinetypes.machinetype` | For PCs |

---

## Special Mapping Notes

### OT Asset Fields (Column 32) - Machine Relationships

**Inventory Pattern:**
```
Row 1: Machine 2023 (192.168.*.*)     | OT Asset Fields: "WJ 2023 WJ 2024"
Row 2: PC / Attached PC (10.134.*.*)  | OT Asset Fields: "WJ 2023 WJ 2024"
Row 3: Machine 2024 (192.168.*.*)     | OT Asset Fields: "WJ 2023 WJ 2024"
Row 4: PC / Attached PC (10.134.*.*)  | OT Asset Fields: "WJ 2023 WJ 2024"
```

**Database Representation:**
```sql
-- Machines 2023 and 2024 are dualpath
INSERT INTO machinerelationships (relationshiptypeid, machineid1, machineid2)
VALUES (1, 2023, 2024);

-- PC controls machine 2023
INSERT INTO machinerelationships (relationshiptypeid, machineid1, machineid2)
VALUES (2, 2023, [pc_machineid]);

-- PC controls machine 2024
INSERT INTO machinerelationships (relationshiptypeid, machineid1, machineid2)
VALUES (2, 2024, [pc_machineid]);
```

**Relationship Types:**
- relationshiptypeid = 1: Dualpath (machines share controller)
- relationshiptypeid = 2: Controlled By (machine controlled by PC)

---

### IP Address Pattern Recognition

**Machine IPs:** `192.168.*.*` → CNC machines
**PC IPs:** `10.134.*.*` → Controlling PCs

**System Name Pattern:**
- Machine: `2023`
- PC: `2023 / Attached PC` or `IT-LAB-01`

---

## Export Query for inventory.xlsx Format

```sql
SELECT
    -- Columns 1-12: Basic Info
    ms.machinestatus AS 'Operational Status',
    bu.businessunit AS 'OT Location Name',
    bu.liaisonname AS 'OT Location Liasion or Site OT Asset Manager',
    m.machinenumber AS 'System Name',
    m.hostname AS 'Hostname',
    COALESCE(os_pc.osname, os_ctrl.osname) AS 'OS Name',
    m.serialnumber AS 'Serial #',
    v.vendor AS 'Manufacturer',
    mo.modelnumber AS 'Model',
    comp.gecoreload AS 'Ge Coreload',
    m.alias AS 'Device Description',
    mt.machinetype AS 'Device Type',

    -- Columns 13-20: Network
    c_ip1.address AS 'IP Address',
    c_ip2.address AS 'IP Address (interface 2)',
    c_ip3.address AS 'IP Address (Interface 3)',
    c_mac1.macaddress AS 'MAC Address (interface1)',
    c_mac2.macaddress AS 'MAC Address (interface 2)',
    c_mac3.macaddress AS 'MAC Address (Interface 3)',
    comp.ongenetwork AS 'On GE Network',
    c_ip1.settings->>'$.vlan' AS 'Vlan',

    -- Columns 21-32: Compliance
    comp.assetcriticality AS 'Asset Criticality(L/M/H)',
    comp.cuidataclassification AS 'CUI/CMMC Data Classification',
    comp.dodassettype AS 'DoD Asset Type',
    comp.dodassetsubtype AS 'DoD Asset Sub-Type',
    comp.otenvironment AS 'OT Environment',
    mv.vendor AS '3rd Party (Other) Managed (Y/N)',
    comp.changerestricted AS 'Change Restricted (Y/N)',
    m.machinenotes AS 'Other Deployment Notes',
    comp.jumpbox AS 'Jump_Box',
    comp.mft AS 'MFT',
    (SELECT scanstatus FROM compliancescans cs
     WHERE cs.machineid = m.machineid
     ORDER BY scandate DESC LIMIT 1) AS 'Scan_Date_Status',

    -- Column 32: OT Asset Fields (relationships)
    GROUP_CONCAT(DISTINCT
        CONCAT(mt_rel.machinetype, ' ', m_rel.machinenumber)
        SEPARATOR ' '
    ) AS 'OT Asset Fields',

    -- Columns 33-35: Controller/Type
    ct.controllertype AS 'Controller_Type1',
    os_ctrl.osname AS 'Controller_OS1',
    mt.machinetype AS 'PC_Type1'

FROM machines m
LEFT JOIN machinetypes mt ON m.machinetypeid = mt.machinetypeid
LEFT JOIN machinestatus ms ON m.machinestatusid = ms.machinestatusid
LEFT JOIN businessunits bu ON m.businessunitid = bu.businessunitid
LEFT JOIN models mo ON m.modelnumberid = mo.modelnumberid
LEFT JOIN vendors v ON mo.vendorid = v.vendorid
LEFT JOIN operatingsystems os_pc ON m.osid = os_pc.osid
LEFT JOIN controllertypes ct ON m.controllertypeid = ct.controllertypeid
LEFT JOIN operatingsystems os_ctrl ON m.controllerosid = os_ctrl.osid

-- Compliance
LEFT JOIN compliance comp ON m.machineid = comp.machineid
LEFT JOIN vendors mv ON comp.managedbyvendorid = mv.vendorid

-- Communications (IP addresses)
LEFT JOIN (SELECT * FROM communications WHERE comstypeid IN (1,3) ORDER BY comid) c_ip1
    ON m.machineid = c_ip1.machineid AND c_ip1.rn = 1
LEFT JOIN (SELECT * FROM communications WHERE comstypeid IN (1,3) ORDER BY comid) c_ip2
    ON m.machineid = c_ip2.machineid AND c_ip2.rn = 2
LEFT JOIN (SELECT * FROM communications WHERE comstypeid IN (1,3) ORDER BY comid) c_ip3
    ON m.machineid = c_ip3.machineid AND c_ip3.rn = 3

-- MAC addresses
LEFT JOIN (SELECT * FROM communications WHERE macaddress IS NOT NULL ORDER BY comid) c_mac1
    ON m.machineid = c_mac1.machineid AND c_mac1.rn = 1
LEFT JOIN (SELECT * FROM communications WHERE macaddress IS NOT NULL ORDER BY comid) c_mac2
    ON m.machineid = c_mac2.machineid AND c_mac2.rn = 2
LEFT JOIN (SELECT * FROM communications WHERE macaddress IS NOT NULL ORDER BY comid) c_mac3
    ON m.machineid = c_mac3.machineid AND c_mac3.rn = 3

-- Relationships (for OT Asset Fields)
LEFT JOIN machinerelationships mr ON (
    mr.machineid1 = m.machineid OR mr.machineid2 = m.machineid
)
LEFT JOIN machines m_rel ON (
    CASE
        WHEN mr.machineid1 = m.machineid THEN mr.machineid2
        ELSE mr.machineid1
    END = m_rel.machineid
)
LEFT JOIN machinetypes mt_rel ON m_rel.machinetypeid = mt_rel.machinetypeid

WHERE m.isactive = 1
GROUP BY m.machineid;
```

---

## Coverage Summary

 **100% Coverage** - All 35 inventory columns mapped to database

**Storage Distribution:**
- `machines` table: 11 columns
- `compliance` table: 9 columns
- `communications` table: 8 columns (includes VLAN in JSON)
- `businessunits` table: 2 columns
- `machinerelationships` table: 1 column (OT Asset Fields)
- `compliancescans` table: 1 column
- Various lookup tables: 3 columns

**Design Benefits:**
-  Normalized structure (no redundancy)
-  Flexible relationships (dualpath, controller associations)
-  Extensible (easy to add new fields/types)
-  Complete audit trail
-  Single source of truth