Where I work we are frequently asked the question, where can I find SPSS (or some other software package in our labs). For years we have tried to keep a webpage up to date that helps professors and students answer this question themselves but the page is never updated on a regular basis and is soon out of date. I decided that I could use KACE to better answer this question. My setup includes a webpage that queries the KACE database for a given Software asset and returns labels in the "Labs and Classrooms" label group, thereby showing the user where they can find the selected title.

Setup:

We have just started keeping track of our supported software using the Software asset type. Software assets are keyed to a smart label in the Software Inventory to link the asset to the individual titles that should be included. Using the label allows us to specify whether an asset includes all versions of a title or just selected versions. For this project I also added two fields to the software asset type:

• "Show on Find Software page" - Single Select, Values of Yes, No
• "Note for Find Software page" - Memo field

I added these so that the Find Software page doesn't display all of the assets in the software asset type.

The next thing I did was create a label group called "Labs and Classrooms". The Find Software page only displays labels that are part of this group so it looks like a list of labs and classrooms and doesn't show things like patch schedules or other labels we use for management purposes. I then labeled our classrooms with English friendly names, like Library Information Commoons, James Hall Room 234, etc. and made sure these labels were in the Labs and Classrooms group. Most of our labs already had labels for other reasons so I just had to move them into this group and rename a few of them.

Once the KACE backend work was done I developed the SQL queries necessary to link a Software asset to the labels of machines that have that asset. Below is the resulting query:

SELECT 
DISTINCT(LABEL.NAME) AS LABELS
FROM ASSET
JOIN ASSET_DATA_6 on ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID
JOIN SOFTWARE_LABEL_JT on SOFTWARE_LABEL_JT.LABEL_ID = ASSET_DATA_6.FIELD_10001
JOIN SOFTWARE on SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
JOIN MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden'
LEFT JOIN LABEL_LABEL_JT on LABEL_LABEL_JT.CHILD_LABEL_ID = LABEL.ID
LEFT JOIN LABEL PARENTLABEL on (PARENTLABEL.ID = LABEL_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')

WHERE ASSET.ID = #AssetID#
AND PARENTLABEL.NAME = "Labs and Classrooms"
ORDER BY LABEL.NAME

This query returns a distinct set of labels found on computers that have any software title from the inventory which is linked to the given software asset (denoted by #AssetID# above), and further limits the list to labels in the Labs and Classrooms group. 

At our institution we use ColdFusion for developing web applications so I built a page which connects to the KACE database and runs the query for the given AssetID (passed as a post variable). Other queries are used to determine if the software is installed on all of the computers in a given lab and to get the "Note for Find Software page". The main page for this web application includes the form that is populated with the list of software assets marked with a Yes for "Show on Find Software page" and AJAX is used to return the results to the user. The end result looks like this:

This tool has given our faculty and students the ability to find software they need and our group no longer needs to keep webpages up to date with where software is installed. Adding software titles to this list is accomplished in an easy way that anyone in our group can now do. If anyone would like to implement a similar solution at their institution feel free to contact me. I'm willing to share the source code for the two pages that comprise the web application.