Hi! Please! I'll need to know what SQL syntax I need to run in the Mysql database to get the scope (and also exclusions) of profiles and policies. Thank you very much
Solved
Mysql Syntax Scope of profiles and policies
+5Best answer by karthikeyan_mac
@yann_palacios Its been a while I use this mysql query. You can give a try. This will get you policies information. It was working in earlier version of Jamf. Please check if it still works.
SELECT distinct policies.policy_id AS 'Policy ID', policies.name AS 'Policy Name', policies.enabled AS 'Enabled', policies.use_for_self_service AS 'Selfservice', packages.package_id AS 'Package ID', packages.package_name AS 'Package Name', scripts.script_id AS 'Script ID', scripts.file_name AS 'Script Name', computer_groups.computer_group_id AS 'Computer Group ID', computer_groups.computer_group_name AS 'Computer Group Name' FROM policies LEFT JOIN policy_packages ON policies.policy_id=policy_packages.policy_id LEFT JOIN packages ON policy_packages.package_id=packages.package_id LEFT JOIN policy_scripts ON policies.policy_id=policy_scripts.policy_id LEFT JOIN scripts ON policy_scripts.script_id=scripts.script_id LEFT JOIN policy_deployment ON policies.policy_id=policy_deployment.policy_id LEFT JOIN computer_groups ON policy_deployment.target_id=computer_groups.computer_group_id order by policies.policy_id;
Thanks.
+26This is probably not the place where you will get help with your need. If you have something specific many of us will be glad to help you sort out a problem, especially if its directly related to the Jamf database. However, a generic I need to know what SQL syntax I need to run is probably best asked on a SQL discussion forum.
+17@yann_palacios Its been a while I use this mysql query. You can give a try. This will get you policies information. It was working in earlier version of Jamf. Please check if it still works.
SELECT distinct policies.policy_id AS 'Policy ID', policies.name AS 'Policy Name', policies.enabled AS 'Enabled', policies.use_for_self_service AS 'Selfservice', packages.package_id AS 'Package ID', packages.package_name AS 'Package Name', scripts.script_id AS 'Script ID', scripts.file_name AS 'Script Name', computer_groups.computer_group_id AS 'Computer Group ID', computer_groups.computer_group_name AS 'Computer Group Name' FROM policies LEFT JOIN policy_packages ON policies.policy_id=policy_packages.policy_id LEFT JOIN packages ON policy_packages.package_id=packages.package_id LEFT JOIN policy_scripts ON policies.policy_id=policy_scripts.policy_id LEFT JOIN scripts ON policy_scripts.script_id=scripts.script_id LEFT JOIN policy_deployment ON policies.policy_id=policy_deployment.policy_id LEFT JOIN computer_groups ON policy_deployment.target_id=computer_groups.computer_group_id order by policies.policy_id;
Thanks.
+5karthikeyan_mac wrote:
@yann_palacios Its been a while I use this mysql query. You can give a try. This will get you policies information. It was working in earlier version of Jamf. Please check if it still works.
SELECT distinct policies.policy_id AS 'Policy ID', policies.name AS 'Policy Name', policies.enabled AS 'Enabled', policies.use_for_self_service AS 'Selfservice', packages.package_id AS 'Package ID', packages.package_name AS 'Package Name', scripts.script_id AS 'Script ID', scripts.file_name AS 'Script Name', computer_groups.computer_group_id AS 'Computer Group ID', computer_groups.computer_group_name AS 'Computer Group Name' FROM policies LEFT JOIN policy_packages ON policies.policy_id=policy_packages.policy_id LEFT JOIN packages ON policy_packages.package_id=packages.package_id LEFT JOIN policy_scripts ON policies.policy_id=policy_scripts.policy_id LEFT JOIN scripts ON policy_scripts.script_id=scripts.script_id LEFT JOIN policy_deployment ON policies.policy_id=policy_deployment.policy_id LEFT JOIN computer_groups ON policy_deployment.target_id=computer_groups.computer_group_id order by policies.policy_id;
Thanks.
Added the exclusion field from the policy_deployment table to the query to distinguish the scope of the exclusion. Thank you very much for your help. 😀
1 person likes this
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.