Suggestion
Option 1 - Activate the Audit trail on all your clients, at least in Production environment. Setting in the UC_CLIENT_SETTING, parameter OBJECT_AUDIT, value Y. Then you will have the possibility to run a report extraction (utility ucybdbrr "revision report") to check all type of audit generated including a "Delete" type. You can generate the report daily for the last 24 hours and keep it as an archive. Depending how do you flag the audit records, you can keep them in the DB or remove them once exported during a maintenance/cleanup cycle. Report is in csv Windows format to be easy to manipulate.
Option 2 - Before running the maintenance/cleanup cycle you can run an SQL query to list all "deleted" objects in the DB like indicated above. If it is done as the very first step of the maintenance/cleanup cycle you should be able to get a trace of all suppression of objects.
But here you need to do this before any action or removal of records (when you run the utility ucybdbun with -BREORG option) because the action is not related to a client, it applies for all the objects in all the recycle bin of all clients.
Also, if someone is running the utility manually or in a non schedule maintenance/cleanup cycle, it will remove objects without leaving a trace of the action as it is not generating the list.
Now you can also combined both options to have something more secure and get two different set of information that are giving a good picture of what happen.
FYI the adutit trail on the DB is not using too much space or resources as it is focused only on the objects modifications and more generally on any manual action. Recording is done in the XAO table for those who wants ore details or monitor DB tables involved in this.
Hope this can help you in future, unfortunately nothing can be done for the past once object records are removed from the DB.
Alain
Original Message:
Sent: 08-12-2019 11:45 AM
From: Pete Wirfs
Subject: Locating when an object was deleted
Nice solution @Torsten Goehler ! I wish this forum had "like" buttons?
Ah, found the "recomend" button. I guess that will do. :)
------------------------------
Pete (AE V11.2)
Original Message:
Sent: 08-12-2019 04:15 AM
From: Torsten Goehler
Subject: Locating when an object was deleted
For a previous company I generated a daily deletion report.
A simple sqli, where the data has been used for sending a daily mail which objects have been deleted.
Something like this:
select oh_client, oh_name, oh_idnr, oh_moddate, usr_lastname, usr_firstname from oh
inner join usr on usr_oh_idnr = oh_moduseridnr
where oh_deleteflag = 1 and oh_name not like '%OLD%'
It was a bit more and we did a bit more, but I don't have access to this, so just a quick and dirty exmaple.
Of Course wouldn't help keeping users from accidentaly deleting objects, but at least good to know "when".
Original Message:
Sent: 08-09-2019 01:29 PM
From: Pete Wirfs
Subject: Locating when an object was deleted
We do not have that turned on. I hope it works for you.
We capture a daily UCYBDBRT report to list all objects that were modified in the previous 24 hours. This has been useful for exposing what the staff are doing on a daily basis. But this too is incapable of reporting delete operations. If someone did an accidental delete and we didn't catch it until a week later (we run cleanup weekly), I don't know that we would be in a good place.
I think one admin here mentioned they run a regular full-export of their objects so they have an easy-to-restore-from backup. I could see taking that approach too.
------------------------------
Pete (AE V11.2)
Original Message:
Sent: 08-09-2019 01:12 PM
From: Larry Halseth
Subject: Locating when an object was deleted
I basically hit a dead end, but was informed of the AE DB Revision Report tool by support, which requires a variable to be set in the client settings. So, going forward I'll be able to track this down. I don't know what the impact to the database will be though.
I've also cranked up the number of versions and statistics (I could have checked workflow monitors for when the object disappeared).
https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Utilities/admin_AboutAEDBRevisionReport.htm#link1
Original Message:
Sent: 08-09-2019 01:03 PM
From: Pete Wirfs
Subject: Locating when an object was deleted
I don't believe a delete operation generates a MELD row. When the object is deleted, it occurs "logically" in that it throws a switch in a table so the next archive/cleanup run knows to nuke it. It is an interesting question though of weather or not archive/cleanup simply removes the object from the database, or archives it first. I don't know.
Hopefully you know where the archive files are stored. They are stored in simple text files, so a folder search tool (my favorite is called Agent Ransack) should be able to easily search all of them for the object in question.
Our practice is to not delete anything until it has exceeded our records retention rules of 3 years. We rename them, giving them a prefix of "RETIRED" so we know to come back and delete them later. And by keeping it in the system for 3 years means we have a better audit trail of the who/what/when information.
Keep us informed... this is an interesting problem.
------------------------------
Pete (AE V11.2)
Original Message:
Sent: 08-09-2019 09:49 AM
From: Larry Halseth
Subject: Locating when an object was deleted
They are definitely cleaned up. Any idea what the message in the meld table when an object is deleted, if any? I've also opened a ticket with support for this but figured response might be faster here since it's not a system down issue.
Larry Halseth
SE2, Prod Support Analyst IV
(o)785.438.3212 (m)785.380.4272
Original Message------
From top of my head:
First, check the recycle bin in Automic, maybe the object is in there.
If not, all objects are in the OH table, and IIRC it has a field called "OH_DELETEFLAG". So if you're lucky and your cleanup process has not yet removed the object entirely, you might still be able to find it's meta data there with an SQL client and a query such as "select * from OH where OH_NAME like 'theName%';" eventhough the object is not visible in the UI anymore. If you do select on OH_DELETEFLAG, be aware that it can take the values 0, 1, 2 or 5.
If it's been cleaned up, you'd probably need the archive files left behind by the cleanup utility and the archive browser. I've never had this specific scenario, so I can't really tell you what to look for or how, you'd just have to open these archive files with the windows tool and see if there's anything useable in there. Be aware the archive browser often times crashes when being fed large files or too many files, then you need to feed smaller files or bits of files to it one after another.
As for your "policy" question, we don't have a good way to deal with tracking deleted objects either. We backup some archive files but never had to resort to looking through those. I myself often export old objects as XML and keep them in a folder, but we have no way to enforce this on every account holder.
Hth,
(edit: changed the suggested query to a "like" query, so it catches object names with automatically added suffix)