$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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment