# A Rum and Coke for Captain Jack
We had a policy running at a very high frequency on 4 days a week with "Update Inventory" enabled. This caused a huge jump in our database size.. so much that we couldn't locally restore the database from Backup.
We were able to find the offending policy, disable it, and in our silly reasoning we flushed the policy log.. while this did reduce the size of the database it also left us with a solve for X equation since the log of the offending policy was gone.
Please note we only knew timeframes for the policy, the computers in scopes thanks to smart groups, and the tables in the database that were relative the to the policy ID in the URL of the offending policy.
Time is in EPOCH
Smart groups are from computer_group_id in the computer_group_membership table
* Then you just swap in the impacted tables, for the example below I am using operation_systems, total tables hit be recon for computers are: applications, plugins, operating_systems, hard_drive_partitions, hard_drives, hardware_reports
SELECT FROM_UNIXTIME(reports.`date_entered_epoch`/1000), COUNT(reports.report_id), operating_systems.*
FROM `computer_group_memberships`
JOIN `reports`
ON reports.`computer_id` = `computer_group_memberships`.computer_id
JOIN `operating_systems`
ON operating_systems.report_id = reports.report_id
WHERE
`computer_group_memberships`.`computer_group_id` IN(33,34,35,38)
AND
(reports.`date_entered_epoch` >= '1391662800000'
AND reports.`date_entered_epoch` <= '1392008400000')
OR
(reports.`date_entered_epoch` >= '1392267600000'
AND reports.`date_entered_epoch` <= '1392613200000')
OR
(reports.`date_entered_epoch` >= '1392872400000'
AND reports.`date_entered_epoch` <= '1393218000000')
OR
(reports.`date_entered_epoch` >= '1393477200000'
AND reports.`date_entered_epoch` <= '1393822800000')
GROUP BY report_id;
Please note this is all policies running during the specific time spans on specific computers. Next we need to delete the results of these queries. Be sure to snapshot your Database VM before delete
Just like before you will change out operation_systems for you desired table.
DELETE operating_systems
FROM operating_systems
JOIN reports
ON reports.report_id = operating_systems.report_id
JOIN computer_group_memberships
ON computer_group_memberships.computer_id = reports.computer_id
WHERE
`computer_group_memberships`.`computer_group_id` IN(33,34,35,38)
AND
(reports.`date_entered_epoch` >= '1391662800000'
AND reports.`date_entered_epoch` <= '1392008400000')
OR
(reports.`date_entered_epoch` >= '1392267600000'
AND reports.`date_entered_epoch` <= '1392613200000')
OR
(reports.`date_entered_epoch` >= '1392872400000'
AND reports.`date_entered_epoch` <= '1393218000000')
OR
(reports.`date_entered_epoch` >= '1393477200000'
AND reports.`date_entered_epoch` <= '1393822800000');
p.s. Internally this type of policy was first a problem thanks to the Adobe Remote Update Manager causing us to use the log to truncate the RUM.. earning it the name of "Captain Jack" as we had to burn the RUM. In that spirit we call this template for truncation post log delete a "RUM and Coke".. it almost drove our developer to drink :)
