MySQL Syntax

[milesleacy]

Hey Nation!

I'm hoping you lovely people can help prevent me from reinventing the wheel/spending too much time on research and/or trial and error...

I need to figure out the appropriate MySQL syntax to extract the following from my database...

Policies
- with no scope targets
- scoped to All Computers
- A separate list of policies for each unique trigger
Computer configuration profiles
- with no scope targets
- scoped to All Computers
Mac App Store Apps
- with no scope targets
- scoped to All Computers
Restricted Software
- with no scope targets
- scoped to All Computers
Groups
- Computer groups with no relationship to other objects (not in any scopes)
- Return all polices for which a given group is a scope target
- Return all polices for which a given group is a scope exclusion
- Return all profiles for which a given group is a scope target
- Return all profiles for which a given group is a scope exclusion
- Return all restricted software for which a given group is a scope target
- Return all restricted software for which a given group is a scope exclusion
- Return all Mac App Store apps for which a given group is a scope target
- Return all Mac App Store apps for which a given group is a scope exclusion

[bburdeaux]

I've created queries for each of the things you've listed, except for one(I wasn't sure what you meant by computer groups with no relation to other objects), and saved them into one big SQL script which you can download here. However, I know that just giving you the queries without any context isn't always helpful, so I'll attempt to explain it as best as I can.

It helps to give a bit of an explanation of how the database is organized. Everything that can be scoped has at least two tables associated with it(usually more, but the others don't matter for this problem). These two mandatory tables are the base table and the deployment table, for computer policies these are policies and policy_deployment. The base table contains most of the information you'd expect it to, while the deployment table contains all the scoping information.

Inside the deployment table, every single target, limitation, and exclusion occupies its own row. This means that if policy 1 is scoped to 5 computers, then you will see 5 rows in the deployment table with policy_id 5.

Because the information you're looking for is in two tables that contain a matching field(i.e. policy_id), the simplest way to pull it is to do two nested queries. First pulling a list of all deployment rows that match your criteria, then using that list to find all of the objects whose IDs appear(or don't) in the first list. I'll give you an example below.

select policy_id, name from policies where policy_id in (select policy_id from policy_deployment where target_type = 101);

The first query, inside the parenthesis, pulls a list policy_ids from all rows with target_type 101. The second query then checks against that list to list the requested fields for each policy whose id is present. You can also use "not in" to get all policies whose id are not present in the initial query.

Things get a little more complicated the more specific you want your query to be, but the general layout of the query is the same.

[milesleacy]

Thanks @bburdeaux !

Since my original post, I've pulled the lists of policies by trigger and I was working on the syntax for the nested queries when I saw your post.

Re: "computer groups with no relation to other objects", I've edited the original post. I'm looking for groups that are not part of any scope. It appears that I'll have to cross-reference quite a few tables to get this.

[bburdeaux]

Something like the script below should work for you. You use the union command to combine the results of all 4 querys then select the computer_group_ids out of that combined list.

select computer_group_id, computer_group_name from computer_groups where computer_group_id not in ( select target_id from ( select target_id from policy_deployment where target_type = 7 union all select target_id from os_x_configuration_profile_deployment where target_type = 7 union all select target_id from restricted_software_deployment where target_type = 7 union all select target_id from mac_app_deployment where target_type = 7 ) as f
);