How to: find most logged events

Info:

Venafi Trust Protection Platform does extensive logging by default. In some cases we may run into a situation where the Log table grows large enough to affect system performance. This article describes steps to analyze what events are taking the most space in the Database.

More Info:

The following query will give us the top 10 most logged events in the Database.

Oracle:

SELECT * FROM (select to_char(EventID,'xxxxxxxx') as EventID, COUNT(EventID) as total FROM TPP.log group by EventID order by COUNT(eventid) desc) WHERE ROWNUM <= 10;

Microsoft SQL:

SELECT top 10 CONVERT(VARBINARY(8), EventID), COUNT(EventID) as total FROM log group by EventID order by COUNT(eventid) desc;

The results will look similar to the following:

The EventID colum shows the EventID in hex format. Total column shows number of these events in the log table.

Once we have the EventIDs we can look up what they translate to by using the Event ID Messages KB articles or by looking at the Event Definitions. For example by looking at KB https://venafi.zendesk.com/entries/44920126 we can determine that our top event 9000c translates to event "Certificate Manager - Processing Stage".

After deciding which events we do not want to log we can do the following:

1. Create a Filter Channel for the Events
2. Delete the events from the DB. (See an example on how to delete millions of rows without creating a big transaction file. The query needs to be updated to delete based on EventIDs)
3. Check that Log Expiration is configured