Over the time Enterprise Alert generates quite some database records and log files. Some of them are very useful for reporting purposes but from some time on historic entries are not needed anymore and just consume extra space and slow down the system.
It is recommended to purge historic database entries and to delete old log files from time to time. In order to automate this process we provide a simple maintenance script in PowerShell. You can execute the script manually or you can schedule it to run in regular intervals (e.g. weekly or monthly) in the Windows Task Scheduler. We recommend to execute the script during times of low traffic because it is putting some extra load on the database while running.
At the beginning of the script there is a configuration section that needs to be adapted to match your scenario.
The Power Shell code looks link follows:
<#
This script deletes Log messages from Enterprise Alert
from Folders and Tables older than configured in $keeplogdays.
To set up this script, please configure settings below in the CONFIGURATION section.
Works with Enterprise Alert 2012, 2015, 2016
© 2008-2016 Derdack GmbH, www.derdack.com, Enterprise Alert is a registered trademark of Derdack GmbH
#>
# History:
# 22.06.2015 HF – Delete Version and Logging revised for EA 2015
# 14.04.2015 HF – Tables added [EmergencyCalloutAlerts], [RemoteActionsHistory], [RemoteJobsHistory]
# 17.04.2014 HF – Connection String
# 16.04.2014 HF – add writes Log File
# 07.04.2014 HF – First Version
#requires -version 3
#endregion – Header
#region – Configuration
#=============================================================================
#CONFIGURATION
#=============================================================================
# Database connection String
$dbConnectionString = “Server=sqlserver;Trusted_Connection=No;UID=SA;PWD=Derdack!;Database=EnterpriseAlert;”
# “Server=localhost\SQLEXPRESS;Database=MMEA;Trusted_Connection=True;”
#keep all logs from the last xx days
$keeplogdays = “60”
#Main log Folder
$logFolders = @(“C:\Program Files (x86)\Enterprise Alert\Logs”,”C:\Program Files (x86)\Enterprise Alert\ScriptingHost\Logs”)
#Tables to truncate Log Tables (tablename,columnname of time to compare)
$DBlogTables = @(@(“MMEVENTS”,”MSGTIMESTAMP”) ,@(“EAEvents”,”Timestamp”))
#Tables to truncate Message and Tickt Tables
$DBMsgTables = @(@(“MMINBOUND”,”MSGTIMESTAMP”),@(“MMOUTBOUND”,”MSGTIMESTAMP”),@(“MMTICKETS”,”CREATIONTIME”),@(“MMTICKETMESSAGES”,”SENDINGTIME”),@(“PolicyExecutionJournal”,”Timestamp”), @(“EmergencyCalloutAlerts”,”CreationTime”),@(“EmergencyCalloutInstances”,”LastTimestamp”), @(“RemoteActionsHistory”,”Created”), @(“RemoteJobsHistory”,”CreationTime”))
#logMessage for E-mail
$emailMessage = “Run Log Message”
#Name of log File for this Script
$LogfileNameFQDN = $logFolders[0] + “\LOG_DELETE\{0:yyyy-MM-dd}_{1}_{2}.log” -f (Get-Date),$env:COMPUTERNAME,””
#static variables
$crlf = “`r`n”
#endregion – Configuration
#region – Function
#truncates Tables from MMEA Database
function .runSQL
{
param( [String]$dbConnectionString,
[String]$SQLStatement
)
try {
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $dbConnectionString
$conn.Open()
$emailMessage = $emailMessage + $crlf + “Run SQL ” + $SQLStatement
$cmd = New-Object System.Data.SqlClient.SqlCommand($SQLStatement,$conn)
$rdr = $cmd.ExecuteNonQuery()
$emailMessage = $emailMessage + $crlf + “Run SQL ” + $rdr
}
catch{
return “Error running: ” + $SQLStatement
}
return [string]$rdr
$conn.close()
}
function .checkTableCount
{
param( [String]$dbConnectionString,
[String]$SQLStatement
)
#Write-output “IDcounter is: ” $SQLStatement
$connectionString = $dbConnectionString
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $SQLStatement
$ID = $command.ExecuteScalar()
$connection.Close()
return $ID
}
# Thank you goes to: http://blog.danskingdom.com/powershell-functions-to-delete-old-files-and-empty-directories/
# Function to remove all empty directories under the given path.
# If -DeletePathIfEmpty is provided the given Path directory will also be deleted if it is empty.
# If -OnlyDeleteDirectoriesCreatedBeforeDate is provided, empty folders will only be deleted if they were created before the given date.
# If -OnlyDeleteDirectoriesNotModifiedAfterDate is provided, empty folders will only be deleted if they have not been written to after the given date.
function Remove-EmptyDirectories([parameter(Mandatory)][ValidateScript({Test-Path $_})][string] $Path, [switch] $DeletePathIfEmpty, [DateTime] $OnlyDeleteDirectoriesCreatedBeforeDate = [DateTime]::MaxValue, [DateTime] $OnlyDeleteDirectoriesNotModifiedAfterDate = [DateTime]::MaxValue)
{
Get-ChildItem -Path $Path -Recurse -Force -Directory | Where-Object { (Get-ChildItem -Path $_.FullName -Recurse -Force -File) -eq $null } |
Where-Object { $_.CreationTime -lt $OnlyDeleteDirectoriesCreatedBeforeDate -and $_.LastWriteTime -lt $OnlyDeleteDirectoriesNotModifiedAfterDate } |
Remove-Item -Force -Recurse
# If we should delete the given path when it is empty, and it is a directory, and it is empty, and it meets the date requirements, then delete it.
if ($DeletePathIfEmpty -and (Test-Path -Path $Path -PathType Container) -and (Get-ChildItem -Path $Path -Force) -eq $null -and
((Get-Item $Path).CreationTime -lt $OnlyDeleteDirectoriesCreatedBeforeDate) -and ((Get-Item $Path).LastWriteTime -lt $OnlyDeleteDirectoriesNotModifiedAfterDate))
{ Remove-Item -Path $Path -Force }
}
# Thank you goes to: http://blog.danskingdom.com/powershell-functions-to-delete-old-files-and-empty-directories/
# Function to remove all files in the given Path that were created before the given date, as well as any empty directories that may be left behind.
function Remove-FilesCreatedBeforeDate([parameter(Mandatory)][ValidateScript({Test-Path $_})][string] $Path, [parameter(Mandatory)][DateTime] $DateTime, [switch] $DeletePathIfEmpty)
{
Get-ChildItem -Path $Path -Recurse -Force -File | Where-Object { $_.CreationTime -lt $DateTime } | Remove-Item -Force
Remove-EmptyDirectories -Path $Path -DeletePathIfEmpty:$DeletePathIfEmpty -OnlyDeleteDirectoriesCreatedBeforeDate $DateTime
}
# Funktion send a E-mail to the SMTP Server
function sendSmtpMail
{
param( [String]$Smtpserver,
[String]$MessageBody
)
$from = “mmea-check-{0}@XXX.net” -f $env:COMPUTERNAME
$to = “mmea-smtp-check@XXX.net”
$Subject = “MMEA-SMTPCHECK-{0}” -f $env:COMPUTERNAME
$Body = $MessageBody
Send-MailMessage -From $from -To $to -SmtpServer $Smtpserver -Body $Body -Subject $Subject -ErrorAction SilentlyContinue
#return $?,$null
}
# Function writes a Log File
function writeLogFile
{
param(
[String]$Message,
[String]$LogfileName
)
if ((Test-Path $LogfileName) -eq $false)
{
New-Item $LogfileName -ItemType file -Force | Out-Null
}
$Message | Out-File $LogfileName -Append
}
function .deleteDBLogEntries{
param(
$DBTables
)
# Delete all DB Logs
foreach ($Tables in $DBTables) {
#getlast ID
$IDcounter = 100
$Iterations = 0
#Write-output “IDcounter Start is: ” $IDcounter
while ($IDcounter -ne 0) {
$sqlstaementCounter = “Select Top(1000) CAST(id as int) as lastID FROM ” + $Tables[0] +” WHERE DATEDIFF(dd,” + $Tables[1] + ” , GETDATE()) >= “+ $keeplogdays + ” order by ID desc”
$LastID = .checkTableCount -dbConnectionString $dbConnectionString -SQLStatement $sqlstaementCounter
if ($LastID -le 0 ){
$IDcounter = 0
break
}
#now delete first part
$sqlstaement = “DELETE top(1000) FROM ” + $Tables[0] + ” WHERE ID < ” + $LastID
$Iterations = $Iterations + 1
$Success = (.runSQL -dbConnectionString $dbConnectionString -SQLStatement $sqlstaement)
$RestNumbersStaetment = “Select CAST(count(id) as int) as Maxnumbers FROM ” + $Tables[0] + ” WHERE ID < ” + $LastID
$IDcounter = .checkTableCount -dbConnectionString $dbConnectionString -SQLStatement $RestNumbersStaetment
}
$Message = $Message + $crlf + “Cleanup for: ” + $Tables[0] + ” Iterations: ” + $Iterations
}
return $Message
}
#endregion – Function
#region – Main
$emailMessage = “Start ” + (Get-Date)
$emailMessage = $emailMessage + $crlf + “Cleanup Days from ” + ((Get-Date).AddDays(-$keeplogdays))
# Delete all files
foreach ($logFolder in $logFolders) {
$emailMessage = $emailMessage + $crlf + “Cleanup ” + $logFolder
Remove-FilesCreatedBeforeDate -Path $logFolder -DateTime ((Get-Date).AddDays(-$keeplogdays)) -DeletePathIfEmpty
}
# Delete all DB Logs
$emailMessage = $emailMessage + $crlf + [string](.deleteDBLogEntries -DBTables $DBlogTables)
$emailMessage = $emailMessage + $crlf + [string](.deleteDBLogEntries -DBTables $DBMsgTables)
$emailMessage = $emailMessage + $crlf + “Log End ” + (Get-Date)
#sendSmtpMail -Smtpserver $env:COMPUTERNAME + -MessageBody $emailMessage
writeLogFile -Message $emailMessage -LogfileName $LogfileNameFQDN
#endregion – Main