User and Location History flush/maintenance?

[benducklow]

Having some trouble confirming whether this is possible or not:

Is there a way (maybe by flushing a particular log in the Log Flushing section on the JSS) to periodically purge the records in this section for computer objects on the JSS? It seems to me that these never get purged or I am having some sort of issue doing so.

If it is managed by the Log Flushing, which particular log is it tied to?

Thanks in advance!

[davidacland]

I can't see that anywhere in the log flushing section. Is it just part of the computer record?

Would be an API call to clear it out if it is in the computer record.

[benducklow]

@davidacland Yes, the User and Location History section in the History tab:

[benducklow]

I heard back from JAMF on this issue and they confirmed this is a defect (D-009921). Before this was confirmed, I did get some MySQL code to purge out records older than a specified epoch timestamp, so it was addressed, but noticed it would just continue to grow (thus increase the database size day after day).

I thought I would share!

[techgeek]

@benducklow Get to hear you got this issue fixed.

Would you mind sharing what MySQL coding you had to use to resolve your issue please?

As the same problem has occurred in my workplace as well, and I am researching as to how to resolve the issue. Our issue appears to a huge amount of 'User and Location History' data that needs to be flushed.

[russeller]

@benducklow @techgeek I was hoping that future releases would resolve this issue. Just upgraded to 9.98.<hotfix> and still have my "User and Location History" growing out of control. Sometimes up to 20-40 entries in this per computer record and we have over 10K Macs in our JSS. Any luck on getting this resolved or what command would be safe to run to clear out the excessive entries into "User and Location History"? Thanks!

[benducklow]

@techgeek @ssrussell - I forgot about this post and issue but your recent reply had me check again and I and seeing this is still an issue for me! It appears that the effort that was done back in 2015 was a one-time cleanup.... which was not my understanding. I have re-vived my case with Jamf Support on this topic and will let you know via this thread as to what comes of it.

[russeller]

@benducklow when I run this
SELECT COUNT(*) FROM `jamfsoftware`.`location_history`; we are up to over 12.5 million rows. I noticed there are a bunch of entries where the computer_id is 0. I'm not sure what these are, so I excluded them from my query with this:
SELECT COUNT(*) FROM `jamfsoftware`.`location_history` WHERE `computer_id` != '0'; Not sure if I could just run a delete command like, DELETE FROM `jamfsoftware`.`location_history` WHERE `computer_id` != '0';
I hesitate not knowing if there are other references to these tables and general "don't mess with mysql" advice I get from support. With 12.5 million rows it could take quite a long time (hours) to clean up this table. What command did support provide you to run your clean up? Thanks!

[benducklow]

So I heard back from Jamf Support and they are stating there is no log this data is associated to for us to flush. I believe I did this manually years back via MySQL commands that Jamf (at the time supplied). I don't seem to have it anymore so it was probably supplied to me in a Webex and then got lost in the shuffle of day-to-day work. This data not being associated to a particular log flushing setting was not the impression I was under 2 years ago. As of now, Jamf Support claims this data isn't significant but stated a Feature Request would be the way to possibly get this addressed in a future release. The only existing Feature Request I found was this one: https://www.jamf.com/jamf-nation/feature-requests/2419/clear-user-and-location-information-in-v9, and event though not 100% what I am looking for, its along the same line... so I upvoted and added a comment.

[benducklow]

Got a follow-up from Jamf support regarding this just now - They said they have a documented product issue for this (PI-004053), which was originally filed as a Defect (D-009921). They also said they couldn't (re)supply me with the commands from a few years ago as it potentially affect other parts of the JSS. Finally they said to keep my eyes peeled for this PI being addressed in some future release...

Thats all I know!

[russeller]

@benducklow Thanks for providing the information you got from JAMF support.

It would be nice to know from them how to handle this because records are just going to keep growing and growing and it's going to take forever to clean them up whenever we are provided with a solution.

[brunerd]

Currently I don't have a solution/magic SQL incantation... to smartly clean up this table
However I have discovered a few things...

1. locations and location_history tables will grow with every Network State Change for a computer only if they have either a Building or Department set. If they have one of those set it will write one or usually two rows to both of those tables. If they have neither then no growth.
2. If you do drop those tables, Smart Groups and Searches will be unaffected, you can still search and scope smart groups to those criteria (username, realname, building_id, room, department_id, phone, email, position). However when you view the computer object in the console you won't see the data from those tables because it is doing a live lookup of the tables. Smart Groups and Searches though are working with the computers_denormalized table which has composited all data from all the various tables into a computer record. Only on the next recon submission for that computer will it update computers_denormalized and then that data will be gone.

Dropping doesn't work for me or my environment, however:
For those who don't care or absolutely must clear out the tables this will get the job done

#creates the columns and attributes from the table in a new empty one
create table new_locations like locations; 
#get rid/drop the old table
drop table locations; 
#create it anew with all the columns backed up in new_locations;
create table locations like new_locations;
#delete the template new_locations
drop table new_locations;

#same method as above
create table new_location_history like location_history; 
drop table location_history; 
create table location_history like new_location_history; 
drop table new_location_history;

[DISCLAIMER: Don't use this in production without testing. No warrantee implied. Just sayin' you can blow away the tables like this... YMMV]

For a proper purge an ideal mySQL query would clean things out and keep the last entry of a computer record and get rid of the other breadcrumbs, something like:
1. Query the location_history table for the location_id for each computer_id with the newest date_assigned_epoch and delete the rest
2. Create an inverse set of those location_id from above and delete them from locations table.

Sounds so easy, right? Yet mySQL-Fu is just not as straightforward as BASH for my brain, perhaps writing out the idea is the first step (of a journey of a thousand queries :)

[beth_lindner]

Wanted to give those on this thread a heads up - As of Jamf Pro 9.100.0 "Re-enrollment Settings for Computers and Mobile Devices" has been introduced. One of the items available for configuration is a "User and Location History category." We look forward to your feedback on the enhancement! Thanks to everyone here for contributing to the community.

[apizz]

@beth.lindner we've needed this for so long! Finally. Will report after we upgrade and test.

[brunerd]

Journey of a thousand commands has arrived at these queries:
keep only the most recent locations and location_history for each computer

##
# flush location_history, keep newest only
# "safe" keeps old table as location_history_old
# count before
select count(*) from location_history;
create table location_history_new like location_history;
#copy only the newest location_history for each computer
insert into location_history_new select * from location_history where location_id in (select max(location_id) from location_history group by computer_id);
# table switcheroo
rename table location_history TO location_history_old, location_history_new TO location_history;
# count after
select count(*) from location_history;

#if everything checks out
#drop table location_history_old;

##
# flush locations, keep only location_id found in flushed location_history;
# "safe" keeps old table as locations_old;
# count before
select count(*) from locations;
create table locations_new like locations;
#copy only the newest locations for each computer
insert into locations_new select * from locations where location_id in (select location_id from location_history);
# table switcheroo
rename table locations TO locations_old, locations_new TO locations;
# count after
select count(*) from locations;

#if everything checks out
#drop table locations_old;

This can be run without stopping TomCat but of course: Always Have a Backup™

[beth_lindner]

@brunerd did you have a chance to play with the "Re-enrollment Settings for Computers and Mobile Devices" in Jamf Pro 9.100.0? I am wondering if there is a Feature Request regarding how that new functionality works?

[CAJensen01]

@beth.lindner Can JAMF break this history item out in the Log Flushing section so that we can flush it on a quicker interval if we're not partial to it? Our location history tables are routinely one of the larger parts of our database--and it's not data we're making a lot of usage of.

[neilmartin83]

Just wanted to pop in and say massive thanks for this - as I am setting Department and Building on computer records, the User and Location History is bloating like mad - a new line every check-in and network state change.

My JSS is moving to Cloud soon (currently on prem 9.101.0) - in my dev instance, running @brunerd 's SQL queries freed up over 1GB on the DB after I dropped the backup tables.

I'm not sure if this is still a thing in later versions (reading through release notes) but I'd imagine migrating the DB to a cloud instance or just doing a JSS upgrade on your own server with all that bloat in it may not be desirable.

[beth_lindner]

@CAJensen01 sounds like a helpful idea! Though requested for a different reason I think this Feature Request might be what we are looking for? https://www.jamf.com/jamf-nation/feature-requests/6802/flush-user-location-history

[chris_kemp]

Upvoted - we just went through this as well, with our database having grown to ridiculous proportions. One of the fixes was the script posted above - having the ability to execute this on demand (if the system isn't going to clean these up itself...) would be a huge help.

[fsjjeff]

Just a heads up that this can be disastrous

Based on this conversation and being faced with a JSS that was crashing multiple times per day, and was unable to even delete a computer anymore because of so many location and location_history records, I followed the script above and cleaned out my system. Everything looked great and I'm no longer seeing the JSS crashes so after a couple of days I cleared out the old tables.

Unfortunately, I'm discovering that even though the Building & Department info looked to be there when I viewed lists of computers, and all my smart groups and such were working, as the devices update that info is lost.

This is very bad as all my iOS apps are scoped by department, and when the department changes the JSS sends messages to remove the scoped apps, so thousands of iPads are deleting their apps. And because of Apple's lovely data sandboxing, in at least some cases that means deleting the user data associated with that app as wel.

I'm lucky to have a sync script to reset the location data, which is running as I type this, but wanted to put up this cautionary note for anyone else looking at this message thread.