Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
demo Azure Automation runbook for creating Azure alerts using Log Analytics search queries
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
[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"
$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
$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
#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
#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
#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')
#Process result
if ($CommandType -ieq 'query')
#Return the array list
Write-Verbose -Message "Number of rows returned: $($ReturnedData.count)"
} else {
Write-Verbose -Message "Number of rows affected: $ReturnedData"
#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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.