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?
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
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!
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.
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!
Now you have me curious about what these new KPI events are that are filling up the activity logs...
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!
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...
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.
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!!
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
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
Do you have any further questions regarding archive/purge within CA SDM?
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.
Retrieving data ...