{"id":2915,"date":"2018-10-17T08:40:45","date_gmt":"2018-10-17T08:40:45","guid":{"rendered":"http:\/\/blog.designed79.co.uk\/?p=2915"},"modified":"2018-11-27T13:16:46","modified_gmt":"2018-11-27T13:16:46","slug":"jamf-pro-database-maintenance","status":"publish","type":"post","link":"https:\/\/blog.designed79.co.uk\/?p=2915","title":{"rendered":"Jamf Pro &#8211; Database maintenance"},"content":{"rendered":"<p>The JSS keeps every recon for every computer. It only needs the last one. In large estates this can be GB&#8217;s of data and can slowdown or stop upgrades.<\/p>\n<p>Heres how to clean the tables<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;height:300px;\"><div class=\"text codecolorer\">###########<br \/>\n# flush reports, keep newest reports for each computer only<br \/>\n# count before<br \/>\nselect count(*) from reports;<br \/>\n#make a new table with all the same columns<br \/>\ncreate table reports_new like reports;<br \/>\n#copy only the newest location_history for each computer<br \/>\ninsert into reports_new select * from reports where report_id in (SELECT MAX(report_id) FROM reports GROUP BY computer_id);<br \/>\n#table switcheroo<br \/>\nrename table reports TO reports_old, reports_new TO reports;<br \/>\n# count after<br \/>\nselect count(*) from reports;<br \/>\n<br \/>\n#if all looks good<br \/>\ndrop table reports_old;<br \/>\n<br \/>\n###########<br \/>\n# flush applications, keep only newest report_id found in flushed reports<br \/>\n# count before<br \/>\nselect count(*) from applications;<br \/>\n#make a new table with all the same columns<br \/>\ncreate table applications_new like applications;<br \/>\n#make new table from the remaining newest report_id<br \/>\ninsert into applications_new select * from applications where report_id in (select report_id from reports);<br \/>\n#table switcheroo<br \/>\nrename table applications TO applications_old, applications_new TO applications;<br \/>\n#count after<br \/>\nselect count(*) from applications;<br \/>\n<br \/>\n#if all looks good<br \/>\ndrop table applications_old;<\/div><\/div>\n<p><strong>Clear Pending and Failed Commands<\/strong><\/p>\n<p>View and clear pending commands:<\/p>\n<p>Before clearing or viewing, select the database:<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;\"><div class=\"text codecolorer\">use jamfsoftware;<\/div><\/div>\n<p>To view Pending:<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;\"><div class=\"text codecolorer\">select count(*) from mobile_device_management_commands where apns_result_status !='Acknowledged';<\/div><\/div>\n<p>To clear Pending codes:<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;\"><div class=\"text codecolorer\">delete from mobile_device_management_commands where apns_result_status !='Acknowledged';<\/div><\/div>\n<p>View and clear failed commands:<\/p>\n<p>To view Failed commands:<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;\"><div class=\"text codecolorer\">select count(*) from mobile_device_management_commands where apns_result_status !='Error';<\/div><\/div>\n<p>To clear Failed codes:<\/p>\n<div class=\"codecolorer-container text default\" style=\"overflow:auto;white-space:nowrap;\"><div class=\"text codecolorer\">delete from mobile_device_management_commands where apns_result_status !='Error';<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>The JSS keeps every recon for every computer. It only needs the last one. In large estates this can be GB&#8217;s of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2915","post","type-post","status-publish","format-standard","hentry","category-info-on-tech"],"_links":{"self":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2915","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2915"}],"version-history":[{"count":0,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2915\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2915"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2915"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2915"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}