policy status in mysql

[kalyaniram]

Hi,
I am trying to get total computers by statuses, completed, remaining & failed by different policies. How is this possible. I m not looking even for the query, but i would love to know how to get the statuses and from which table in mysql. Thanks alot in advance

[karthikeyan_mac]

Hi @kalyaniram ,

Please try the below query. This will give success and failure of the policy if you replace ID in policies.policy_id and package name in log_actions, but I am unable to get the query to find the pending machines for the policy. Pending machines relates to policy_deployments table but I am not great in SQL to play with it.

SELECT DISTINCT policies.policy_id AS 'Policy ID', computers.computer_id AS 'Computer ID', computers.computer_name AS 'Computer Name', policies.enabled AS 'Policy Enabled', policies.name AS 'Policy Name', logs.log_id AS 'Log ID', logs.error AS 'Status(1 Failed, 0- Sucess)', log_actions.action AS 'Logs' FROM policies LEFT JOIN policy_history ON policies.policy_id=policy_history.policy_id LEFT JOIN logs on policy_history.log_id=logs.log_id LEFT JOIN log_actions on logs.log_id=log_actions.log_id LEFT JOIN computers on logs.computer_id=computers.computer_id where policies.policy_id in ('ID') and log_actions.action LIKE ('%Successfully installed Package Name%')

Thanks & Regards,
Karthikeyan Marappan