About SQLite in Remote Desktop

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 macOS 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).

Here is an example of the first few lines of output. The lines may wrap depending on your font and window sizes.

Adjust the delimiter used between columns with the -separator switch. This sample uses a comma delimiter.

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.

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:

Outputs:

For each objectname, there are a number of propertyname values possible. Modifying your query provides the available objectname/propertyname pairs:

Outputs:

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:

Make the SQL query get a list of the computers’ names (instead of Ethernet IDs) and values:

Outputs:

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:

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

The following items can be queried using 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