Tracking user who has deleted the record in the archive table

3026
9
12-05-2012 06:37 AM
Status: Open
Labels (1)
AndriusBogvila
New Contributor II

At the moment Editor tracking records the folowing information:

  • The name of the user who created it.
  • The date and time it was created.
  • The name of the user who edited it.
  • The date and time it was last edited.

This is great feature, however when archiving is enabled it misses such a very important information:

  • The name of the user who deleted it.
  • The date and time it was deleted.
9 Comments
VytautasGipiskis
Hello,

Actually geodatabase archiving combined with editor tracking needs to be used there. There is a bug NIM085153 on this issue, and it shoud be easy to implement. 


VytautasGipiskis
Hello,

Actually geodatabase archiving combined with editor tracking needs to be used there. There is a bug NIM085153 on this issue, and it shoud be easy to implement. 


Cesar_RafaelLopes
vgipiskis,

The geodatabase archiving is only possible in versioned geodatases and versions are only supported in ArcSDE geodatabases.

The editor tracking is available in non ArcSDE geodatabases, so it would be great to had a similar function to archiving in non ArcSDE Geodatabases.

Thanks!
ThomasColson

I've done this in the past on SDE tables About Change Data Capture (SQL Server) | Microsoft Docs  but it's not worth all of the extra steps. If you need to establish chain of custody for a forensic IT investigation, you can get the user name of whom deleted a record from system tables and transaction logs (again, a PITA), provided you're doing daily or incremental backups.  

Lake_Worth_BeachAdmin

I agree with this statement. you can accomplish OP's idea directly in SQL Server (Or any other RDBMS) but its not easy to set up if you're not a professional DBA.

Having this capability directly in ESRI products would be beneficial.

PaulBarr

This is still an issue a decade later in 2022.

I am using archiving and editor tracking. But the username of the user performing the delete action is is still not captured.

How can this be implemented?

I can find no record of bug NIM085153 and its resolution, or otherwise.

PaulBarr

It is now possible to capture who deleted a record in a table with standard archiving and user tracking using attribute rules.

It is not possible to capture the user editing the data into the same table (as the row just gets ended).

It is possible to capture deletes into separate logging table.

PaulBarr_0-1667948742196.png

Note this is an old screen capture, the Field should exist in your table that is being tracked. It doesn't matter which one, it is not being modified, it just needs to exist so that a calculation rule can be triggered on delete to place a record in the logging table.

There is no point in having a column called DeletedByfield within the table being tracked, as it is not possible to populate the result into the edited table during a delete.

Here is the arcade expression for the attribute rule:

return {
       //we want to just return the value of field `Field` no change require
      "result": $feature.DeletedByUser,
       //this keyword indicates an edit that need to happen, its an array since we can make many edits
       "edit": [  
           {  
               //the other class we want to edit
               "className" : "GIS.CCC.TestDeleteLog",
               //the type of edit, in this case we want to add so we say `adds`, its an array since we can make many inserts
               "adds" : [
                       
                      {
                            //the attribute of the feature we want to add,
                            // we only want to populate the pointGuid with the globalId of the feature being created
                            "attributes":  
                             {
                                   "FeatureName": "TESTDELETEUSERTRACKING",
                                   "FeatureObjectID": $feature.OBJECTID,
                                   "FeatureGISID": $feature.OBJECTID,
                                   "DeletedBy":  GetUser(GetFeatureSet($feature)).username,
                                   "DeletedAt": Today()
                               }//,
                            //we want to use the buffered geometry to insert the polygon
                           // "geometry": none
                      }
             ]
            }
     ]
}

 

Useful web references
User Idea to allow a calculation rule without specifying another field. (not implemented).
Edit another feature class with a calculation rule.
MartinKrál

Hello,

great idea @PaulBarr 

We just could not get right credentials in code of described delete attribute rule.
I mean for deletes from ArcGIS Pro directly connected to database to get database login, and for deletes via feature service to get portal login (same like in Editor Tracking).

Fortunately instead of directly writing DeletedBy/DeletedAt in code of delete attribute rule we just enabled editor tracking on logging table too, and
now it seems to work the way we wanted.


(created_user in logging table is simply the one who deleted record in original table).

Regards

alex_friant

The date of when a feature is deleted is stored in the SQL table of the same name suffixed by "_H", under the field "GDB_TO_DATE".

For example, if your feature class is called "schools" the archive table is called "schools_H".

They really just need to add a field for deletions like "DELETED_BY" so you could have the "WHO" of the "WHEN by WHO?" question.