Database Question - Applications Table 12gb

TroyP
New Contributor II

Hi Guys

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.

4 REPLIES 4

carltonbrumbelo
New Contributor II
New Contributor II

Hi Troy,

There are a few things that we can look at when we have an applications table that is this large.

  1. 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.

  2. 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.

  3. 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.

Carlton

brunerd
Contributor

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™

Steven_Xu
Contributor
Contributor

@carltonbrumbelow @brunerd Thank you both! you helped me out.

kerouak
Valued Contributor

@brunerd

Yep, That's how we do it and very effective

Both Thumbs Up :-)