History Tracking - MS SQL Enterprise Geodatabse

[Mark_Will]

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.

[CodyPatterson]

Hey @Mark_Will 

Something like this is possible, but it's definitely going to eat up storage if you have quite a large environment. This will need to be done using triggers on the MS SQL Server to setup auditing on the whole CRUD suite, so create read or select, update, delete and so on. Here's a link to a StackOverflow topic that describes somewhat of what you're wanting https://stackoverflow.com/questions/11437987/using-triggers-in-sql-server-to-keep-a-history

You'll just need to add Delete, unless you need Select as well. On these, you'll need to filter out the generic database tasks etc. because you will end up with an absolutely massive amount of SDE user changes or ArcGIS Server outreach.

Cody

[Mark_Will]

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?

[CodyPatterson]

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

[DavidSolari]

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.