Managing Log Files By Chuck Stermer
If you are processing a lot of snmptraps and log messages, the LogMessage Table in the Collage DB (which feeds the console) can grow quite large. There are no upper bounds on this, so as the database tables get bigger the query times get longer. This impacts the usability of the console. Also, if left unchecked it could chew up a lot of diskspace. So, if you have too many messages in the Console View of your GroundWork Portal, or if the console is too slow to be useful, you may consider the following options:
Data Retention in the Console Tables (Too Many Messages in the Console)
Log Messages
The LogMessage Table in the Collage DB (which feeds the console) can grow quite large. This is particularly true if one is processing a lot of snmptraps and log messages. There are no upper bounds on this, so as the database tables get bigger the query times get longer. This impacts the usability of the console. Also, if left unchecked it could chew up a lot of diskspace.
This procedure explores the two tables involved in the console and discusses sql queries that can help manage this data.
Backup the Collage DB
First, do a mysqldump of the GWCollageDB in case something goes wrong:
# mysqldump GWCollageDB > /root/GWCollageDB.sql
Stop the GroundWork Feeder Services
GroundWork has several cron job activated services that feed data from other subsystems into its databases. You need to stop these feeder processes.
# service gwservices stop
Access the Collage DB
Establish a DB connection to the Collage DB.
# mysql GWCollageDB
mysql>
Marking Messages as Processed.
If you have lots of messages visible in the console and want to clear some of them out you can flag them as processed (and therefore not showing in the console) by changing the 'OperationStatusID' field in the LogMessage table to 4.
The following SQL update statment will flag as processed any messages with a 'FirstInsertDate' earlier than Aug 1, 2006.
mysql-> update LogMessage set OperationStatusID=4 WHERE FirstInsertDate <= '2006-08-01 00:00:00';
This will flag as processed all entries older than August 1st and make them disappear from the console. The entries will still be in the table, but will not be displayed in the console. This is what happens when you do a submit from within the console GUI.
Deleting Older Console Events Messages.
At some point you will need to delete these entries from the table.
The thing to bear in mind here is that there are actually two tables involved - keyed by the LogMessageID. The two tables are LogMessage and LogMessageProperty.
> mysql> desc LogMessage; +-----------------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+----------+------+-----+---------+----------------+ | LogMessageID | int(11) | NO | PRI | NULL | auto_increment | | ApplicationTypeID | int(11) | NO | MUL | | | | DeviceID | int(11) | NO | MUL | | | | HostStatusID | int(11) | YES | MUL | NULL | | | ServiceStatusID | int(11) | YES | MUL | NULL | | | TextMessage | text | NO | | | | | MsgCount | int(11) | NO | | 1 | | | FirstInsertDate | datetime | NO | | | | | LastInsertDate | datetime | NO | | | | | ReportDate | datetime | NO | | | | | MonitorStatusID | int(11) | YES | MUL | NULL | | | SeverityID | int(11) | NO | MUL | | | | ApplicationSeverityID | int(11) | NO | MUL | | | | PriorityID | int(11) | NO | MUL | | | | TypeRuleID | int(11) | NO | MUL | | | | ComponentID | int(11) | NO | MUL | | | | OperationStatusID | int(11) | NO | MUL | | | +-----------------------+----------+------+-----+---------+----------------+ 17 rows in set (0.04 sec) mysql> desc LogMessageProperty; +----------------+------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------------------+-------+ | LogMessageID | int(11) | NO | PRI | | | | PropertyTypeID | int(11) | NO | PRI | | | | ValueString | text | YES | | NULL | | | ValueDate | datetime | YES | | NULL | | | ValueBoolean | tinyint(1) | YES | | NULL | | | ValueInteger | int(11) | YES | | NULL | | | ValueLong | bigint(20) | YES | | NULL | | | ValueDouble | double | YES | | NULL | | | LastEditedOn | timestamp | YES | | CURRENT_TIMESTAMP | | | CreatedOn | timestamp | YES | | 0000-00-00 00:00:00 | | +----------------+------------+------+-----+---------------------+-------+ 10 rows in set (0.00 sec)
The LogMessage table contains a FirstInsertDate - which we can use in a delete using a delete command somewhat similar to the update command shown above: HOWEVER - before you do this, consider what the result will be. We will delete a bunch of records from LogMessage, but will still have entries in the LogMessageProperties table that point to keys (LogMessageID) that no longer exist. And to make matters worse, there are no dates in LogMessageProperties that I could use to delete from. So the smart thing to do would be something like this:
This delete query, a join in dbtalk, would delete the LogMessageProperty records that corresponded to the LogMessageId records where FirstInsertDate is earlier than Aug 1, 2006.
mysql> delete FROM LogMessageProperty WHERE LogMessageId IN ( SELECT FirstInsertDate FROM LogMessage WHERE FirstInsertDate <= '2006-08-01 00:00:00’);
Once the records are deleted from LogMessageProperties you can safely delete the same messages from the LogMessage table.
Now why did we insist that you stop gwservices first? Because if more records are inserted between the first delete and the second delete, you may wind up with records in LogMessageProperties that point to LogMessageIDs in the LogMessage table that no longer exist. We don't want to break the referential intregrity of the database.
Deleting All Messages.
OK... suppose you just want to delete everything - perhaps its all test data and you are ready to move into production and want to wipe the slate....
Just make sure gwservices is stopped then from within mysql type:
mysql> delete from LogMessageProperties;
mysql> delete from LogMessage;
That should delete all the entries in the console.
Start the GroundWork Feeder Services
Oh, by the way, when you are finished don't forget to re-start gwservices.
