I wanted to find a way to incorporate purchase dates for our Apple hardware into KACE in the same way that Dell hardware shows the shipped date. The Apple GSX program can be utilized by institutions that repair their own Apple hardware and there is an API available to interact with the service to get relevant information on hardware. One of the fields available is the estimated purchase date. This article describes how to setup KACE to have a custom inventory field that shows that date and how to build a report showing Apple computers that are over four years old.

More information about Apple GSX can be found here: http://www.apple.com/support/programs/ssa/

Requirements:

• You must have a GSX account which includes permissions "Can access web services". 
• Download the GSXLib, a free PHP library that includes a command line tool for interacting with GSX (I used version 0.4 as of this writing)
  https://github.com/filipp/gsxlib 

Step 1 - Create a script to run gsxcl on Apple computers

Create a script with the following options:

• Type: Offline
• Name: I called mine "Get Apple Purchase Date"
• Enabled: Check
• Deployment: I would recommend testing on a few machines but at this point I now have the option checked to deploy to all computers
• Supported Operating Systems: Mac OS X (no need to pick specific versions)
• Scheduling: I have mine set to "Run once at next client checkin" since it doesn't need to run more than once per machine. 
• "Allow run while logged off" Check
• Dependencies: Upload the gsxcl and gsxlib.php downloaded from https://github.com/filipp/gsxlib
• Policy or Job Rules:
  • Task 1:
    • Verify: Launch a Program
    • Directory: $(KACE_DEPENDENCY_DIR)
    • File: gsxcl
    • Parameters: -s <your GSX sold to account number> -u <GSX user name> -p <GSX Password> -r am -q | grep estimatedPurchaseDate > "$(KACE_DATA_DIR)purchasedate.txt"

This script runs the gsxcl command on the target computer and queries for the computer's information. The grep statement pulls out just the line we're interested in and places it in a text file located in the KACE data directory (for lack of a better place to keep it). Here's an example of the file from my machine:

    [estimatedPurchaseDate] => 10/17/11

Step 2 - Create a custom inventory field containing the purchase date

• In Inventory, Software, Choose Action, Add New Item
• Enter a Display Name (I prefix all of my custom inventory fields with an asterisk to make them easier to find): *Apple Purchase Date
• Supported Operating Systems: Select all of the MacOS options
• Custom inventory rule: ShellCommandDateReturn(awk '{print $3}' "/Library/Application Support/Dell/KACE/Data/purchasedate.txt")
  This pulls just the date from the purchasedate.txt file.  I didn't pull in the whole file so that we can query the data using date functions.
• Save the software item and make a note of the title id. If you're using the adminui then the software id will be displayed in the URL when you view the title. 

Step 3 - Wait for the magic to happen

Once machines start running the script and checking in you should see the custom inventory field get populated. 

Step 4 - Create a report showing machines that have a purchase date

A very simple report can be created that just shows computers that have a purchase date. Here's the SQL code you can use to create a SQL report:

SELECT MACHINE.NAME, date(MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE) as PURCHASE_DATE
FROM ORG1.MACHINE_CUSTOM_INVENTORY
JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
WHERE SOFTWARE_ID = <software id of title you created in step 2>

Step 5 - Create a report showing machines that are over four years old

Once you have the data you can make more interesting reports. For instance. the following SQL will show computers with a purchase date that is over four years old:

SELECT MACHINE.NAME, date(MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE) as PURCHASE_DATE
FROM ORG1.MACHINE_CUSTOM_INVENTORY
JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
WHERE SOFTWARE_ID = <software id of title you created in step 2>
AND DATE_FIELD_VALUE < DATE_SUB(NOW(), INTERVAL 4 YEAR);

By integrating GSX into KACE we will be able to make more informed decisions about the age of our Apple hardware and more readily identify machines that are in need of replacement. The GSX data also contains warranty information, so similar methods could be followed to create reports showing which computers are out of warranty coverage.