Hi,
We built a similar script as below based on the tables listed in the content store cleanup scripts available in the installation folders.
-- This script deletes records from tables for Microsoft SQL Server database.
-- Parameters:
-- 1) Replace AUDITDB_SCRIPT_TIMESTAMP to the timestamp value provided by the user.
DECLARE @AUDITDB_SCRIPT_TIMESTAMP datetime = '2018-01-01'
delete FROM COGIPF_ACTION where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_PARAMETER where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_NATIVEQUERY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_RUNJOB where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_RUNJOBSTEP where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_RUNREPORT where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_USERLOGON where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_VIEWREPORT where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_EDITQUERY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_AGENTRUN where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_AGENTBUILD where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_MIGRATION where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_THRESHOLD_VIOLATIONS where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_POWERPLAY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_HUMANTASKSERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_ANNOTATIONSERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
delete FROM COGIPF_MOBILESERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
There used to be a cleanup tool in earlier versions of Cognos (https://www.ibm.com/support/pages/node/315223) but now that Cognos is offering a paid Toolkit similar to Motio, BSP Metamanager etc, they removed access to all such utilities from public domain.
Regards,
------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi------------------------------
Original Message:
Sent: Wed April 07, 2021 11:14 AM
From: Tony Barlas
Subject: Cognos Audit database pruning records
No problem. I'm surprised that it not more information about pruning records from the audit db is available out in the wild. This scripts was designed using Cognos 11.1.7.
------------------------------
Tony Barlas
Original Message:
Sent: Wed April 07, 2021 11:07 AM
From: Vincent Dsouza
Subject: Cognos Audit database pruning records
Awesome !!!, Thanks Tony for sharing this script to the community, these script are of use to me.
------------------------------
Vincent Dsouza
Original Message:
Sent: Wed April 07, 2021 10:45 AM
From: Tony Barlas
Subject: Cognos Audit database pruning records
We are planning on holding only 2 years of history in your audit db. I haven't found much help online about pruning records in the audit database. I come up with this, but was curious what other do.
SQL Statement:
delete FROM COGIPF_ACTION where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_NATIVEQUERY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_PARAMETER where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_RUNJOB where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_RUNJOBSTEP where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_RUNREPORT where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_USERLOGON where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_VIEWREPORT where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_EDITQUERY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_AGENTRUN where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_AGENTBUILD where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_MIGRATION where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_THRESHOLD_VIOLATIONS where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_POWERPLAY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_HUMANTASKSERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_ANNOTATIONSERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
delete FROM COGIPF_MOBILESERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
------------------------------
Tony Barlas
------------------------------
#CognosAnalyticswithWatson