Determine who deleted records from an enterprise geodatabase dataset

Summary

In today's data-driven world, maintaining the integrity and security of geospatial information stored in an enterprise geodatabase is important. 

An enterprise geodatabase provides all the advanced capabilities of a geodatabase such as concurrent editing, versioning for complex workflows, high security, and data integrity. You can leverage your organization's enterprise relational databases with an enterprise geodatabase.

Enterprise geodatabases work with a variety of DBMS storage models and take full advantage of underlying DBMS architectures.

Databases and the enterprise geodatabase support auditing capabilities to track by whom and when data is inserted, updated and deleted from various datasets. 

Organizations may be interested in auditing information for the following reasons:

• It enhances accountability by tracking user actions, ensuring that individuals are responsible for their data management practices. 
• It aids in compliance with regulatory requirements, as many industries are mandated to maintain detailed records of data access and modifications.
• Having the ability to trace deletions can facilitate quicker recovery from accidental or malicious data loss, ultimately safeguarding the organization's valuable geospatial assets.

Procedure

There are a couple of methods and tools available for determining who created, updated and deleted records from datasets stored in an enterprise geodatabase. Below, we discuss a few different methods, the capabilities they offer, and their limitations. These capabilities are supported on datasets that are stored in an enterprise geodatabase and that are registered with the geodatabase:

Editor tracking

Editor tracking provides a setting on feature classes and tables that allows automatically recording information about any inserts and updates made. It maintains a record of the editor who created or modified the data, and a time stamp of when the edit occurred. 

Many organizations find editor tracking helpful to maintain accountability and transparency as well as to enforce quality control standards.

Editor tracking can help answer questions such as who was responsible for edits made within a geodatabase.

When editor tracking is enabled on a feature class or table, the following information is tracked automatically:

• The user who created a feature
• The date and time the feature was created
• The user who last edited a feature
• The date and time the feature was last edited

When editor tracking is enabled in ArcGIS Pro on a dataset in a file, mobile, or enterprise geodatabase, the attribute table is updated to include fields designated to track editing information.

When a feature is created, editor tracking records the name of the user who created the feature along with the date and time of creation. When existing features are updated, the name of the user who made the edit, along with the date and time the edit was made, are recorded.

However, when a record is deleted, the editor tracking information is deleted along with the record. Editor tracking in isolation therefore cannot be used to determine who deleted a particular record from a dataset. 

Archiving

Archiving provides the functionality to record and access changes made to all or a subset of the data in a geodatabase. Archiving is the mechanism for capturing, managing, and analysing data changes over time.

Organizations must preserve the changes made to their data to answer common questions such as the following:

• What was the value for a specific attribute at a certain moment?
• How has a particular feature or row changed through time?
• How has a spatial area evolved?

Archiving can be enabled to track historical data on traditional versioned datasets in an enterprise geodatabase or on non-versioned datasets in an enterprise or mobile geodatabase.

When archiving is enabled on versioned data, all attributes and all rows in the default version of the dataset or object class are copied to a new archive class. The archive class has the same schema as the original dataset, with additional date attributes GDB_FROM_DATE and GDB_TO_DATE to record the timestamp for the effective lifespan of the archived row and a GDB_ARCHIVE_OID attribute to uniquely identify each row.

When archiving is enabled on non-versioned data, the geodatabase creates the additional date attributes directly in the base table for that dataset. 

As edits are made to the dataset, these attributes are updated to maintain a historical record over time.

Archiving, therefore, preserves deleted records in the enterprise geodatabase and sets the GDB_TO_DATE to the date the record was deleted. 

The archive class can be added to ArcGIS Pro to query and view archived data and columns directly in ArcGIS Pro. 

Using a combination of editor tracking and archiving, it is possible to determine when a record was deleted and who the last person was to modify the record, but it still does not record who deleted the record. 

Branch versioning

In an enterprise geodatabase with multiple editors, versions allow working with the same data at the same time without applying locks or duplicating data. Versions give each editor their own unique, isolated view of the data.

Versioning facilitates long transactions by allowing editors to work in isolation within their own version of the geodatabase and across multiple edit sessions. Once an editor finishes a collection of edits, they can merge their changes back to the parent version from which their version was created. The original parent of all versions in a geodatabase is called the Default version.

Branch versioning is a type of geodatabase versioning that works with the ArcGIS Enterprise Web GIS model using a services-based architecture to allow multiuser editing workflows and long transaction scenarios through web feature layers.

When registering a dataset as branch versioned, editor tracking, archiving and GlobalIDs are automatically enabled on the dataset to support multi-user editing and edit isolation. 

Several additional columns are added to the dataset when it is registered as branch versioned, including the standard editor tracking and archiving columns, as well as the GDB_IS_DELETED, GDB_DELETED_AT and GDB_DELETED_BY fields, as shown in the next image. 

The additional columns can be viewed by adding the archiving class to ArcGIS Pro, or they can be viewed using DBMS tools such as SQL Server Management Studio, PgAdmin, or SQL Developer for example, as shown in the next image. 

The standard editor tracking and archiving fields can be used to determine when and who created and updated records in a dataset, while using the GDB_IS_DELETED, GDB_DELETED_AT and GDB_DELETED_BY fields, which can be used to identify whether a row or feature has been deleted, when it was deleted, and by whom.

While branch versioning can be used to determine who deleted a record from a dataset and when, it does require publishing the dataset as a web feature layer to ArcGIS Enterprise to support editing.  Edits made to any version other than the Default version also must be reconciled and posted to the Default version.

For organizations that already make use of branch versioning, this provides the most streamlined solution to fully audit tracking of the data. 

Attribute Calculation rule

For organizations that do not need or want to register data as branch versioned, attribute calculation rules can be considered to obtain full auditing capabilities. A combination of editor tracking and archiving already provides the information on who and when a record was inserted and updated in a dataset, so all that is missing is by whom and when a record was deleted. 

Calculation rules are used to automatically populate attribute configurations on a feature. They can be created as immediate calculation rules or batch calculation rules.

Calculation rules that are evaluated during edit operations based on certain triggering events are referred to as immediate calculation rules. This type of calculation rule is used in workflows in which calculations must be performed automatically as edits are made.

Immediate calculation rules are evaluated at the time of the edit operation, based on specific triggering events, such as insert, update, or delete.

It is possible to capture who deleted a record in a table or feature class using archiving, edit tracking and an attribute calculation rule to populate a separate logging table with the auditing information each time a record is deleted. The workflow to do this is as follows:

• Enable editor tracking on the dataset.
• Enable archiving on the dataset.
• Enable GlobalIDs on the dataset.
• Create a separate logging table in the enterprise geodatabase with the following fields:
  • FeatureName (Text)
  • FeatureObjectID (Long)
  • DeletedBy (Text)
  • DeletedAt (Date)
• Create an immediate calculation rule that triggers whenever a record is deleted and inserts a new record into the logging table. A sample of the Arcade expression is as follows:

return {
       "edit": [  
           {  
               //the other class we want to edit
               "className" : "xxxxxxxx",
               "adds" : [
                      {
                            "attributes":  
                             {
                                   "FeatureName": "xxxxxx",
                                   "FeatureObjectID": $feature.OBJECTID,
                                   "DeletedBy":  GetUser(GetFeatureSet($feature)).username,
                                   "DeletedAt": Today()
                               },
                      }
             ]
            }
     ]
}

• When deleting a record in the dataset, a new record is created in the logging table to capture the time and user that deleted a record in a specific table.

While using an immediate attribute calculation rule does require creating an additional logging table in the enterprise geodatabase and an attribute calculation rule for each dataset that is needed to track the deletes for, it provides a solution that does not require registering the data as branch versioned.

Which methods (or combination of methods) to use depends on the organization's specific requirements and desired capabilities.