Apple Developer Connection
Advanced Search
Member Login Log In | Not a Member? Support

Providing Application Access to SQL Data in Apple Remote Desktop 2

Apple Remote Desktop 2 stores the system information it collects in an PostgreSQL database (included with Apple Remote Desktop 2). This data is accessible with industry-standard database tools, which means you can tap into the database and use the results of your system information reports in other applications.

This article shows you how to get started working with the Apple Remote Desktop 2 data in the PostgreSQL database. To do this effectively, you will need at least some experience with Structured Query Language (SQL), and either command-line experience, or experience with a graphical database query tool that can access PostgreSQL databases.

NOTE: With the release of Apple Remote Desktop 3 the default database access settings have changed slightly from 2.x, in order to be more compatible with an existing PostgreSQL installation and to be more secure:

  • In ARD 3, the default port is now 5433. (A default PostgreSQL install uses 5432)
  • The default username for the database is still 'ard'. However, the default password is now created randomly when the database is first run. The username and password are stored in /private/var/db/RemoteManagement/RMDB/passwd.txt. The password is displayed in plain text in the file.

Working with Apple Remote Desktop 2

Apple Remote Desktop 2 is a powerful desktop management solution for system administrators in education, the enterprise and help desk administration. You can distribute software and configure systems to client Macs on your network, as well as provide real-time online help remotely. Apple Remote Desktop 2 generates a lot of valuable information about the machines you are managing, and you can access that information, either through command-line utilities to create in-house archives and reports, or even full-blown applications that can be used internally or made available to a wider public.

This article shows you how to access the data in Apple Remote Desktop 2's PostgreSQL database using the command line, and make that information available to your outside application or utility. It includes building a sample application with REALbasic to access and display the data, including a simple user interface. From there, you can no doubt find ways to use, manipulate and display that information as suits your purposes, or those of your users.

The Database

Apple Remote Desktop 2 captures system information data to a table called systeminformation. It does not keep historical data. New values will overwrite previously captured information when a new report is run. One possible use for external access to the database might be to capture this data to a second database, preserving historical data. Apple Remote Desktop 2 system information can also be useful to aid with Mac OS X deployment. Applications can access computer records or synchronize machine-specific settings based on values in the database. Another Mike Bombich application, NetRestore, is an example of such an application.

The techniques in this document will enable both read and write access to the Apple Remote Desktop 2 database, but for practical purposes you should treat it as read-only. The database structure could change to meet the needs of newer versions of Apple Remote Desktop and Apple Remote Desktop 2 will, in some circumstances, purge the database in order to prevent corruption (see the Resources section below for relevant Kbase articles).

The Pieces

The PostgreSQL database, tools, and processes may be installed in directories other than those found in a standard PostgreSQL installation. One advantage to this is Apple Remote Desktop 2 does not overwrite or conflict with a previous Postgres installation. The following chart will help you identify the components included with Apple Remote Desktop 2.

TOPIC LINK
Database tools

/System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin

System information database

/var/db/RemoteManagement/RMDB/rmdb.data/

Configuration files

/var/db/RemoteManagement/RMDB/rmdb.data/postgresql.conf
/var/db/RemoteManagement/RMDB/rmdb.data/pg_hba.conf

Process

postmaster -D /var/db/RemoteManagement/RMDB/rmdb.data

Table 1: Database Tools, Files and Process Included with Apple Remote Desktop 2.

Enabling Access to the Database

For security reasons, access to the Apple Remote Desktop 2 database is restricted to the Apple Remote Desktop 2 application. To allow external applications to access the data, you will need to modify two PostgreSQL configuration files. The first file will establish an account and password to access the database, and the second will enable database access via TCP/IP.

To make the changes manually, first launch the Remote Desktop application and make sure it is running properly in its default state. Then, quit Remote Desktop, and stop the Apple Remote Desktop client in the Sharing pane of System Preferences.

Next, we want to edit the pg_hba.conf file to establish an account. The complete path for the file is:

/var/db/RemoteManagement/RMDB/rmdb.data/pg_hba.conf

You need to be root, or use the sudo command, to edit this file. Open the file with your favorite text editor and add the following line to the end. Once this is done, save the file.

host ard ard 127.0.0.1 255.255.255.255 password

This line configures an account named "ard" with a password of "ard" to access the database (named ard). Also, only connections from applications on the machine hosting the PostgreSQL database will be allowed.

Now we want to edit the postgresql.conf file to enable TCP/IP access to the database. The complete path for this file is:

/var/db/RemoteManagement/RMDB/rmdb.data/postgresql.conf

You will need to be root, or use the sudo command, to edit this file. Open the file with your favorite text editor and find the line that says:

#tcpip_socket = false

Remove the # symbol from the beginning of the line and change the value from false to true. The line should now look like this:

tcpip_socket = true

Once this is done, save the file. It is now safe to start up the Apple Remote Desktop client in the Sharing pane of System Preferences. You should also launch the Remote Desktop application and run a System Overview report on at least one client system to populate your database with some data.

Retrieving Data From the Command Line

PostgreSQL includes a command-line tool called psql that can be used to retrieve data from the database. The following will send an SQL query to reveal all data for all fields in the systeminformation table, the table that stores your report data. It will prompt you for the password (we set it above to ard, as you will remember) and then dump the data to the screen. The psql command is located in /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin and will be used several times in the following examples. You need to be in that directory (or add it to your path) before typing the examples below.

./psql -U ard -c "SELECT * FROM systeminformation" ard

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

    computerid     |  objectname      | propertyname   |itemseq|        value         |      lastupdated
-------------------+------------------+----------------+-------+----------------------+-----------------------
 00:0a:95:a8:9e:7c | Mac_DriveElement | DataDate       |     0 | 2005-02-07T19:43:07Z | 2005-02-07 19:43:07-08
 00:0a:95:a8:9e:7c | Mac_DriveElement | CreationDate   |     0 | 2004-08-26T01:11:57Z | 2005-02-07 19:43:07-08
 00:0a:95:a8:9e:7c | Mac_DriveElement | FileSystemType |     0 | 18475                | 2005-02-07 19:43:07-08
 00:0a:95:a8:9e:7c | Mac_DriveElement | FreeSpace      |     0 | 31404100             | 2005-02-07 19:43:07-08
 00:0a:95:a8:9e:7c | Mac_DriveElement | GroupName      |     0 | admin                | 2005-02-07 19:43:07-08
 00:0a:95:a8:9e:7c | Mac_DriveElement | IsBootVolume   |     0 | true                 | 2005-02-07 19:43:07-08
 

With a small change, you can force the output into a tab-separated file:

./psql -A -F $'\t' -U ard -c "select * from systeminformation" ard > /tmp/mytestfile.txt

You can also add the HTML output option by adding the -H switch:

./psql -H -U ard -c "SELECT * FROM systeminformation" ard

The first thing you should notice is the computerid field. This 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. There are currently a handful of objectname types. You can see them all by modifying the SQL from the psql example above to say:

"SELECT distinct objectname FROM systeminformation"

Your results should look something like this:

         objectname          
-----------------------------
 Mac_HardDriveElement
 Mac_NetworkInterfaceElement
 Mac_PCIBusElement
 Mac_RAMSlotElement
 Mac_SystemInfoElement
 Mac_USBDeviceElement

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

"SELECT distinct objectname, propertyname FROM systeminformation"

Your psql output should start with something like this:

         objectname          |          propertyname           
-----------------------------+---------------------------------
 Mac_HardDriveElement        | CreationDate
 Mac_HardDriveElement        | DataDate
 Mac_HardDriveElement        | FileSystemType
 Mac_HardDriveElement        | FreeSpace
 Mac_HardDriveElement        | GroupName
 Mac_HardDriveElement        | IsBootVolume
 Mac_HardDriveElement        | IsCasePreserving
 Mac_HardDriveElement        | IsCaseSensitive
 Mac_HardDriveElement        | IsWritable
 Mac_HardDriveElement        | JournalingIsActive
 Mac_HardDriveElement        | LastBackupDate
 Mac_HardDriveElement        | LastConsistencyCheckDate
 Mac_HardDriveElement        | LastModificationDate
 Mac_HardDriveElement        | OwnerName
 Mac_HardDriveElement        | PermissionModes
 Mac_HardDriveElement        | PermissionsAreEnabled
 Mac_HardDriveElement        | RemovableMedia
 Mac_HardDriveElement        | SupportsJournaling
 Mac_HardDriveElement        | TotalFileCount
 Mac_HardDriveElement        | TotalFolderCount
 Mac_HardDriveElement        | TotalSize
 Mac_HardDriveElement        | UnixMountPoint
 Mac_HardDriveElement        | VolumeName
 Mac_NetworkInterfaceElement | AllBroadcastAddresses
 Mac_NetworkInterfaceElement | AllDNSServers

These properties will combine to help you form an SQL query that retrieves a particular value. If, for example, we want to find the serial numbers of our clients, we could use:

"SELECT distinct computerid, propertyname, value FROM systeminformation 
WHERE propertyname = 'MachineSerialNumber'"

You should see something like this:

    computerid     |    propertyname     |    value    
-------------------+---------------------+-------------
 00:0a:95:a8:9e:7c | MachineSerialNumber | XB343106NVS
 00:30:65:f6:40:b6 | MachineSerialNumber | UV117041KB7

Finally, you can use the following SQL command to get a list with the computers' names (instead of MAC addresses) and values:

"SELECT R1.value, R2.value FROM systeminformation R1, systeminformation R2 WHERE R1.computerid=R2.computerid 
AND R1.propertyname='ComputerName' AND R2.propertyName='MachineSerialNumber'"

Should give you results like:

value  |    value    
--------+-------------
 g5     | XB343106NVS
 pbg4   | UV117041KB7

Connecting with REALbasic

REALbasic is a powerful, easy-to-use application development tool that allows you to build (among other things) applications that talk to a PostgreSQL database, using an included database plugin.

Once you have copied the PostgreSQL Plugin into the REALbasic Plugins folder, you can add the Apple Remote Desktop 2 database as a data source for your project.

To do this, open the File menu, choose the Add Data Source submenu, and select PostgreSQL Database..., as shown in Figure 1.

the Add Data Source submenu

Figure 1: The Add Data Source Submenu.

In the resulting dialog, set the Database, User, and Password fields to ard, as in Figure 2.

Selecting the Database

Figure 2: Selecting the Database.

You can now double-click the ard data source in your project window and use the View Data button to run sample SQL queries against the systeminformation table, as shown in Figure 3.

Viewing the Query Output

Figure 3: Viewing the Query Output.

You can also easily create a simple user interface to this data. Start by opening the Window1 window in a new REALbasic project and drag a button and listbox to the window from the Controls palette. Name the button (our example uses Go) and stretch the listbox to fill the window appropriately.

Now, double-click the button to add some code. The following code entered in the Action event window fills the listbox with the MAC address and Computer Name of all computers in your ard data source:

  
Sub Action()

  Dim myRecordSet as RecordSet
  Dim i as Integer
  
  myRecordSet = ard.SQLSelect("SELECT computerid, value from systeminformation where
  propertyname='ComputerName'")
  
  While Not myRecordSet.eof
    
    ListBox1.ColumnCount = 2
    Listbox1.AddRow myRecordSet.IdxField(1).StringValue
    ListBox1.Cell( ListBox1.LastIndex, 1 ) = myRecordSet.IdxField(2).StringValue
    
    myRecordSet.MoveNext
    
  Wend
  
End Sub

Now compile and run your new application; you should get some results as shown in Figure 4 when you push the Go button:

he New Interface for the Output Showing Located Systems

Figure 4: The New Interface for the Output Showing Located Systems.

This gives you the feel for working with the database information in REALbasic; from here, you should be able to find ways to display and work with the data using the tools and techniques that are right for you.

Wrap-up

Once you have opened access to your Apple Remote Desktop 2 database, any number of possibilities are open to you. This document has focused on command-line options, but dozens of graphical tools exist that can connect to and manage PostgreSQL databases. Many of them are listed on the PostgreSQL Technical Documentation website. Additional resources for managing and integrating with Apple Remote Desktop's database are listed below.

Objectname and Propertyname Values

Mac_HardDriveElement
	CreationDate
	DataDate
	FileSystemType
	FreeSpace
	GroupName
	IsBootVolume
	IsCasePreserving
	IsCaseSensitive
	IsDetachable
	IsWritable
	JournalingIsActive
	LastBackupDate
	LastConsistencyCheckDate
	LastModificationDate
	LogicalUnitNumber
	Model
	OwnerName
	PermissionModes
	PermissionsAreEnabled
	Protocol
	RemovableMedia
	Revision
	SerialNumber
	SupportsJournaling
	TotalFileCount
	TotalFolderCount
	TotalSize
	UnixMountPoint
	VolumeName

Mac_NetworkInterfaceElement
	AllBroadcastAddresses
	AllDNSServers
	AllIPAddresses
	AllSubnetMasks
	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
	EthernetSQETestErrors
	EthernetSingleCollisionFrames
	EthernetTxInterrupts
	EthernetTxJabberEvents
	EthernetTxPHYTransmissionErrors
	EthernetTxResets
	EthernetTxResourceErrors
	EthernetTxTimeouts
	EthernetTxUnderruns
	HardwareAddress
	InterfaceFlags
	InterfaceName
	IsActive
	IsPrimary
	NetworkCollisions
	NetworkInputErrors
	NetworkInputPackets
	NetworkOutputErrors
	NetworkOutputPackets
	OutputQueueCapacity
	OutputQueueDropCount
	OutputQueueOutputCount
	OutputQueuePeakSize
	OutputQueueRetryCount
	OutputQueueSize
	OutputQueueStallCount
	PrimaryBroadcastAddress
	PrimaryDNSServer
	PrimaryIPAddress
	PrimarySubnetMask
	RouterAddress
	

Mac_PCIBusElement
	CardMemory
	CardName
	CardRevision
	CardType
	DataDate
	DeviceID
	RomRevision
	SlotName
	VendorID


Mac_RAMSlotElement
	DataDate
	MemoryModuleSize
	MemoryModuleSpeed
	MemoryModuleType
	SlotIdentifier


Mac_SystemInfoElement
	ATADeviceCount
	ActiveProcessorCount
	AppleTalkIsActive
	BootROMVersion
	BusDataSize
	BusSpeed
	BusSpeedString
	ComputerName
	DataDate
	En0Address
	FTPIsEnabled
	FileSharingIsEnabled
	FireWireDeviceCount
	HasKeyboardConnected
	HasMouseConnected
	HasVectorProcessingUnit
	KernelVersion
	Level2CacheSize
	MachineClass
	MachineIsNetBooted
	MachineModel
	MachinePartNumber
	MachineSerialNumber
	MainMonitorDepth
	MainMonitorHeight
	MainMonitorType
	MainMonitorWidth
	ModemCountryInfo
	ModemDescription
	ModemDriverInfo
	ModemFirmwareVersion
	ModemInstalled
	ModemInterfaceType
	OpticalDriveType
	PCISlotsUsedCount
	PhysicalMemorySize
	PrimaryIPAddress
	PrimaryNetworkCollisions
	PrimaryNetworkFlags
	PrimaryNetworkHardwareAddress
	PrimaryNetworkInputErrors
	PrimaryNetworkInputPackets
	PrimaryNetworkOutputErrors
	PrimaryNetworkOutputPackets
	PrimaryNetworkType
	PrinterSharingEnabled
	ProcessorCount
	ProcessorSpeed
	ProcessorSpeedString
	ProcessorType
	RemoteAppleEventsEnabled
	RemoteLoginEnabled
	SCSIDeviceCount
	SelectedPrinterName
	SleepDisplayWhenInactive
	SleepWhenInactive
	SpinDownHardDrive
	SystemVersion
	SystemVersionString
	TotalFreeHardDriveSpace
	TotalHardDriveSpace
	TotalRAMSlots
	TotalSwapFileSize
	TrashSize
	USBDeviceCount
	UnixHostName
	UnusedRAMSlots
	UserMemorySize
	WakeOnLanEnabled
	WebSharingIsEnabled
	WindowsFileSharingEnabled
	WirelessCardFirmwareVersion
	WirelessCardHardwareAddress
	WirelessCardInstalled
	WirelessCardIsActive
	WirelessCardLocale
	WirelessCardType
	WirelessChannelNumber
	WirelessIsComputerToComputer
	WirelessNetworkAvailable
	WirelessNetworkName

Mac_USBDeviceElement
	BusPower
	DataDate
	DeviceSpeed
	ProductID
	ProductName
	VendorID
	VendorName

For More Information

Modified: 2006-08-25