Feedback
Did this article resolve your question/issue?

   

Article

How to use AI notes to find who deleted a record from a table ?

Information

 
TitleHow to use AI notes to find who deleted a record from a table ?
URL NameP18048
Article Number000132679
EnvironmentProduct: Progress
Version: 8.x, 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to find out who deleted a record from a table ?
How to find the DBKEY associated with the RECID of a database record ?
How to find the user associated with a record delete from the after-image notes ?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
The following conditions must exist:
  1. After Imaging must have been enabled at the time of the record deletion and the associated AI files available.
  2. You must know which table(s) the record(s) were deleted from.
  3. You must know which Storage Area the table(s) are stored.
  4. You must know either the recid(s) of the deleted record(s) or the blocknumber.
If all of the above information is known, the following example outlines how to determine who deleted the suspect record(s).

1) Review the database structure file "dbname.st" to confirm:

a. The records per block (RPB) value for the Storage Area the table belongs to.
b. The Storage Area Number corresponding to the Storage Area Name
     
$  prostrct list <dbname> dbname.st

2a) If the RECID is known, calculate the DBKEY from the RECID using the RPB value.

Divide the RECID by the records per block, then take the whole number from the result and multiply by RPB value again.

EXAMPLE:
RECID = 1366
RPB = 32

1366 / 32 = 42.6875
42 x 32 = 1344 = The first DBKEY in the block that was modified.

2b) If the BlockNumber is known, calculate the DBKEY from the BLOCKNUMBER using the RPB value.

Multiply the BlockNumber by the records per block

EXAMPLE:
BLOCKNUMBER = 42
RPB = 32

DBKEY = 42 x 32 = 1344 = The first DBKEY in the block that was modified

3) Parse the after-image files that were created between the time the record was known to exist to the time it can no longer be found.

3a) First extract the ai file transaction notes. For further detail refer to Article 
$   rfutil <dbname> -C aimage scan verbose -a <dbname>.a<n> > AIscan<n>.txt"

3b) Parse the resulting AIscan.txt file for the DBKEY and corresponding Storage Area number.

NOTE: the format of this file has changed over releases, the following are provided only as examples

EXAMPLE: (Progress 9)
...
Trid: 329 Fri Jan 24 10:46:16 2009. (2598)
User Id: fredthebad. (2599) <------------------- This displays the userID running the transaction responsible for the delete operation.
...
...
code = RL_RMDEL (1637) <-------------------- "RL_RMDEL" confirms that a record delete was performed.
transaction index = 329 (1638)
area = 9 dbkey = 1344 update counter = 11 (9016) <---- This line confirms the associated storage area number and dbkey.

EXAMPLE: (OpenEdge 10)
...
Trid: 3863 Tue Feb 02 15:42:15 2010. (2598)
Trid: 3863 User Id: fredthebad (12531) <------- This displays the userID running the transaction responsible for the delete operation.
...
...
Trid: 3863 code = RL_RMDEL version = 2 (12528) <------- "RL_RMDEL" shows that a delete was performed.
Trid: 3863 area = 9   dbkey = 1344   update counter = 83 (12529) <---- This line shows the storage area number and dbkey of the database BLOCK where the recid is placed

Before drawing conclusions, it is imperative to confirm the transaction end note and ensure the transaction was not undone.
 
Workaround
Notes
Last Modified Date11/20/2020 7:35 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.