MySQL query to find a Smart Group assigned to the Scope in Policy(s).

[benducklow]

Trying to do some cleanup and organization with our Smart Computer Groups. I am looking to see if there is a MySQL query I can run to see which Policy a given Smart Group is tied to. For example, I know the Smart Computer Group name and the computer_group_id. I am trying to see if a particular computer_group_id is being used in one or more policies. I haven't been able to figure this one out. Maybe my old friend the MySQL guru @iJake would know?

[iJake]

Oooh, this sounds like a fun challenge. I'll work on it later today.

[iJake]

Alright, I think this will get you what you want. If you don't want Self Service policies you can remove those.
7 = policy
101 = self service

SELECT t1.policy_id, name, t3.computer_group_id, t3.computer_group_name
FROM `policies` t1
LEFT JOIN `policy_deployment`
AS t2 on t1.policy_id = t2.policy_id
LEFT JOIN computer_groups
as t3 on t2.target_id = t3.computer_group_id
WHERE target_type = '7' or target_type = '101'
ORDER BY t3.computer_group_name ASC

Let me know if this works for you.

[powellbc]

@iJake Thanks for sharing this query. One of the best practices I picked up last week at the JNUC was reducing smart groups when possible, so I wanted to replace all the smartgroups without policies applied to saved inventory searches. This got me on the right track.

[iJake]

@powellbc Glad that old query is still helping someone!

[hansjoerg_watzl]

Old thread but still useful! (Thanks @iJake for the original query. Helped me a lot.)

Here's the same query, but for configuration profiles instead of policies. Use both queries if you want to clear up your older smart/static groups, but don't know, where there are still referenced.

SELECT t1.os_x_configuration_profile_id, display_name, t3.computer_group_id, t3.computer_group_name FROM os_x_configuration_profiles t1 LEFT JOIN os_x_configuration_profile_deployment AS t2 on t1.os_x_configuration_profile_id = t2.os_x_configuration_profile_id LEFT JOIN computer_groups as t3 on t2.target_id = t3.computer_group_id WHERE (target_type = '7' or target_type = '101') AND t3.computer_group_id = '344' ORDER BY t3.computer_group_name ASC

The bold marked text can be removed, if you want to list all groups.