關於「遠端桌面」中的 SQLite
「遠端桌面」會將其收集的資訊儲存在 SQLite 資料庫中。
關於資料庫
「遠端桌面」會將系統資訊的資料擷取到一個稱為「systeminformation」的表格。它並不會保存歷程資料。新值會在每次收集資料時,覆寫先前擷取的資訊。如果你想要保存歷史資料,可以將資料擷取到第二個資料庫。資料庫中的資料有助於 macOS 的配置作業,因為 App 可以依據資料庫中的值來取用電腦記錄或同步特定機器的設定。
為實用性目的,你應該將資料庫視為唯讀。資料庫結構可能會為了符合較新版本的「遠端桌面」而更改,或「遠端桌面」在某些狀況下可能會清除資料庫來防止損毀。
SQLite 資料庫位於 /private/var/db/RemoteManagement/RMDB/rmdb.sqlite3。RMDB 目錄僅限於 daemon 使用者,所以存取檔案需要超級使用者(sudo)權限。當你使用 sudo 來進行指令前置作業,你會被提示詢問管理者密碼。
使用指令行擷取資料
Sqlite3 工具安裝在 /usr/bin/sqlite3。
Sqlite3 工具提供「遠端桌面」資料庫的表格和資料存取。以下指令會在「systeminformation」表格(即儲存你報告資料的表格)中傳送 SQL 查詢。
sudo sqlite3 /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "select * from systeminformation"以下是其輸出結果前幾行的範例。指令行可能會依據你的字型和視窗大小斷行。
00:0d:93:9c:0a:e4|Mac_HardDriveElement|DataDate|0|2008-12-11T23:05:58Z|2008-12-11T23:05:58Z00:0d:93:9c:0a:e4|Mac_HardDriveElement|Model|0|Hitachi HDS722580VLSA80|2008-12-11T23:05:58Z00:0d:93:9c:0a:e4|Mac_HardDriveElement|VolumeName|0|Server HD|2008-12-11T23:05:58Z00:0d:93:9c:0a:e4|Mac_HardDriveElement|JournalingIsActive|0|true|2008-12-11T23:05:58Z00:0d:93:9c:0a:e4|Mac_HardDriveElement|LastBackupDate|0|2008-09-05T18:13:22Z|2008-12-11T23:05:58Z00:0d:93:9c:0a:e4|Mac_HardDriveElement|LastModificationDate|0|2008-12-11T23:05:57Z|2008-12-11T23:05:58Z藉由 -separator 切換來調整欄之間的定義符號。此範例會使用逗號作為定義符號。
sudo sqlite3 -separator , /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "select * from systeminformation"其他方便的切換包括 -html、-line、-list 和 -column。如需更多資訊,請參閱 sqlite3 的 man 頁面。
此指令是以非常易讀的欄位輸出,並有欄位標題顯示。
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 \    "select * from systeminformation"ComputerID        ObjectName           PropertyName ItemSeq    Value                LastUpdated----------------- -------------------- ------------ ---------- -------------------- --------------------00:0d:93:9c:0a:e4 Mac_HardDriveElement DataDate     0          2008-12-11T23:05:58Z 2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement Model        0          Hitachi HDS722580VLS 2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement VolumeName   0          Server HD            2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement JournalingIs 0          true                 2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement LastBackupDa 0          2008-09-05T18:13:22Z 2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement LastModifica 0          2008-12-11T23:05:57Z 2008-12-11T23:05:58Z00:0d:93:9c:0a:e4 Mac_HardDriveElement TotalSize    0          80287128.000000      2008-12-11T23:05:58ZComputerID 欄位符合與報告資料相應的用戶端 MAC 位址,並可作為一項形成你的 SQL 查詢的獨有值。藉由修改以上範例的 SQL 來查看所有 objectname 類型:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 \    "SELECT distinct objectname FROM systeminformation"輸出:
ObjectName -------------------- Mac_HardDriveElementMac_NetworkInterfaceMac_SystemInfoElemenMac_RAMSlotElementMac_PCIBusElementMac_USBDeviceElementMac_FireWireDeviceEl每一個 objectname 可能有好幾個 propertyname 值。修改你的查詢會提供可用的 objectname/propertyname 組合:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 \    "SELECT distinct objectname, propertyname FROM systeminformation"輸出:
ObjectName           PropertyName -------------------- ------------Mac_HardDriveElement DataDateMac_HardDriveElement ModelMac_HardDriveElement VolumeNameMac_HardDriveElement JournalingIsMac_HardDriveElement LastBackupDaMac_HardDriveElement LastModifica這些屬性會整合起來幫助你構成一個擷取特定值的 SQL 查詢。例如,若要尋找用戶端序號,你可以使用:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "SELECT distinct computerid, propertyname, value FROM systeminformation WHERE propertyname = 'MachineSerialNumber'"ComputerID        PropertyName        Value ----------------- ------------------- ----------- 00:0d:93:9c:0a:e4 MachineSerialNumber QP4241FHPMZ 00:16:cb:a2:6d:1b MachineSerialNumber YM6090M9U39 00:16:cb:ca:81:52 MachineSerialNumber W862100NW92 00:17:f2:04:db:24 MachineSerialNumber G86492DVX68 00:14:51:22:28:38 MachineSerialNumber W854503QURC 00:17:f2:2b:b9:59 MachineSerialNumber 4H63861KVMM讓 SQL 查詢取得一份電腦名稱(而非乙太網路識別碼)列表和值:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "SELECT R1.value, R2.value FROM systeminformation R1, systeminformation R2 WHERE R1.computerid=R2.computerid AND R1.propertyname='ComputerName' AND R2.propertyName='MachineSerialNumber'"輸出:
Value      Value ---------- ----------- mini     X0XX234XYXYX mini       X01X23X4XXXX NetBoot001 X01X0101XXX0 Server     XX12345XXX6 Server2    PPYWWSSSEEER Aga O    X0XX123XY4XY透過 sqlite 指令使用 Automator
因為 Automator 可以執行 Shell 指令,因此它可以用來收集 sqlite3 輸出並將其傳送到其他 App。此範例收集報告資料並將其傳送到「文字編輯」:
on run {input, parameters}    return do shell script "/usr/bin/sqlite3 -separator " & quote & tab & quote & " /var/db/RemoteManagement/RMDB/rmdb.sqlite3 " & quote & input & quote with administrator privilegesend run工作流程將會提示詢問一個 SQL 指令然後使用「Run AppleScript」動作建立一個 Shell 指令,並帶出一個驗證對話框。本範例中的定義符號為 tab。
sqlite 查詢項目
可使用 sqlite 查詢以下項目:
Mac_SystemInfoElement
- ActiveProcessorCount 
- AppleTalkIsActive 
- ATADeviceCount 
- ARDComputerInfo1 
- ARDComputerInfo2 
- ARDComputerInfo3 
- ARDComputerInfo4 
- BootROMVersion 
- BusDataSize 
- BusSpeed 
- BusSpeedString 
- ComputerName 
- DataDate 
- En0Address 
- FileSharingIsEnabled 
- FireWireDeviceCount 
- FTPIsEnabled 
- HasKeyboardConnected 
- HasLightsOutController 
- HasMouseConnected 
- HasVectorProcessingUnit 
- KernelVersion 
- Level2CacheSize 
- MachineClass 
- MachineModel 
- MachineSerialNumber 
- MainMonitorDepth 
- MainMonitorHeight 
- MainMonitorType 
- MainMonitorWidth 
- ModemCountryInfo 
- ModemDescription 
- ModemDriverInfo 
- ModemInstalled 
- ModemInterfaceType 
- OpticalDriveType 
- PCISlotsUsedCount 
- PhysicalMemorySize 
- PrimaryIPAddress 
- PrimaryNetworkCollisions 
- PrimaryNetworkFlags 
- PrimaryNetworkHardwareAddress 
- PrimaryNetworkInputErrors 
- PrimaryNetworkInputPackets 
- PrimaryNetworkOutputErrors 
- PrimaryNetworkOutputPackets 
- PrimaryNetworkType 
- PrinterSharingEnabled 
- ProcessorCount 
- ProcessorSpeed 
- ProcessorSpeedString 
- ProcessorType 
- RemoteAppleEventsEnabled 
- RemoteLoginEnabled 
- SCSIDeviceCount 
- SelectedPrinterName 
- SelectedPrinterPostScriptVersion 
- SelectedPrinterType 
- SleepDisplayWhenInactive 
- SleepWhenInactive 
- SpinDownHardDrive 
- SystemVersion 
- SystemVersionString 
- TotalFreeHardDriveSpace 
- TotalHardDriveSpace 
- TotalRAMSlots 
- TotalSwapFileSize 
- TrashSize 
- UnixHostName 
- UnusedRAMSlots 
- USBDeviceCount 
- UserMemorySize 
- WakeOnLanEnabled 
- WebSharingIsEnabled 
- WindowsFileSharingEnabled 
- WirelessCardFirmwareVersion 
- WirelessCardHardwareAddress 
- WirelessCardIsActive 
- WirelessCardInstalled 
- WirelessCardLocale 
- WirelessCardType 
- WirelessChannelNumber 
- WirelessIsComputerToComputer 
- WirelessNetworkAvailable 
- WirelessNetworkName 
Mac_HardDriveElement
- CreationDate 
- DataDate 
- FileSystemType 
- FreeSpace 
- GroupName 
- IsBootVolume 
- IsCasePreserving 
- IsCaseSensitive 
- IsDetachable 
- IsWritable 
- JournalingIsActive 
- LastBackupDate 
- LastConsistencyCheckDate 
- LastModificationDate 
- LogicalUnitNumber 
- Manufacturer 
- Model 
- OwnerName 
- PermissionModes 
- PermissionsAreEnabled 
- Protocol 
- RemovableMedia 
- Revision 
- SerialNumber 
- SupportsJournaling 
- TotalFileCount 
- TotalFolderCount 
- TotalSize 
- UnixMountPoint 
- VolumeName 
Mac_NetworkInterfaceElement
- AllDNSServers 
- AllIPAddresses 
- ConfigurationName 
- ConfigurationType 
- DataDate 
- DomainName 
- EthernetAlignmentErrors 
- EthernetCarrierSenseErrors 
- EthernetChipSet 
- EthernetCollisionFrequencies 
- EthernetDeferredTransmissions 
- EthernetExcessiveCollisions 
- EthernetFCSErrors 
- EthernetFrameTooLongs 
- EthernetInternalMacRxErrors 
- EthernetInternalMacTxErrors 
- EthernetLateCollisions 
- EthernetMissedFrames 
- EthernetMultipleCollisionFrames 
- EthernetRxCollisionErrors 
- EthernetRxFrameTooShorts 
- EthernetRxInterrupts 
- EthernetRxOverruns 
- EthernetRxPHYTransmissionErrors 
- EthernetRxResets 
- EthernetRxResourceErrors 
- EthernetRxTimeouts 
- EthernetRxWatchdogTimeouts 
- EthernetSingleCollisionFrames 
- EthernetSQETestErrors 
- EthernetTxInterrupts 
- EthernetTxJabberEvents 
- EthernetTxPHYTransmissionErrors 
- EthernetTxResets 
- EthernetTxResourceErrors 
- EthernetTxTimeouts 
- EthernetTxUnderruns 
- HardwareAddress 
- InterfaceFlags 
- InterfaceName 
- IsActive 
- IsPrimary 
- NetworkCollisions 
- NetworkInputErrors 
- NetworkInputPackets 
- NetworkOutputErrors 
- NetworkOutputPackets 
- OutputQueueCapacity 
- OutputQueueDropCount 
- OutputQueueOutputCount 
- OutputQueuePeakSize 
- OutputQueueRetryCount 
- OutputQueueSize 
- OutputQueueStallCount 
- PrimaryDNSServer 
- PrimaryIPAddress 
- RouterAddress 
Mac_USBDeviceElement
- BusPower 
- DataDate 
- DeviceSpeed 
- ProductID 
- ProductName 
- SerialNumber 
- VendorID 
Mac_FireWireDeviceElement
- DataDate 
- DeviceSpeed 
- Manufacturer 
- Model 
Mac_RAMSlotElement
- DataDate 
- MemoryModuleSize 
- MemoryModuleSpeed 
- MemoryModuleSpeed 
- MemoryModuleSpeed 
Mac_PCIBusElement
- CardMemory 
- CardName 
- CardRevision 
- CardType 
- DataDate 
- DeviceID 
- RomRevision 
- SlotName 
- VendorID