SQL to See Count of Policy Logs?

[musat]

Since we can't flush policy logs granularly (set a separate flush schedule for each policy), is there a SQL command that will print out a list of policies and the number of log files that policy has? I have named each of the policies that I know generate log files quickly, but every once in a while, something happens and another policy goes crazy. Usually this is due to a bad Smart Group causing the policy to run continually.

I wish the policy list would also show the count of log files, bu it doesn't. I could go into each policy manually, but I am sure there is a simple SQL command to just list that information. I am just not SQL savvy enough to make it happen.

I figured during this time with no students or teachers would be a good time to catch up on some cleanup routines.

Thanks, Tim

[cvgs]

Depending on the JSS version, something like this should give you what you want:

SELECT
    `policy_history`.`policy_id` AS "Policy ID",
    `policies`.`name` AS "Policy Name",
    COUNT(`policy_history`.`log_id`) AS "Logs"
FROM `policy_history`
    LEFT JOIN `policies` ON `policies`.`policy_id` = `policy_history`.`policy_id`
GROUP BY `policy_history`.`policy_id`
ORDER BY "Policy Name"

Good luck, and never run SQL code you found on the Internet :-)

Christoph

[musat]

Getting back to wishing that the policy list showed the count of log files. Someone here modified an Ongoing policy that had been tied to Smart Groups, to add in a Building. So, for who knows how long, this policy has been installing the app for every Mac in that building at every check-in. When I went in today there were 140,000 policy logs built up.

On edit: I remembered that there is a History button, so I can see that the change was made back on 3/2.