Our JSS database Applications table has blown out to 12gb. Now im not sure if this is normal or not as we only have 700 apple laptops that use the JSS.
Its something thats sort of gone unnoticed for a while i suspect that at some point the tick box for application inventory "Include sizes, copyright information, modification dates, bundle IDs and permissions when gathering applications" has been ticked and may be the culprit for the size?.
At the moment any attempt to check on the database and or optimize/repair results in a machine that attempts to do something but nothing ever happens.
What are my options?, does the applications table hold vital information? or is it something we can truncate?.
Not sure how to proceed on this one and would very much appreciate some advice.
There are a few things that we can look at when we have an applications table that is this large.
We should look at what you've already mentioned, check to see if the box is checked for additional application data to be collected during inventory updates.
We should check out the flushing rules in Settings -> Flush Database Logs. The Computer Inventory Reports option here is what should be flushing the applications table. If we do not have a flushing rule set up yet, I would suggest doing so.
We should go through our policies and look for any that have "update inventory" selected. If we have a bunch of policies with it select, but they are all set to just run once, this shouldn't be too big of a deal. However, if we have a lot of policies that are updating the inventory daily (or worse, ongoing), then we could cull some of those out. Every time a managed computer updates its inventory, a new report is added to the database, and one of the tables touched is the applications table. So, for example, let's say we determine that between all of our policies, the devices are updating their inventory 10 times a day. With 700 clients in the environment, that's 7,000 new inventory reports daily, which means 7,000 new rows added to the applications table as well.
As far as what the table is used for, it's simply used to keep track of historical records regarding applications on the managed clients. It's mainly utilized for audits and such. If you feel that you have no real need to hold onto those records for any real length of time, then we can be pretty aggressive in how often we flush these reports.
I hope this helps.
Here is how to keep only the most recent inventory for each computer
########### # flush reports, keep newest reports for each computer only # count before select count(*) from reports; #make a new table with all the same columns create table reports_new like reports; #copy only the newest location_history for each computer insert into reports_new select * from reports where report_id in (SELECT MAX(report_id) FROM reports GROUP BY computer_id); #table switcheroo rename table reports TO reports_old, reports_new TO reports; # count after select count(*) from reports; #if all looks good #drop table reports_old ########### # flush applications, keep only newest report_id found in flushed reports # count before select count(*) from applications; #make a new table with all the same columns create table applications_new like applications; #make new table from the remaining newest report_id insert into applications_new select * from applications where report_id in (select report_id from reports); #table switcheroo rename table applications TO applications_old, applications_new TO applications; #count after select count(*) from applications; #if all looks good #drop table applications_old
This can be run without restarting Tomcat but of course: Always Have A Backup™