demo Azure Automation runbook for creating Azure alerts using Log Analytics search queries

SQLMonitoringDemo.ps1

 <#
====================================================================================================
AUTHOR:  Tao Yang 
DATE:    18/03/2018
Version: 1.0
Comment: demo Azure Automation runbook for creating Azure alerts using Log Analytics search queries
====================================================================================================
#>

#region functions
Function Invoke-SQLQuery
{
  [OutputType([System.Collections.ArrayList])]
  [OutputType([int])]
  [CmdletBinding()]
  PARAM (
    [Parameter(Mandatory=$true,HelpMessage='Please enter the SQL Server name')][Alias('SQL','Server','s')][String]$SQLServer,
    [Parameter(Mandatory=$false,HelpMessage='Please enter the SQL Instance name')][Alias('Instance','i')][String]$SQLInstance,
    [Parameter(Mandatory=$false,HelpMessage='Please enter the TCP Port number for the SQL Instance')][Alias('port')][int]$SQLPort,
    [Parameter(Mandatory=$true,HelpMessage='Please enter the SQL Database name')][Alias('d')][String]$Database,
    [Parameter(Mandatory = $false)][Alias('e')][Boolean]$Encrypt = $false,
    [Parameter(Mandatory = $false)][Alias('trust')][Boolean]$TrustServerCertificate = $false,
    [Parameter(Mandatory = $true)][Alias('q')][String]$Query,
    [Parameter(Mandatory = $false)][Alias('type')][ValidateSet('Query','NonQuery')][String]$CommandType='Query',
    [Parameter(Mandatory = $true,HelpMessage='Please specify the SQL credential')][Alias('cred')][PSCredential][System.Management.Automation.Credential()]$Credential,
    [Parameter(Mandatory = $false)][Alias('timeout', 't')][int]$SQLQueryTimeout = 600
  )
  if ($PSBoundParameters.ContainsKey('SQLInstance'))
  {
    $SQLServerConn = "$SQLServer`\$SQLInstance"
  }
  else 
  {
    $SQLServerConn = $SQLServer
  }

  if ($PSBoundParameters.ContainsKey('SQLPort'))
  {
    $SQLServerConn = "$SQLServerConn`,$SQLPort"
  }
  
  $ConnectionString = "Server`=$SQLServerConn; Database=$Database;Encrypt=$Encrypt;TrustServerCertificate=$TrustServerCertificate;"
  $SQLCon = New-Object -TypeName System.Data.SqlClient.SqlConnection
  $SQLCon.ConnectionString = $ConnectionString
  $Credential.Password.MakeReadOnly()
  $SQLCred = New-Object -TypeName System.Data.SqlClient.SqlCredential -ArgumentList ($Credential.UserName, $Credential.Password)
  Write-Verbose -Message "Adding credential '$($Credential.UserName)' to the SQL connection object."
  $SQLCon.Credential = $SQLCred
  $SQLCon.Open()

  #execute SQL query
  $sqlCmd = $SQLCon.CreateCommand()
  $sqlCmd.CommandTimeout = $SQLQueryTimeout
  $sqlCmd.CommandText = $Query
    
  Switch ($CommandType)
  {

    'Query' {
      $SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $sqlCmd
      $DataSet = New-Object -TypeName System.Data.DataSet
      [void]$SqlAdapter.Fill($DataSet)
      #Process result
      $arrReturnedData = New-Object -TypeName System.Collections.ArrayList
      Foreach ($set in $DataSet.Tables[0])
      {
        $objDS = New-Object -TypeName psobject
        Foreach ($objProperty in (Get-Member -InputObject $set -MemberType Property))
        {
          $PropertyName = $objProperty.Name
          Add-Member -InputObject $objDS -MemberType NoteProperty -Name $PropertyName -Value $set.$PropertyName
        }
        [void]$arrReturnedData.Add($objDS)
      }
      #Return the array list
      $ReturnedData = $arrReturnedData
    }
    Default {
      try {
        $NumberOfRowsAffected = $sqlCmd.ExecuteNonQuery()
      } catch {
        Write-Error $_.Exception.InnerException
      }
      #return number of rows affected (numeric value)
      $ReturnedData = $NumberOfRowsAffected
    }
  }
  if ($SQLCon.State -ieq 'open')
  {
    $SQLCon.Close()
  }

  #Process result
  if ($CommandType -ieq 'query')
  {
    #Return the array list
    Write-Verbose -Message "Number of rows returned: $($ReturnedData.count)"
    ,$ReturnedData
  } else {
    Write-Verbose -Message "Number of rows affected: $ReturnedData"
    $ReturnedData
  }
}
#endregion

#region main
#SQL Queries
#SQL DB Data free space
$SQLDBFreeSpaceQuery = "SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files where type_desc = 'ROWS'"

#Get All Databases
$GetDBQuery = "SELECT name FROM master.dbo.sysdatabases"

#Database last backup
$DBLastBckpQueryTemplate = "SELECT top 1 * FROM dbo.backupset where database_name = '{0}' order by backup_start_date desc"

#GEt list of SQL Servers
$arrSQLServers = (Get-AutomationVariable SQLServers).split(';')

#Get default SQL SA credential
$SQLSACredential = Get-AutomationPSCredential SQL-SA

Foreach ($SQLServer in $arrSQLServers)
{
  #Get all databases
  $AllDBs = (Invoke-SQLQuery -SQLServer $SQLServer -Database 'master' -Credential $SQLSACredential -Query $GetDBQuery).name
  Foreach ($DB in $AllDBs)
  {
    Write-Verbose "CHecking $DB on $SQLServer"
    #Get last backup date
    $DBLastBckpQuery = [string]::Format($DBLastBckpQueryTemplate, $DB)
    $LastBackupSet = Invoke-SQLQuery -SQLServer $SQLServer -Database 'msdb' -Credential $SQLSACredential -Query $DBLastBckpQuery
    If ($LastBackupSet.count -gt 0)
    {
      $UTCOffSetMinute = $LastBackupSet[0].time_zone * 15
      $BackupFinishedDate = $LastBackupSet[0].backup_finish_date
      $LastBackupUTCTime = $BackupFinishedDate.AddMinutes($UTCOffSetMinute)
    } else {
      $LastBackupUTCTime = get-date 0
    }
    Write-Output "SQLServerName: $SQLServer, Database: $DB, Last Backup Finish Date (UTC): '$LastBackupUTCTime'"
    
    #Check DB free space
    $DBFreeSpaceQueryResult = Invoke-SQLQuery -SQLServer $SQLServer -Database $DB -Credential $SQLSACredential -Query $SQLDBFreeSpaceQuery
    $DBFreeSpacePercent = ($DBFreeSpaceQueryResult[0].FreeSpaceMB / $DBFreeSpaceQueryResult[0].CurrentSizeMB).tostring("P")
    $CurrentSizeMB = [math]::Round($DBFreeSpaceQueryResult[0].CurrentSizeMB)
    $FreeSpaceMB = [math]::Round($DBFreeSpaceQueryResult[0].FreeSpaceMB)
    Write-Output "SQLServerName: $SQLServer, Database: $DB, Current Size MB: $CurrentSizeMB, Free Space MB: $FreeSpaceMB, Free Space Percentage: $DBFreeSpacePercent"
  }
}
#endregion