CA Service Management

Expand all | Collapse all

Data for archiving and purging

  • 1.  Data for archiving and purging

    Posted Aug 15, 2017 10:19 AM

    My database for the ticket system is getting rather large and I am looking into archiving and purging some data. In addition to tickets, what other data should I consider for archiving and purging?



  • 2.  Re: Data for archiving and purging
    Best Answer

    Posted Aug 15, 2017 10:43 AM

    Hi Laura,  We would recommend archiving and purging things like tickets, activity log, event log.  Those are typically the largest tables.  You can read more about the archive/purge functionality here:  How to Archive and Purge Historical Data - CA Service Management - 17.0 - CA Technologies Documentation 

    That will help you get an understanding of how to configure it and use it. 

    Hope this helps

    Jon I.



  • 3.  Re: Data for archiving and purging

    Posted Oct 10, 2018 09:15 AM

    Hi Jon

    Interested in your reply here - we are looking to archive our activity log but this is not in the list of configuration objects for archive/purge. We are upgrading to r17 this wekeend and just checked it is still not there to select?

    Thanks in advance for your reply!



  • 4.  Re: Data for archiving and purging

    Posted Oct 10, 2018 09:53 AM

    Hi Mannihobar,

     

    We haven't started archiving and purging yet, but plan to start in January 2019. 

     

    The notes I've gathered from CA resources on this over the years says that the activity logs are archived as part of the call request, incident, problem, and change order rules....  See below.  Someone at CA should probably confirm that this information is accurate though.

     

    • Call Request – All fields in the Request Tickets including any custom fields and associated Activity Logs.
    • Incident – All fields in the Incident Tickets including any custom fields and associated Activity Logs.
    • Problem – All fields in the Problem Tickets including any custom fields and associated Activity Logs.
    • Change Order – All fields in the Change Order including any custom fields and associated Activity Logs.

     

    Thanks,

    Tammy



  • 5.  Re: Data for archiving and purging

    Posted Oct 10, 2018 10:02 AM

    Hi Tammy

     

    Thanks for your reply. Yes, we already have an archive rule for I/R/P but this is only for calls older than 5 years. I would like to archive a specific type of activity from the log which is adding 30k entries every day to our activity log (new KPI events). CA have confirmed that there is no out of the box solution to stop the activity type being added to the activity log and now it seems I can't archive or purge them either! Frustrating, which was why Jon's reply piqued my interest!

     

    best Regards

     

    Nick



  • 6.  Re: Data for archiving and purging

    Posted Oct 10, 2018 01:48 PM

    Hi Nick,

     

    Now you have me curious about what these new KPI events are that are filling up the activity logs...

     

    Tammy



  • 7.  Re: Data for archiving and purging

    Posted Oct 11, 2018 07:55 AM

    Ha! They are SLA events attached to the Transfer Activity Notification so we can record a kpi from when the ticket was received by a second line group rather than when the ticket was created by a first line one!



  • 8.  Re: Data for archiving and purging

    Posted Oct 12, 2018 05:23 PM

    Ha!  Thanks for letting me know what things to steer clear of!    Those KPIs require that the audit log be installed, right? 

     

    I have a quick question, if you don't mind.  How can you capture this accurately when the first level team is manually creating a ticket (customer called), and they initially save the ticket as assigned to an appropriate second level team?  There isn't an transfer activity recorded for those, so I'm curious...

     

    Thanks,

    Tammy



  • 9.  Re: Data for archiving and purging

    Posted Oct 11, 2018 06:08 AM

    Hello Nick,

     

    you can pretty safely delete desired data directly from database without bothering with archive/purge functionality, that I have found pretty useless many years ago. Biggest tables are usually "session_log" and "not_log". You can't correctly purge not_log because you can base your rules on the table itself, so you can't make sure that data being purged doesn't belong to open ticket as was in our case.

    As for activity log, there are two tables. For R/I/P it is "act_log" and for changes it is "chgalg".

    List of big tables can be easily obtained from your DB. For MS SQL just right click on your DB and go to Reports => Standard Reports => Disk Usage by Top Tables

     

    And if you are afraid of touching DB directly, then you can do it through pdm_extract / pdm_load combo. That way you will get backup of data too, in case you delete more than desired

     

    For example deleting activities that happened before 1.1.2018 and are of type callback with description test is done this way:

    DELETE FROM act_log 

    WHERE last_mod_dt < 1514764800

       AND type = 'CB'

       AND description = 'test'

     

     

    I hope these hints will help you achieve your goal.



  • 10.  Re: Data for archiving and purging

    Posted Oct 11, 2018 07:58 AM

    Hi

     

    I hadn't thought of the pdm_extract then pdm_load -r option - that would be an excellent workaround and no more or less than what the archive does. I'll use a script to automate it - many thanks!!

    Regards

    Nick



  • 11.  Re: Data for archiving and purging

    Broadcom Employee
    Posted Aug 15, 2017 10:56 AM

    Laura, one approach would be you talk to your DBA to see which tables have huge number of records and you try to

    archive and purge those tables. For example, session_log table. Thanks _Chi



  • 12.  Re: Data for archiving and purging

    Posted Aug 15, 2017 02:19 PM

    Agreed!   Session log can simply be truncated - it has no affect on the product at all. Nothing references it.  Its solely used for auditing outside of the product.  So thats one easy one that can be done right away



  • 13.  Re: Data for archiving and purging

    Broadcom Employee
    Posted Aug 17, 2017 10:55 AM

    Laura.........

     

    Do you have any further questions regarding archive/purge within CA SDM?



  • 14.  Re: Data for archiving and purging

    Posted Aug 23, 2017 03:14 PM

    Not at this time. Thanks for the suggestions!

     

    I do see that the not_log table is the biggest table in the database, so that will probably be the table I target first.