ScheduledTaskEmailReporting.ps1

$credential = Get-AutomationPSCredential -Name 'Exchange'

$session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri 'https://outlook.office365.com/powershell-liveid/' -Credential $credential -Authentication Basic -AllowRedirection

Import-Module (Import-PSSession -Session $session -DisableNameChecking -AllowClobber ) -Global

$TodaysDate = Get-Date (Get-Date).AddDays(-1) -Format 'MM/dd/yyyy'

$NumberOfInternalEmails = (Get-MessageTrace -StartDate $TodaysDate -EndDate $TodaysDate | Where-Object{$_.'SenderAddress' -like '*alexholmeset.onmicrosoft.com'} | Where-Object{$_.'RecipientAddress' -like '*alexholmeset.onmicrosoft.com'}).count




#Set Defaults (Optional) which allows you to skip defining instance, user, and password each time
$AzureDefaultInstanceName = "ServerName"
$AzureDefaultDatabaseName = "DatabaseName"
$AzureDefaultUserID = Get-AutomationVariable -Name 'Administrator'
$AzureDefaultPassword = Get-AutomationVariable -Name 'Password'
#The actual function
Function get-azureSQL (
[string]$DatabaseName = $AzureDefaultDatabaseName,   
[string]$InstanceName = $AzureDefaultInstanceName
,[string]$UserID = $AzureDefaultUserID
,[string]$Password = $AzureDefaultPassword
,[string]$Query){
$connectionString = "Server=tcp:$($InstanceName).database.windows.net,1433;"
$connectionString = $connectionString + "Database=$($DatabaseName);"
$connectionString = $connectionString + "User ID=$($UserID)@$($InstanceName);"
$connectionString = $connectionString + "Password=$($Password);"
$connectionString = $connectionString + "Encrypt=True;"
$connectionString = $connectionString + "TrustServerCertificate=False;"
$connectionString = $connectionString + "Connection Timeout=30;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()
return $DataSet.Tables[0]
}








$sqlquery = @"
INSERT INTO InternalEmailActivity (Date, NumberOfInternalEmails)
VALUES ('$((get-date (get-date).AddDays(-1) -Format 'MM/dd/yyyy'))', '$NumberOfInternalEmails');
"@

#Querying Azure SQL using Defaults defined above
get-azureSQL -Query $sqlquery