Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
$credential = Get-AutomationPSCredential -Name 'Exchange'
$session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri '' -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 '*'} | Where-Object{$_.'RecipientAddress' -like '*'}).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
$connectionString = "Server=tcp:$($InstanceName),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
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
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.