Cleanup

PowerShell Cleanup Script

Cleanup logfiles when file change date is older than 30 days…

<#
    Description    : Cleanup logfiles when file change date is older than 30 days...
    Keywords       : Files, Change, Cleanup
    Majorversion   : 1
    Author         : Tim van Kooten Niekerk
    Date           : 2011-11-10
#>

<#      CHANGELOG:
        2013-12-12 => Add Headers (Tim van Kooten Niekerk)
#>

# Global config settings...
[string]$sDirName = "D:\LogFiles\W3SVC705438624"
[string]$sFileNameMask = "*.log"
[string]$sGCIMask = $sDirName + "\" + $sFileNameMask
[string]$sEventLogMessage = "### Logfile cleanup script started ###`r`n"

# Start Main script...
if (Test-Path $sDirName) {
get-childitem $sGCIMask | 
	% { 
		if ($_.LastWriteTime -lt (Get-date).AddDays(-30)) {
		remove-item $_.fullname
		$sEventLogMessage = $sEventLogMessage + "File " + $_.fullname + " (" + $dFileModTime.DateTime + ") removed...`r`n"
		}
	}
}

# Cleanup...
[string]$sEventLogMessage = $sEventLogMessage + "### Logfile cleanup script finished ###"
write-eventLog -LogName "Windows PowerShell" -Source "PowerShell" -EventID 30001 -Message $sEventLogMessage -EntryType Information

PowerShell Remove Old Files From a Directory

Script for removal of old backup or logfiles from a specific directory. The example below removes all files from a directory that are older than 90 days. A log of all files that are removed is written to the windows eventlog.

# Global config settings...
[string]$sDirName = "D:\LogFiles"
[string]$sFileNameMask = "*.log"
[string]$sGCIMask = $sDirName + "\" + $sFileNameMask
[string]$sEventLogMessage = "### Cleanup Script Started... ###`r`n"

# Functions and Procedures...
function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Start Main script...
if (Test-Path $sDirName) {
get-childitem $sGCIMask | 
    % { 
        # Get File Last Write Time...
        [datetime]$dFileModTime = fGetFileModTime($_.fullname)
        if ($dFileModTime -lt (Get-date).AddDays(-90)) {
        remove-item $_.fullname
        $sEventLogMessage = $sEventLogMessage + "File " + $_.fullname + " (" + $dFileModTime.DateTime + ") removed...`r`n"
        }
    }
}

# Finish and Cleanup...
[string]$sEventLogMessage = $sEventLogMessage + "### Cleanup Script Finished... ###"
write-eventLog -LogName "Windows PowerShell" -Source "PowerShell" -EventID 30001 -Message $sEventLogMessage -EntryType Information

MySQL Table Cleanup Procedure

With the following procedure, it is possible to move data from a primary table to a secondary table for archival purposes. This specific example moves data on the basis of a date column in the primary table. The number of days (related to ┬áthe current date/time) to remain in the primary table is passed to the procedure. Start procedure with ‘CALL pCleanUPTable (35)’.

DELIMITER //

CREATE PROCEDURE pCleanUpTable(IN iDays INT)

  BEGIN
    START TRANSACTION;
    SELECT (now() - interval iDays day) INTO @dtDeleteFrom;
    SAVEPOINT PreCleanUP;
    -- Copy old records to new archive/secundary  table...
    INSERT INTO sectab (primtab_id, value1, value2, value3, datum)
    SELECT id, value1, value2, value3, datum from primtab where datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsCopy;

    -- Remove records from primary table...
    DELETE FROM primtab WHERE datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsDelete;

    -- Check if rows copied -eq rows deleted...
    IF (@iRowsCopy = @iRowsDelete) THEN
      COMMIT;
      SELECT 'Success - Committed!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    ELSE
      ROLLBACK TO PreCleanUp;
      SELECT 'Failure - ROLLBACK!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    END IF;
  END //

DELIMITER ;

In bovenstaande voorbeeld is gebruik gemaakt van de volgende InnoDB tabellen.

[primtab]
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value1 | varchar(75)      | YES  |     | NULL    |                |
| value2 | varchar(75)      | YES  |     | NULL    |                |
| value3 | varchar(75)      | YES  |     | NULL    |                |
| datum  | datetime         | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

[sectab]
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| primtab_id | int(10)          | YES  |     | NULL    |                |
| value1     | varchar(75)      | YES  |     | NULL    |                |
| value2     | varchar(75)      | YES  |     | NULL    |                |
| value3     | varchar(75)      | YES  |     | NULL    |                |
| datum      | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+