Remote Desktop SQLite
Remote Desktop stores information it collects in a SQLite database.
About the database
Remote Desktop captures system information data to a table called “systeminformation.” It doesn’t keep historical data. New values overwrite previously captured information whenever data is collected. If you want to preserve the historical data, you could capture the data to a second database. The data in the database can also help with OS X deployment, because apps can access computer records or synchronize machine-specific settings based on values in the database.
For practical purposes, you should treat the database as read-only. The database structure might change to meet the needs of newer versions of Remote Desktop, or Remote Desktop will, in some circumstances, purge the database to prevent corruption.
The SQLite database is located in /private/var/db/RemoteManagement/RMDB/rmdb.sqlite3. The RMDB directory is limited to the daemon user, so superuser (sudo) access is required to access the files. When you use sudo to preface the commands, you’re prompted for your administrator password.
Retrieve data using the command line
The sqlite3 tool is installed in /usr/bin/sqlite3.
The sqlite3 tool provides access to the tables and data in the Remote Desktop database. The following sends an SQL query in the “systeminformation” table (the table that stores your report data).
sudo sqlite3 /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "select * from systeminformation"Here is an example of the first few lines of output. The lines may wrap depending on your font and window sizes.
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
Adjust the delimiter used between columns with the -separator switch. This sample uses a comma delimiter.
sudo sqlite3 -separator , /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "select * from systeminformation"Other handy switches include -html, -line, -list, and -column. For more details, see the man page for sqlite3.
This command outputs in very readable columns and displays the column headers.
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:58Z
The ComputerID field matches the Ethernet MAC address of the client that responded with report data, and can serve as a unique value to form your SQL queries. See all objectname types by modifying the SQL from the example above to say:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "SELECT distinct objectname FROM systeminformation"
Outputs:
ObjectName -------------------- Mac_HardDriveElementMac_NetworkInterfaceMac_SystemInfoElemenMac_RAMSlotElementMac_PCIBusElementMac_USBDeviceElementMac_FireWireDeviceEl
For each objectname, there are a number of propertyname values possible. Modifying your query provides the available objectname/propertyname pairs:
sudo sqlite3 -header -column /var/db/RemoteManagement/RMDB/rmdb.sqlite3 "SELECT distinct objectname, propertyname FROM systeminformation"
Outputs:
ObjectName PropertyName -------------------- ------------Mac_HardDriveElement DataDateMac_HardDriveElement ModelMac_HardDriveElement VolumeNameMac_HardDriveElement JournalingIsMac_HardDriveElement LastBackupDaMac_HardDriveElement LastModifica
These properties combine to help you form an SQL query that retrieves a particular value. For example, to find client serial numbers, you could use:
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
Make the SQL query get a list of the computers’ names (instead of Ethernet IDs) and values:
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'"Outputs:
Value Value ---------- ----------- Xserve QP4241FHPMZ mini YM6090M9U39 NetBoot001 W862100NW92 Server G86492DVX68 Server2 W854503QURC Wendy G 4H63861KVMM
Use Automator with the sqlite command
Because Automator can execute shell commands, it can be used to collect sqlite3 output and send it to other apps. This example collects report data and sends it to TextEdit:
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
The workflow prompts for an SQL command and then builds a shell command using the Run AppleScript action, which can bring up an authentication dialog. The delimiter in the example is a tab.
sqlite query items
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 |
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_SystemInfoElement |
|---|
ActiveProcessorCount |
AppleTalkIsActive |
ATADeviceCount |
BootROMVersion |
BusDataSize |
BusSpeed |
BusSpeedString |
ComputerName |
DataDate |
En0Address |
FileSharingIsEnabled |
FireWireDeviceCount |
FTPIsEnabled |
HasKeyboardConnected |
HasLightsOutController |
HasMouseConnected |
HasVectorProcessingUnit |
KernelVersion |
Level2CacheSize |
MachineClass |
MachineIsNetBooted |
MachineModel |
MachineSerialNumber |
MainMonitorDepth |
MainMonitorHeight |
MainMonitorType |
MainMonitorWidth |
ModemDescription |
ModemDriverInfo |
ModemInstalled |
ModemInterfaceType |
OpticalDriveType |
PCISlotsUsedCount |
PhysicalMemorySize |
PrimaryIPAddress |
PrimaryNetworkCollisions |
PrimaryNetworkFlags |
PrimaryNetworkHardwareAddress |
PrimaryNetworkInputErrors |
PrimaryNetworkInputPackets |
PrimaryNetworkOutputErrors |
PrimaryNetworkOutputPackets |
PrimaryNetworkType |
PrinterSharingEnabled |
ProcessorCount |
ProcessorSpeed |
ProcessorSpeedString |
ProcessorType |
RemoteAppleEventsEnabled |
RemoteLoginEnabled |
SCSIDeviceCount |
SleepDisplayWhenInactive |
SleepWhenInactive |
SpinDownHardDrive |
SystemVersion |
SystemVersionString |
TotalFreeHardDriveSpace |
TotalHardDriveSpace |
TotalRAMSlots |
TotalSwapFileSize |
TrashSize |
UnixHostName |
UnusedRAMSlots |
USBDeviceCount |
UserMemorySize |
WakeOnLanEnabled |
WebSharingIsEnabled |
WindowsFileSharingEnabled |
WirelessCardInstalled |
Mac_RAMSlotElement |
|---|
DataDate |
MemoryModuleSize |
MemoryModuleSpeed |
MemoryModuleType |
SlotIdentifier |
Mac_PCIBusElement |
|---|
CardMemory |
CardName |
CardRevision |
CardType |
DataDate |
DeviceID |
RomRevision |
SlotName |
VendorID |
Mac_NetworkInterfaceElement |
|---|
DomainName |
Mac_SystemInfoElement |
|---|
WirelessCardFirmwareVersion |
WirelessCardHardwareAddress |
WirelessCardIsActive |
WirelessCardLocale |
WirelessCardType |
Mac_USBDeviceElement |
|---|
BusPower |
DataDate |
DeviceSpeed |
ProductID |
ProductName |
SerialNumber |
VendorID |
Mac_SystemInfoElement |
|---|
WirelessNetworkAvailable |
Mac_FireWireDeviceElement |
|---|
DataDate |
DeviceSpeed |
Manufacturer |
Model |
Mac_SystemInfoElement |
|---|
ARDComputerInfo1 |
ARDComputerInfo2 |
ARDComputerInfo3 |
ARDComputerInfo4 |
ModemCountryInfo |
SelectedPrinterName |
SelectedPrinterPostScriptVersion |
SelectedPrinterType |
WirelessChannelNumber |
WirelessIsComputerToComputer |
WirelessNetworkName |