Management

Hi Szymon

Everyone's use and needs for archiving are different- I would start with asking

Why you are archiving the events ?

          are there regulatory mandates for you to keep all events for a specific period of time  ?

         are there company rules that state you have to keep events for a period of time ?

         or are you archiving for some other reason ?

This should enable you to determine the maximum time period you need to keep them for.

Then figure out how to measure the age of an event - FirstOccurrence or LastOccurrence again that will be relevant to why you are archiving them.

Next review which events are being sent to the database - each and every event - or just events that are important to you ?

Are there events that are being archived for which there is never any subsequent query from the database ? if so you may want to configure the gateway not to archive such events .

When you have defined which events are important and how long you need to keep them you can then put in place a protocol  to delete from the database where for example events are older than say 120 days and you can add to that, filters to act on specific classes of events.

The mode of the reporter database also influences the size of the database

Is your reporter database running in Reporter or Audit mode ? if it is Audit Mode review if you absolutely need to run in that mode .

Are you archiving from collection layers and or aggregation ?

In addition to event data - journal and details information is also often archived - you may determine that you may or may not need to store this additional information and so may elect to remove it from the database

Here is a link to a support page that may help

https://www.ibm.com/support/pages/remove-redundant-data-reporter-schema-database

Hope that helps

John

• The simplest answer for a reason why I'm archiving events is beacuse I'm using Event Analystics feature of NOI so it's required
• General assumption would be to keep them for the last 12 months
• All events are sent now to db
• I don't know if there are events that are being archived for which there is never any query because again it's for analytics so I'm observing all events that happen in the network
• About the protocol you mention. That's the bit I'm missing. How do I delete old events and release free space? Are there any scripts (run on-demand or cron) provided by NOI or best practicies? I don' see any particular tools or settings to help maintaining REPORTER database. But documentation is huge so maybe I just can't find it.
• I'm running in Reporting mode as per NOI configuration documentation for Gateway for JDBC
• It's a single layer rchitecture of Omnibus

Hi Szymon

Everyone's use and needs for archiving are different- I would start with asking

Why you are archiving the events ?

          are there regulatory mandates for you to keep all events for a specific period of time  ?

         are there company rules that state you have to keep events for a period of time ?

         or are you archiving for some other reason ?

This should enable you to determine the maximum time period you need to keep them for.

Then figure out how to measure the age of an event - FirstOccurrence or LastOccurrence again that will be relevant to why you are archiving them.

Next review which events are being sent to the database - each and every event - or just events that are important to you ?

Are there events that are being archived for which there is never any subsequent query from the database ? if so you may want to configure the gateway not to archive such events .

When you have defined which events are important and how long you need to keep them you can then put in place a protocol  to delete from the database where for example events are older than say 120 days and you can add to that, filters to act on specific classes of events.

The mode of the reporter database also influences the size of the database

Is your reporter database running in Reporter or Audit mode ? if it is Audit Mode review if you absolutely need to run in that mode .

Are you archiving from collection layers and or aggregation ?

In addition to event data - journal and details information is also often archived - you may determine that you may or may not need to store this additional information and so may elect to remove it from the database

Here is a link to a support page that may help

https://www.ibm.com/support/pages/remove-redundant-data-reporter-schema-database

Hope that helps

John

Hi Szymon

Event Analytics: are you referring to the Impact Based Event Analytics or CNEA ?

For how to delete :

 BP guides :  https://www.ibm.com/docs/en/noi/1.6.3?topic=developer-best-practices

Specifically this is taken from the Netcool OMNIbus BestPractice guide

https://www.ibm.com/docs/en/SSTPTP_1.6.3/noi-pdfs/IBM_Netcool_OMNIbus_8.1_Best_Practices_v1.1-SECOND-EDITION.pdf

Housekeeping historical event archive data 
Periodically a database administrator will need to purge old historical event data from the historical archive database. The following sample SQL shows how to delete data from the historical event archive that is older than 60 days for both DB2 

The three key tables are the reporter_status, reporter_journal and reporter_details tables.

Where the Gateway is running in REPORTER mode, the audit tables that track changes in Severity, Acknowledged state, who the OwnerUID and OwnerGID also need to be cleaned.

delete from reporter_status where lastoccurrence < CURRENT DATE - 60 DAYS;
delete from reporter_details where identifier not in (select identifier from reporter_status);
delete from reporter_journal where chrono < CURRENT DATE - 60 DAYS;
delete from rep_audit_severity where enddate < CURRENT DATE - 60 DAYS;
delete from rep_audit_ack where enddate < CURRENT DATE - 60 DAYS;
delete from rep_audit_ownergid where statechange < CURRENT DATE - 60 DAYS;
delete from rep_audit_owneruid where statechange < CURRENT DATE - 60 DAYS;

If you wanted to automate this you chron a job to run a sql script that logged into db2 and issued the above commands

db2 -td@ -vf  prune.reporter.sql

then in the file prune.reporter.sql

CONNECT TO reporter USER db2inst1 USING db2inst1Password @

delete from reporter_status where lastoccurrence < CURRENT DATE - 60 DAYS@
delete from reporter_details where identifier not in (select identifier from reporter_status)@
delete from reporter_journal where chrono < CURRENT DATE - 60 DAYS@
delete from rep_audit_severity where enddate < CURRENT DATE - 60 DAYS@
delete from rep_audit_ack where enddate < CURRENT DATE - 60 DAYS@
delete from rep_audit_ownergid where statechange < CURRENT DATE - 60 DAYS@
delete from rep_audit_owneruid where statechange < CURRENT DATE - 60 DAYS@

Personally I would simply connect to db2

su db2inst1  

cd

db2

connect to reporter

and then copy and paste the delete commands at db2 mindful that the examples are set for 60 days not 12 months

------------------------------
john postoyko
IBM
London

Original Message:
Sent: Wed January 05, 2022 05:57 AM
From: Szymon Trocha
Subject: What is the recipe to prune historical data in REPORTER database?

Hi John,

Thank you for your reply. I will try to answer your points to provide better picture:

• The simplest answer for a reason why I'm archiving events is beacuse I'm using Event Analystics feature of NOI so it's required
• General assumption would be to keep them for the last 12 months
• All events are sent now to db
• I don't know if there are events that are being archived for which there is never any query because again it's for analytics so I'm observing all events that happen in the network
• About the protocol you mention. That's the bit I'm missing. How do I delete old events and release free space? Are there any scripts (run on-demand or cron) provided by NOI or best practicies? I don' see any particular tools or settings to help maintaining REPORTER database. But documentation is huge so maybe I just can't find it.
• I'm running in Reporting mode as per NOI configuration documentation for Gateway for JDBC
• It's a single layer rchitecture of Omnibus

Hi Szymon

Everyone's use and needs for archiving are different- I would start with asking

Why you are archiving the events ?

          are there regulatory mandates for you to keep all events for a specific period of time  ?

         are there company rules that state you have to keep events for a period of time ?

         or are you archiving for some other reason ?

This should enable you to determine the maximum time period you need to keep them for.

Then figure out how to measure the age of an event - FirstOccurrence or LastOccurrence again that will be relevant to why you are archiving them.

Next review which events are being sent to the database - each and every event - or just events that are important to you ?

Are there events that are being archived for which there is never any subsequent query from the database ? if so you may want to configure the gateway not to archive such events .

When you have defined which events are important and how long you need to keep them you can then put in place a protocol  to delete from the database where for example events are older than say 120 days and you can add to that, filters to act on specific classes of events.

The mode of the reporter database also influences the size of the database

Is your reporter database running in Reporter or Audit mode ? if it is Audit Mode review if you absolutely need to run in that mode .

Are you archiving from collection layers and or aggregation ?

In addition to event data - journal and details information is also often archived - you may determine that you may or may not need to store this additional information and so may elect to remove it from the database

Here is a link to a support page that may help

https://www.ibm.com/support/pages/remove-redundant-data-reporter-schema-database

Hope that helps

John