Derdack

Targeted Alert Notifications – Anywhere Actions

Derdack
  • Use Cases
    • Overview
    • Enterprise IT Alerting
    • IT Managed Services
    • Mobile Alerting in Manufacuring
    • Critical Operations Alerting in Banking & Financial
    • Field Service Dispatching in Energy & Utilities
    • Use Cases in other Industries
  • Products
    • Overview
    • Enterprise Alert®
      • Overview
      • Alert Notifications
      • On-call Duty Scheduling
      • Collaboration
      • Anywhere Remediation
      • Incident Mgmt. App
      • Integrations
      • Technical Support
      • Online Knowledge Base
      • Derdack FAQ
    • SIGNL4® Cloud
    • References & More
  • How to Buy
    • Overview
    • Pricing and Quotes
    • Find a local Partner
    • Azure Marketplace
  • About Derdack
    • About
    • Careers
    • Partner Program
    • Strategic Partners
    • Derdack Podcast
    • Contact Derdack
  • News & Blog
  • Request Demo
    • de de
  • News & Blog

    • Home
    • News & Blog
    • Technical
    • Automate Maintenance of Enterprise Alert for Database and Log Files

    Automate Maintenance of Enterprise Alert for Database and Log Files

    • June 23, 2016
    • Technical
    Automate Maintenance of Enterprise Alert for Database and Log Files

    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

    Tagged

    Databaselog filesMaintenancePowerShellpurge

    Share

    Related Posts

    Enterprise Alert 9.4.1 comes with fixes and the revised version of the sentinel connector app

    February 1, 2023

    Critical System Alerts via SIGNL4

    December 29, 2022

    Enterprise Alert 9.4 Update introduces Remote Actions for hybrid scenarios and proxy support for MS Teams

    October 25, 2022

    Upgrade your shopfloor alerting with Derdack

    September 8, 2022

    About

    DERDACK products combine automated alert notification workflows, 24/7 duty scheduling, ad-hoc collaboration and anywhere IT troubleshooting – reducing unexpected IT downtimes at large enterprises and organizations by 60%.

    Most popular

    • Derdack Company Take your ITIL incident management to the next level with Enterprise Alert
    • Mobile alert notifications for HP Service Manager (HPSM)
    • How to forward alerts to Microsoft Teams
    • Oncall Scheduling On-Call Schedule Management with Auto-Rotation
    • checking-mobile Enhancing SCOM alert notifications
    • Announcing Enterprise Alert 2019
    • Even, Alert, Incident, Notification Definition of Event, Alert, Incident and Notification
    • who-is-on-call-sharepoint Add a live “Who is On-Call” Dashboard into Sharepoint and other Tools

    Categories

    • Business (37)
    • Cloud Services (5)
    • Consultancy (1)
    • Customers (18)
    • Energy & Utilities (7)
    • Events (23)
    • Financial & Banking (4)
    • IT Ops (19)
    • Manufacturing (8)
    • News (48)
    • Schools (1)
    • Software (9)
    • Staffing (1)
    • Support (4)
    • Technical (141)
    • Transport & Logistics (5)

    Tags

    alert alert notifications Anywhere Resolution Anywhere Response Azure azure BMC customer reference Database derdack enterprise alert Enterprise Alert Enterprise Alert 2016 Enterprise Alert 2019 Gartner HPE HPE ITSM incident Incident Management Incident resolution incident response Industrie 4.0 Integration IT Alerting IT Operations Maintenance microsoft mobile Mobile App monitoring OMS on-call on-call schedule Operational Alerting rapid response release Remote Action REST API SCOM security SolarWinds NPM System Center update User Group voice

    Follow us

    • Twitter
    • Facebook
    • LinkedIn
    • XING
    • YouTube
    • Vimeo
    • Home
    • News & Blog
    • Technical
    • Automate Maintenance of Enterprise Alert for Database and Log Files

    CONTACT US:
    Intl: +49 331 29878-0

    US: +1 (202) 470-0885
    UK: +44 (20) 88167095
    CH: +41 (31) 5391990

    CONTACT VIA EMAIL:
    info@derdack.com

    OFFICES:
    US & Europe

    NEWSLETTER:
    Sign up here

    CAREER:
    Latest job offers

    EVENTS

    • No Upcoming Events
    • Who we help
    • Products
    • How to Buy
    • About Derdack
    • News & Blog
    • Free Trial
    • Twitter
    • LinkedIn
    • XING
    • YouTube
    • Vimeo

     © 2022 Derdack – Imprint, Privacy policy

    • Use Cases
      • Overview
      • Enterprise IT Alerting
      • IT Managed Services
      • Mobile Alerting in Manufacuring
      • Critical Operations Alerting in Banking & Financial
      • Field Service Dispatching in Energy & Utilities
      • Use Cases in other Industries
    • Products
      • Overview
      • Enterprise Alert®
        • Overview
        • Alert Notifications
        • On-call Duty Scheduling
        • Collaboration
        • Anywhere Remediation
        • Incident Mgmt. App
        • Integrations
        • Technical Support
        • Online Knowledge Base
        • Derdack FAQ
      • SIGNL4® Cloud
      • References & More
    • How to Buy
      • Overview
      • Pricing and Quotes
      • Find a local Partner
      • Azure Marketplace
    • About Derdack
      • About
      • Careers
      • Partner Program
      • Strategic Partners
      • Derdack Podcast
      • Contact Derdack
    • News & Blog
    • Request Demo
    Manage Cookie Consent
    We use cookies to optimize our website and our service.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}