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:
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.
Apple Remote Desktop 2 captures system information data to a table called
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 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.
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:
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
Now we want to edit the postgresql.conf file to enable TCP/IP access to the database. The complete path for this file is:
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
./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
"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.
Figure 1: The Add Data Source Submenu.
In the resulting dialog, set the Database, User, and Password fields to
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
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:
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.
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