Find the date and time when a user is deleted

Products

• CA Release Automation - Release Operations Center (Nolio)
• CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

How to find the date and time when an user is deleted ?

Environment

Release Automation - All Versions

Resolution

Following SQL Query could be used to find in database the date when a user has been deleted :

select u.username 'Deleted user',
       dateadd(ss, dre.timestamp/1000+datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Delete Date', 
       dre.username
  from auditingentry ae
  inner join revision_auditingentry rae on rae.auditentries_id = ae.id
  inner join designrevisionentity dre on dre.id = rae.designrevisionentity_id
  left outer join users_aud u on u.id=ae.entityid   
  where ae.action = 1 and ae.entity = 30
order by dre.timestamp desc

 

In above example user test123 has been deleted on 23 May 2025 at 23:33:05 by superuser

Here is the SQL Query for Oracle :

SELECT 
       to_char(to_date('01011970', 'DDMMYYYY') + ( 1 / 24 / 60 / 60 / 1000) * DRE.TIMESTAMP, 'DD.MM.YYYY HH24:MI:SS') "Delete Date",
       U.USERNAME "Deleted User",
       DRE.USERNAME
  FROM AUDITINGENTRY AU
  LEFT OUTER JOIN REVISION_AUDITINGENTRY RAD ON RAD.AUDITENTRIES_ID = AU.ID
  LEFT OUTER JOIN DESIGNREVISIONENTITY DRE ON DRE.ID = RAD.DESIGNREVISIONENTITY_ID 
  LEFT OUTER JOIN USERS_AUD U ON U.ID=AU.ENTITYID
WHERE AU.ACTION=1 AND AU.ENTITY=30
ORDER BY DRE.TIMESTAMP DESC;

Additional Information

There are 2 important remarks :

- This is working if "Audit Design Changes" has been set to true in the System Settings :

If it is set to false, set it to true and restart Nolio Service on the NAC. The next time a user is deleted, the action will be stored in the audit tables (like auditentry, users_aud etc..)

- In Version 6.9.0 and 6.8.5 (and lower version), deleted user name was not put in table users_aud.
In below example, in lines 2,3 and 4 we could see that a user was deleted. We known the date but we do not know the deleted username.

Starting version 6.8.6 and version 6.9.2, the deleted user is put in table users_aud.
In above example, at line 1 we could see the deleted username and the deletion date.