History Tracking - MS SQL Enterprise Geodatabse

Question

Hi folks,

I'm looking for ways to keep track of all the changes made to a published layer, something more detailed than just editor tracking. For example, when an asset record was created and who created it, when a record was updated and what changes were made by whom, and when a photo attachment was added.

I don’t want just the history of the most recent changes. I want to track every change, more like an audit. Any thoughts or links to documentation would be greatly appreciated.

We have ArcGIS Enterprise and MS SQL Server as the enterprise geodatabase.

DavidSolari · Answer

Branch Versioned data stores all edits to DEFAULT in a single table. Updates appear as a new record with a creation timestamp and deletions appear as a copy of the record with a flag set and the name of which user deleted it. There's pros and cons to branch versioning beyond the archive table but if it meets your needs then go for it. My org uses branch versioning all over the place and the tables have been an invaluable audit source when someone fat-fingers the delete button.

If DEFAULT-only is unacceptable and you need an audit trail of all activity then what @CodyPatterson suggested is required. Just keep in mind that A) the username logged will always be the Enterprise username for service edits, but it'll be the name of the DB user for direct connection edits so you'll have to proactively configure editing rights; and B)  any triggers you add to a feature class/table have to be maintained outside of ArcGIS and you may have to rewrite them if fundamental changes are made through ArcGIS.

CodyPatterson · Answer

Hey @Mark_Will

To my knowledge, it would show up as a change made from the ArcGIS Server or SDE when a user ends up editing the item through a referenced service. In that case, you may want to combine the editor tracking, as the editing user would just be "SDE" rather than a certain user, but the editor tracking would catch it correctly. I believe this would register as a change and be caught, then the trigger executed, but you may need to test that on your side, I'm no longer in the environment where I'd be able to unfortunately.

Cody

Mark_Will · Answer

I’m considering exploring triggers, but I’m confused about one thing. Triggers may only log changes when they are made directly in the database by a database user. However, in my case, the database has a referenced published service, and users will be updating records through the ArcGIS web platform. I’m not sure whether triggers would capture the names of the people making those changes. Any suggestions?