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() 
}

PowerShell Check Database-backup

This next script can be used to check if Full and Transaction Log backups are made within the last two days. Databases are read from a CSV file. I use a slightly modified version which reads databases from a registration database.

# ********************************************************************
# Scriptnaam: CHECKBACKUP.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20110811A
# Info: Check if backup is made within last two days...
# ********************************************************************

# Global config settings...
[string]$sServerInstanceFile = ".ServerInstance.csv"
[string]$sSMTPServer1 = "smtp-server-name"
[string]$sToAddress1 = "email-adsress"

# Global objects...
$oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

# Register Snapins...
$oPSSnapinRegResult = PSSnapin SqlServerCmdletSnapin* -registered
if ($oPSSnapinRegResult) {
  $oPSSnapinResult = PSSnapin $oPSSnapinRegResult.Name
  if (-not $oPSSnapinResult) { 
    Add-PSSnapin $oPSSnapinRegResult.Name
  }
} else {
  break
}
  
# Functions and Procedures...
function fSendMail([string]$sSMTPSrvrName, [string]$sFrom, [string]$sTo, [string]$sSubject, [string]$sBody, [string]$sAttFileName) {
  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      return "INFO: Email has been sent."
    } else {
      return "ERROR: Error sending e-mail."
    }

  } else {
    return "ERROR: Argument(s) missing. [fSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" (`"Body`") (`"AttachFileName`")]"
  }
}

function fSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" `
                               + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; `
                               if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

# Start Main script...
[int]$iCounter = 0
Import-Csv $($sServerInstanceFile) -header ("instance", "checkfull", "checktl") | foreach {
  # Skip header...
  if (($iCounter -ne 0) -and ($_.instance)) {
    $oResult1 = $null
    $oResult2 = $null
    # Run Queries on servers...
    if ($_.checkfull -eq 1) {
      [string]$sQuery1 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentFullBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE ((msdb..backupset.type = 'D' OR msdb..backupset.type = 'I') `
      AND msdb..backupset.is_copy_only = 0 `
      AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.state = 0 `
      AND sys.databases.name  'tempdb' `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult1) = fSQLCmd $($_.instance) $sQuery1 "master"
    }


    if ($_.checktl -eq 1) {
      [string]$sQuery2 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentTLBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.recovery_model != 3 and sys.databases.state = 0 `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult2) = fSQLCmd $($_.instance) $sQuery2 "master"
    }

    # Zodra er rows worden geteld een e-mail samenstellen...
    if (($oResult1.Count -gt 0) -or ($oResult2.Count -gt 0)) {
      $oResult1 = $oResult1 | Out-String
      $oResult2 = $oResult2 | Out-String
      [string]$sFromAddress1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
      [string]$sBody1 = $oResult1 + $oResult2
      [string]$sSubject1 = $($_.instance.Replace("", "_")) + ":Database backup older than 2 days..."
      fSendMail $sSMTPServer1 $sFromAddress1 $sToAddress1 $sSubject1 $sBody1
    }
  }
  $iCounter++
}
# ********************************************************************

CSV File format:

instance,checkfull,checktl
SERVERA,1,1
SERVERBINS1,1,1
SERVERC,1,1

PowerShell E-mail Function

Met behulp van onderstaande functie kun je vanuit een script een e-mail verzenden. De procedure is in een functie gegoten waardoor deze gemakkelijk kan worden hergebruikt. Deze functie werkt zowel in PowerShell V1 als PowerShell V2. Deze functie maakt gebruik van een andere functie fnWriteEventLog t.b.v. schrijfacties naar het Windows EventLog.

function fnSendMail()
{
  param (
    [string]$sSMTPSrvrName, 
    [string]$sFrom, 
    [string]$sTo, 
    [string]$sSubject, 
    [string]$sBody, 
    [string]$sAttFileName, 
    [switch]$swIsBodyHTML = $false
    )

  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    $oMessage.IsBodyHtml = $swIsBodyHTML
    $oMessage.BodyEncoding = ([System.Text.Encoding]::UTF8)
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      #fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Email has been sent..." -iEventID 1 -sType "Information"
      return "INFO: Email has been sent..." 
    } else {
      fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Error sending mail..." -iEventID 501
      return "ERROR: Error sending e-mail..."
    }

  } else {
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage "fSendMail argument missing..." -iEventID 501
    return "ERROR: Argument(s) missing. [fnSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" `"Body`" (`"AttachFileName`" `"isBodyHTML`")]"
  }
}