| TOPIC | LINK |
|---|---|
| Database tools |
|
| System information database |
|
| Configuration files |
|
| Process |
|
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.
Figure 1: The Add Data Source Submenu.
In the resulting dialog, set the Database, User, and Password fields to ard, as in Figure 2.
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.
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.
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
- Actual Technologies' ODBC Driver for Open Source Databases
Create custom reports with FileMaker Pro and Microsoft Excel using information from the PostgreSQL database. - Practical PostgreSQL by O'Reilly and Associates
- Apple Remote Desktop Administrator's Guide version 2.0 (PDF)
- Resources for Apple Remote Desktop 2 website
- Apple Remote Desktop 2: Agent resets the database under certain conditions
- Apple Remote Desktop 2: Backing up the admin database
- MacEnterprise.org: Apple Remote Desktop 2 In-Depth Webcast
- MacEnterprise.org: ARD Tracker
- Mike Bombich's Utility Software for Mac OS X for utilities and applications that work with Apple Remote Desktop 2.
Modified: 2006-08-25
  |
|
Get information on Apple products. Copyright © 2008 Apple Inc. |
|