Cleaning up Large JSS Database Tables post Log Flush

[niatish]

# 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 :)