SQL to See Count of Policy Logs?

musat
Contributor III

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

2 REPLIES 2

cvgs
Contributor II

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
Contributor III

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.