Categories
PowerShell Scripting

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

Categories
PowerShell Scripting

PowerShell Check File Modified Time

Script to check is a specific file exists and its modified time is less than 1 day in the past. This script uses some custum functions from a imported module.

# Import DBA modules...
Import-Module 'D:\Path\To\DefaultFunctionsModule.psm1'

# Global config settings...
[string]$sSubject1 = "SBO:CheckLogFiles Errors Found..."
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "mail@example.com"
[string]$sEventLogSource = "{eventlogsource}"

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

# Start Main script...
if ((Test-Path $args[0]) -and ($args[1])) {
  # Request file modified time...
  [datetime]$dFileModTime = fnGetFileModTime("$($args[0])")
  # Alert if file modified time is older than 1 day...
  if ((Get-date).AddDays(-1) -gt $dFileModTime) {
  $sMessage1 = "ERROR: File modified time is older than 1 day...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
  fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  }
} else {
  if (($args[1])) {
    # Alert if file does not exist...
    $sMessage1 = "ERROR: File does not exist...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
    fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  } else {
      $sMessage1 = "ERROR: Missing argument... [CheckModTime.ps1 `"c:\path\to\file`" `"service`"]`r`n`r`nFilename: " + $args[0]  + "`r`nService: " + $args[1]    
      echo $sMessage1
  }
}
Categories
PowerShell Scripting

PowerShell Auto Add Database to SQL AllwaysOn Availability Group

This script adds all user databases which are not yet replicated to a specific SQL Server AlwaysOn Availability Group. After that a check procedure is started to check if replication state for all databases exists and are healthy. Alerts can be send to an e-mail address or to the Windows Eventlog (or both).

This script uses custum functions fnSendMailfnWriteEventLog to send alert messages and fnSQLCmd for handeling SQL statements.

# Import DBA modules (functions)...
Import-Module 'AdminFunctions.psm1'

# Global config settings...
[string]$cAOGroupName = "AO Group Name (LISTENER)"
[string]$cAOListenerName = "LISTENER\INSTANCE"
[string]$cDatabaseBackupLocation = "\\servershare$\_AlwaysOnTempDir"
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "admin@example.com"

# Functions en Procedures...
function fnAddDatabase2AOGroup()
{
 param (
   [string]$sAOGroupName,
   [string]$sDatabaseName,
   [string]$sPrimaryServerName,
   [string]$sSecondaryServerName,
   [string]$sDatabaseBackupLocation
 )

 # Create a timestamp...
 [string]$sDate = Get-Date -UFormat %Y%m%d%H%M

 # Concat strings to create path...
 [string]$sDatabaseBackupFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".bak"
 [string]$sDatabaseLogFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".trn"
 [string]$sAOPrimaryPath = "SQLSERVER:\SQL\" + $sPrimaryServerName + "\AvailabilityGroups\" + $sAOGroupName
 [string]$sAOSecondaryPath = "SQLSERVER:\SQL\" + $sSecondaryServerName + "\AvailabilityGroups\" + $sAOGroupName

 # Backup Data + Log on primary...
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sPrimaryServerName)
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sPrimaryServerName) -BackupAction 'Log'

 # Restore Data + Log on secondary...
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sSecondaryServerName) -NoRecovery
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sSecondaryServerName) -RestoreAction 'Log' -NoRecovery

 # Add Database to Availability Group...
 Add-SqlAvailabilityDatabase -Path $sAOPrimaryPath -Database $($sDatabaseName)
 Add-SqlAvailabilityDatabase -Path $sAOSecondaryPath -Database $($sDatabaseName)
}

function fnSetAllDatabases2FullRecovery()
{
 param (
         [string]$sDBServerName
       )
  
  [string]$sQuery9 = "-- Declarations and temptables... `
                      DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max) `
                      CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max)) `
                      -- Insert Commands into temptable... `
                      INSERT INTO #tCommands (Command) `
                      SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' `
                      FROM [master].[sys].[databases] `
                      WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb') `
                      -- Execute commands from temptable... `
                      SELECT @iNumRecs = COUNT(*) FROM #tCommands `
                      WHILE @iRecNum <= @iNumRecs `
                      BEGIN `
                        SELECT @vCommand = Command FROM #tCommands `
                        WHERE ID = @iRecNum `
                        -- Execute command and increase record number... `
                        -- EXECUTE(@vCommand) --Alternative, Not preferred... `
                        EXECUTE sys.sp_executesql @stmt = @vCommand `
                        SET @iRecNum += 1 `
                      END `
                      -- Cleanup... ` 
                      DROP TABLE #tCommands"
  fnSQLCmd -sInstance $($sDBServerName) -sQuery $sQuery9 -sDatabase "master" 
}


### Start main script... ###

# First we set all databases to full recovery...
fnSetAllDatabases2FullRecovery -sDBServerName $($cAOListenerName)

# Retrieve AlwaysOn information from listener...
[string]$sQuery0 = "-- Retrieve AllwaysOn information and status... `
                    SELECT nim.ag_name, arcs.replica_id, arcs.replica_server_name, arcs.join_state, `
                    arcs.join_state_desc, ars.is_local, ars.role, ars.role_desc, `
                    ars.operational_state, ars.operational_state_desc, ars.recovery_health, `
                    ars.recovery_health_desc, ars.synchronization_health, ars.synchronization_health_desc `
                    FROM [master].[sys].dm_hadr_availability_replica_states ars `
                    INNER JOIN [master].[sys].dm_hadr_availability_replica_cluster_states arcs ON ars.replica_id = arcs.replica_id `
                    INNER JOIN [master].[sys].dm_hadr_name_id_map nim ON arcs.group_id = nim.ag_id"
if ($oResult0) { Remove-Variable -name oResult0 }
# Force in object so we can always count...
[Object[]]($oResult0) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery0 -sDatabase "master"

# Retrieve primary and secondary nodes for group...
foreach($rows0 in $oResult0) {
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "1")) { 
    [string]$cPrimaryServerName = $rows0["replica_server_name"] 
    [string]$cPrimaryReplicaID = $rows0["replica_id"]
  }
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "2")) { [string]$cSecondaryServerName = $rows0["replica_server_name"] }
}

# Retrieve database names...
[string]$sQuery1 = "-- Return all unreplicated databases without `
                    SELECT name FROM [master].[sys].[databases] `
                    WHERE name NOT IN ('master','tempdb','model','msdb','SSISDB') `
                    AND replica_id IS NULL"
if ($oResult1) { Remove-Variable -name oResult1 }
[Object[]]($oResult1) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery1 -sDatabase "master" 

# Process the rows returned...
foreach($rows1 in $oResult1) {
  if (($cPrimaryServerName) -and ($cPrimaryServerName)) {
  fnAddDatabase2AOGroup -sAOGroupName $cAOGroupName -sDatabaseName ($rows1["name"]) -sPrimaryServerName $cPrimaryServerName -sSecondaryServerName $cSecondaryServerName -sDatabaseBackupLocation $cDatabaseBackupLocation
  } 
}

# Check database replication state...
Start-Sleep -m 10000
[string]$sQuery2 = "-- Check database replication state... `
                    SELECT db.name, db.replica_id, synchronization_state, synchronization_health_desc `
                    FROM [master].[sys].[databases] db `
                    LEFT OUTER JOIN [master].[sys].[dm_hadr_database_replica_states] dbrs ON db.database_id = dbrs.database_id `
                    WHERE name NOT IN ('master','tempdb','model','msdb', 'SSISDB') `
                    AND (db.replica_id IS NULL OR synchronization_state <> 2)"
# Force in object so we can always count...
if ($oResult2) { Remove-Variable -name oResult2 }
[Object[]]($oResult2) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery2 -sDatabase "master"

# Send report if sync state <> 2 or database not replicated... 
if ($oResult2.Count -gt 0) {
  # Build HTML body...
  [string]$sHTML_Body1 = ""
  [string]$sHTML_Body1 += $oResult2 | ConvertTo-Html name, replica_id, synchronization_health_desc -Fragment
  [string]$sHTML_Body1 += ""
 
  # Build TEXT body...
  [string]$sResult2 = $oResult2 | Out-String
  [string]$sTEXT_Body1 = $sResult2

  # Send messages...
  [string]$sSubject1 = ($cAOListenerName.Replace("\", "_")) + ":Database(s) not replicated..."
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sHTML_Body1 -swIsBodyHTML

  # Write to Eventlog...
  $sTEXT_Body1 = $sSubject1 + $sTEXT_Body1
  fnWriteEventLog -sNode ($cAOListenerName.Replace("\", "_")) -sMessage $sTEXT_Body1
}

Categories
PowerShell Scripting

PowerShell Webservices Probe

The following script reads URL’s from an array to check if specific web services are online. When the script finds that one or more services are offline it sends an e-mail message (HTML). A TEXT version of the message is created in the Windows EventLog.

Het script uses custum functions: fnSendMail en fnWriteEventLog om berichten af te handelen.

# Global config settings...
[string]$sSMTPServer1 = "smtp.example.org"
[string]$sSubject1 = "EXAMPLE:WebService probes..."
[string]$sToAddress1 = "test@example.org"
[string]$sEventLogSource = "EVENTLOGSOURCE"
[switch]$swAlarm = $false
[array]$aWebServices1 = @(
   "https://example.org/webService?WSDL",
   "https://example.org/webService?WSDL",
)

# Functions and procedures...
function fnProbeWebSvc() {
  param (
    [string]$sWebSvcWSDL
  )
  $oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL
  if ($oWebSvc1) {
    [switch]$swWebSvcIsAlive = $true
  } else {
    [switch]$swWebSvcIsAlive = $false
  }  
  return $swWebSvcIsAlive
}

# Start Main script...
# Create array for results...
[array]$aWebServiceState = @()

# Fill table from array...
foreach ($row1_aWebServices1 in $aWebServices1) {
  $oWebServiceState = New-Object PSObject -Property @{
    'WebService' = $row1_aWebServices1
    'IsAlive' = (fnProbeWebSvc -sWebSvcWSDL $row1_aWebServices1) }
  # FLip Alarm status if fnProbeWebSvc return value is false...  
  if ($oWebServiceState.IsAlive -eq $false) {[switch]$swAlarm = $true}
  $aWebServiceState += $oWebServiceState
}

# Build HTML and TEXT body w. array data if alarm status is set to true...
if ($swAlarm -eq $true) {
  
  # Body header HTML...
  $sbBody1_HTML = New-Object System.Text.StringBuilder
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")
  [void]$sbBody1_HTML.Append("<table><tbody><tr bgcolor='grey'><th>WebService</th><th>IsAlive</th></tr>`r`n")

  # Body header TEXT...
  $sbBody1_TEXT = New-Object System.Text.StringBuilder
  [void]$sbBody1_TEXT.Append("Webservice(s) offline. Contact your administrators.`r`n`r`n")

  # Build table rows from array...
  foreach ($row1_aWebServiceState in $aWebServiceState) {
    # Build table rows HTML...
    [void]$sbBody1_HTML.Append("<tr bgcolor='lightblue'><td>")
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.WebService)
    # Row Coloring...
    if ($row1_aWebServiceState.IsAlive -eq $true) {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightgreen'>")
    } else {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightpink'>")
    }
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.IsAlive)
    [void]$sbBody1_HTML.Append("</td></tr>")

    # Build table rows TEXT (only show failed services...
      if ($row1_aWebServiceState.IsAlive -eq $false) {
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.WebService)
        [void]$sbBody1_TEXT.Append(": ")
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.IsAlive)
        [void]$sbBody1_TEXT.Append("`r`n")
      }
  }

  # Mail body footer...
  [void]$sbBody1_HTML.Append("</tbody></table>`r`n")
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")

  # Send HTML body in mail to admins...
  [string]$sBody1 = $sbBody1_HTML.ToString()
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sBody1 -swIsBodyHTML

  # Send text body to eventlog...
  fnWriteEventLog -sNode "NODENAME" -sMessage $sbBody1_TEXT.ToString() 
}
Categories
PowerShell Scripting

PowerShell Get Local Machine FQDN Function

The example function below retrieves the FQDN of the machine on which the script is running. The switch option swIsEmail can be activated to construct a noreply email address.

function fnGetFQDN() {
  param (
    [switch]$swIsEmail = $false
  )
  $oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()
  if ($swIsEmail -eq $true) {
    [string]$Return1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
  } else {
    [string]$Return1 = $oNETIP.HostName + "." + $oNETIP.DomainName
  }  
  return $Return1
}