Skip to main content
Question

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


Forum|alt.badge.img+17

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?

5 replies

iJake
Forum|alt.badge.img+21
  • Contributor
  • 279 replies
  • January 28, 2015

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


iJake
Forum|alt.badge.img+21
  • Contributor
  • 279 replies
  • January 29, 2015

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.


Forum|alt.badge.img+14
  • Contributor
  • 238 replies
  • October 28, 2016

@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
Forum|alt.badge.img+21
  • Contributor
  • 279 replies
  • October 28, 2016

@powellbc Glad that old query is still helping someone!


Forum|alt.badge.img+7

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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings