<#
Author 	: Tushar Kanti
Project : Mission Crititcal
Purpose : Automation of Database Reporting
Version : 1.0
History : No change Yet 
#>
## First RunBook in Azure
# Load the credentials required to connect to the resource groups
$sqlcred = Get-AutomationPSCredential -Name 'SQLAutomationCred'
# Load the credentials required for sending the email
$mailcred=Get-AutomationPSCredential -Name 'emailCredential'
# selecting the default subscription (required by the cmdlets )
Add-AzureAccount -Credential $sqlcred -Tenant "You have to put your tenantid here"
# Setting Varibales for the email communication
$tomail = 'recipient'
$frommail = 'reports@automation.com'
$mailbody = 'The Total space used by the databases in SQL Azure is '
$submail = 'Azure Reporting Automation'
$smtpser =  'smtpserver'
## Main Code begins 
$db_info = New-Object System.Collections.ArrayList
[double]$TotalSizeByte = 0
[String]$TotalSizeGB = ''
$AzureDBServerNames = Get-AzureSqlDatabaseServer | Select ServerName -ExpandProperty ServerName
ForEach($AzureDBServerName in $AzureDBServerNames)
{
$AzureDatabases = Get-AzureSqlDatabase -ServerName $AzureDBServerName | Select Name -ExpandProperty Name
#echo $AzureDBServerName
#echo $AzureDatabases

ForEach($AzureDatabase in $AzureDatabases)
{
$DatabaseSizes = Get-AzureSqlDatabaseUsages -ServerName $AzureDBServerName -DatabaseName $AzureDatabase | Select ResourceName, Limit, CurrentValue
[string]$ResourceName = $DatabaseSizes.ResourceName
$CurrentLimit = ([long]$DatabaseSizes.Limit)/(1024*1024*1024.00)
$CurrentValue = ([long]$DatabaseSizes.CurrentValue)/(1024*1024*1024.00)
$Value = $ResourceName + ',' + $CurrentLimit + ',' + $CurrentValue
#$Value >> $DBSizeDetailsFile
$db_info.Add($Value)
}
}
# Here is the total information for the databases under all the resource groups in the subscription
echo $db_info

foreach ($db in $db_info)
{
## Here we are summing up the data space usage of the individual databases
 [double]$TotalSizeByte = [double]$TotalSizeByte + $db.Substring($db.LastIndexOf(",")+1)    
}

$TotalSizeGB = $TotalSizeByte|out-string
## echo the total database space used
echo $TotalSizeGB
## To be used in future 
$CustomDate = Get-Date -Format "MMM-yyyy"
echo $TotalSizeGB  
$mailbody = $mailbody + $TotalSizeGB  + 'GB'
echo $mailbody
Send-MailMessage -To $tomail -from $frommail -Subject $submail  -SmtpServer $smtpser -UseSsl -Credential $mailcred -BodyAsHtml  $mailbody