Excluding specific Applications from Software Inventory Report

[FritzsCorner]

I was wondering if anyone knew of a way to generate Software inventory reports that exclude specific applications such as apps included in the OS. (Console.app, Chess.app, etc.) We are in the early stages of migrating our Mac users to an official company build and are trying to get a good understanding of applications that they currently have installed. All of the Macs have been enrolled and are reporting inventory but I have not found a way to create an at-a-glance list of the applications they have installed. I looked through the API and didn't see anything that looked like it would help so my next thought was to create a sql query. I just wanted to make sure that there wasn't a better way of doing this before I spent the time hacking something together.

[bentoms]

@FritzsCorner, I think you'll need to add the apps you want to exclude as licensed software.

Once done, you can then stop the apps for showing.

[#3]

Reading this, I decided to update my OS default apps - after I added everything new since 10.8 (Reminders.app, Maps.app, etc) the list is no longer being excluded from inventory. If I create a new licensed software record with a single app definition, and set to Exclude, it works as expected; but with 42 entries it seems to stop working. JSS is 9.25.

[FritzsCorner]

I was looking at going the licensed software route but I think I am going to just write a custom sql query instead. That way I can get exactly what I am looking for. As a start I see that I can pull up a computers application inventory simply by finding it's report id and then checking that report ID against the applications table.

SELECT last_report_id FROM computers_denormalized where computer_name = "MAC123";

Query returns last_report_id as "18415"

SELECT * FROM applications WHERE report_id = 18415;

From here I can just update the custom query to exclude the known apps and return only what we don't include with our build. I will report back with my custom query as soon as I get a chance to work on it.

Thanks for the feedback everyone!

EDIT: As a bonus I discovered that the applications table has a value for "mac_app_store" If the value for mac_app_store is 0 the app was not installed from the store, however if it is set to 1 the app was installed from the store. Identifying all the apps that were installed from the App Store has been on my to-do list for quite some time and now it looks like I will be able to check that one off as well.

SELECT * FROM applications WHERE mac_app_store = 1;

[FritzsCorner]

As promised here is my custom Query I threw together last night. It can be pretty messy and cumbersome once you start adding a lot of Applications to exclude. It isn't ideal and I am sure there are much better and cleaner ways to do this but here is the quick and dirty method I will use for now until I have a chance to improve on it.

*SET @computername = "MAC123";

SELECT last_report_id FROM computers_denormalized WHERE computer_name LIKE '@computername' INTO @reportID;

SELECT FROM applications WHERE report_id = @reportID
AND application_name NOT IN ( 'App1.app', 'App2.app', 'App3.app' );**