<# .SYNOPSIS Maintains a CSV database of USB storage devices and their sync directories, preserving manual edits. .DESCRIPTION This script: 1. Detects currently connected USB storage devices using PnP. 2. Matches devices against a predefined mapping table ($deviceMappings) to assign source/target sync directories. 3. Updates a CSV file (DeviceMapping.csv) with new devices while preserving existing directory mappings. 4. Only populates empty Model/SourceDirectory/TargetDirectory fields - never overwrites user-defined values. .KEY BEHAVIORS - New devices: Added to CSV with "NotMapped" if no predefined mapping exists - Existing devices: Blank fields are auto-filled, populated fields are preserved - Model names: Extracted from DeviceID (PROD_* segment) .USAGE Run periodically or on USB insertion to keep CSV updated. Another script can use this CSV to perform actual file synchronization. .NOTES CSV format: Model,DeviceID,FriendlyName,SourceDirectory,TargetDirectory #> # Define the mappings for DeviceID to SourceDirectory and TargetDirectory $deviceMappings = @{ "USBSTOR\DISK&VEN_FAKE&PROD_FLASH_DRIVE&REV_2.10\SN1234567890&0" = @{ SourceDirectory = "DataToSync" TargetDirectory = "C:\Backup\USBFlashDrive" } "USBSTOR\DISK&VEN_GENERIC&PROD_USB_DISK&REV_6.50\4&ABCDEF12&0&_&0" = @{ SourceDirectory = "Test" TargetDirectory = "C:\Flash\GeneralUDisk\Test" } "USBSTOR\DISK&VEN_VIRTUAL&PROD_STORAGE&REV_0001\ZZ9012345678&0" = @{ SourceDirectory = "ImportantDocs" TargetDirectory = "C:\Archive\ST8GBDrive" } } # Function to extract the model from the DeviceID function Get-ModelFromDeviceID { param ( [string]$DeviceID ) # Example regex to extract the model from DeviceID # This regex is a placeholder and may need to be adjusted based on the actual DeviceID format $match = [regex]::Match($DeviceID, 'PROD_(?[^&]+)') if ($match.Success) { return $match.Groups['Model'].Value } else { return "UnknownModel" } } # Get the USB devices and create custom objects with the required properties $usbDevices = Get-PnpDevice | Where-Object { $_.DeviceID -like "USBSTOR*" } $newData = foreach ($device in $usbDevices) { $friendlyName = $device.FriendlyName $deviceId = $device.DeviceID $model = Get-ModelFromDeviceID -DeviceID $deviceId if ($deviceMappings.ContainsKey($deviceId)) { [PSCustomObject]@{ Model = $model FriendlyName = $friendlyName DeviceID = $deviceId SourceDirectory = $deviceMappings[$deviceId].SourceDirectory TargetDirectory = $deviceMappings[$deviceId].TargetDirectory } } else { [PSCustomObject]@{ Model = $model FriendlyName = $friendlyName DeviceID = $deviceId SourceDirectory = "NotMapped" TargetDirectory = "NotMapped" } } } # Path to the CSV file $csvPath = "C:\Users\J2897\Code\PowerShell\DriveSync\DeviceMapping.csv" # Load the existing CSV file if it exists if (Test-Path $csvPath) { $existingData = Import-Csv -Path $csvPath } else { $existingData = @() } # Create a hashtable to map DeviceID to existing data $existingDataHashtable = @{} foreach ($item in $existingData) { $existingDataHashtable[$item.DeviceID] = $item } # Update the existing data with new data foreach ($newItem in $newData) { $deviceId = $newItem.DeviceID if ($existingDataHashtable.ContainsKey($deviceId)) { $existingItem = $existingDataHashtable[$deviceId] # Update only if the existing cells are missing or blank if ([string]::IsNullOrEmpty($existingItem.Model)) { $existingItem.Model = $newItem.Model } if ([string]::IsNullOrEmpty($existingItem.SourceDirectory)) { $existingItem.SourceDirectory = $newItem.SourceDirectory } if ([string]::IsNullOrEmpty($existingItem.TargetDirectory)) { $existingItem.TargetDirectory = $newItem.TargetDirectory } } else { # Add new item if it doesn't exist $existingData += $newItem } } # Export the updated data to the CSV file $existingData | Export-Csv -Path $csvPath -NoTypeInformation