Automic Workload Automation

Expand all | Collapse all

Locating when an object was deleted

Jump to Best Answer
  • 1.  Locating when an object was deleted

    Posted 08-09-2019 09:24 AM
    Is there a query or way to search in the archive browser for when an object was deleted? We have an object that was deleted over a year ago (based on the last time we know it ran) and now we are being question about who why when. If we can see Who and or When it will help us locate backing documentation. Follow up question, how do y'all handle tracking things like this?


  • 2.  RE: Locating when an object was deleted

    Posted 08-09-2019 09:37 AM
    We track all production changes via a ServiceNow change ticket.


  • 3.  RE: Locating when an object was deleted

    Posted 08-09-2019 09:41 AM

    We theoretically have a request somewhere in our ticketing systems but they are fairly freeform. I'm hoping when /who results help narrow down tickets to check manually.

     

    Larry Halseth

    SE2, Prod Support Analyst IV

    (o)785.438.3212 (m)785.380.4272

     






  • 4.  RE: Locating when an object was deleted

    Posted 08-09-2019 09:42 AM
    Edited by Carsten Schmitz 08-09-2019 09:44 AM
    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)



  • 5.  RE: Locating when an object was deleted

    Posted 08-09-2019 09:50 AM

    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

     






  • 6.  RE: Locating when an object was deleted

    Posted 08-09-2019 10:04 AM
    ​No idea, sorry. Have never seen one, would not be surprised if this isn't logged for objects that are cleaned up.


  • 7.  RE: Locating when an object was deleted

    Posted 08-09-2019 01:04 PM
    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)
    ------------------------------



  • 8.  RE: Locating when an object was deleted

    Posted 08-09-2019 01:12 PM

    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


  • 9.  RE: Locating when an object was deleted

    Posted 08-09-2019 01:29 PM
    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)
    ------------------------------



  • 10.  RE: Locating when an object was deleted

    Posted 08-09-2019 01:33 PM

    I'm sure we'll have some talks around these ideas. Thanks everyone.

     

    Larry Halseth

    Prod Support Analyst IV

     

    5D0BABB5-A823-40D1-B8E1-5355F6D8367C@dc.rr.com

    ..............................................................................................

    5801 S.W. 6th Avenue |  Topeka, KS 66636

    o 785.438.3212 m 785.380.4272

    larry.halseth@se2.com  |  www.SE2.com[SE2.com]

     

    SE2 Confidentiality Notice: This email and any files transmitted with it are private, confidential and solely for the use of the intended recipient. It may contain material that is legally privileged, proprietary or subject to copyright belonging to SE2 and its subsidiaries, and it may be subject to protection under federal or state law. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and delete the material from any computer.





  • 11.  RE: Locating when an object was deleted
    Best Answer

    Posted 08-12-2019 04:15 AM
    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".





  • 12.  RE: Locating when an object was deleted

    Posted 08-12-2019 11:45 AM
    Edited by Pete Wirfs 08-12-2019 11:46 AM
    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)
    ------------------------------



  • 13.  RE: Locating when an object was deleted

    Posted 08-13-2019 10:44 AM
    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