PowerShell Retrieve OAUTH Access Token

PowerShell function example to retrieve an OAUTH2 access token from ADFS using form-based authentication or by using a previously retrieved OAUTH Refresh Token.

# Functions en Procedures...
function fnGetOauthXSToken()
{
  param (
    [string]$ADFSOAUTHAuthorizeUri, 
	[string]$ADFSOAUTHGetTokenUri, 
	[string]$Client_ID, 
	[string]$Resource, 
	[string]$Redirect_URI,
	[string]$RefreshToken, 
	[string]$UserName, 
	[string]$Password,
	[string]$Secret
	)

  if ( $RefreshToken -ne "" ) { 
    
    # Get OAUTH Access Token by using OAUTH refresh_token...

    ## Retrieve OAUTH Token...
    $vPostValues = "grant_type=refresh_token&client_id=" + $Client_ID + "&redirect_uri=" + $Redirect_URI + "&refresh_token=" + $RefreshToken
    $oResult0 = Invoke-RestMethod -Method Post -Uri $ADFSOAUTHGetTokenUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $vPostValues -ContentType application/x-www-form-urlencoded

  } else {
    
    # Get OAUTH Access Token by using authorization_code (username and password)... 
  
    ## Build authentication Uri and create websession...
	if ($Secret -eq "") {
	   $sUri = $ADFSOAUTHAuthorizeUri + "?response_type=code&client_id=" + $Client_ID + "&resource=" + $Resource + "&redirect_uri=" + $Redirect_URI
	} else {
	   $sUri = $ADFSOAUTHAuthorizeUri + "?response_type=code&client_id=" + $Client_ID + "&resource=" + $Resource + "&redirect_uri=" + $Redirect_URI + "&client_secret=" + $Secret
	}
    $oWebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession

    ## Authenticate by using username and password (formbased)...
    $aPostValues = @{UserName=$UserName; Password=$Password; AuthMethod='FormsAuthentication' }
    $oResult0 = Invoke-WebRequest -Method Post -Uri $sUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $aPostValues -Websession $oWebSession -MaximumRedirection 0 -ErrorAction SilentlyContinue

    ## Retrieve authorization code...
    $oResult0 = Invoke-WebRequest -Uri $sUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Websession $oWebSession -MaximumRedirection 0 -ErrorAction SilentlyContinue
    $sCode = $oResult0.Headers.Location.Substring($oResult0.Headers.Location.IndexOf("?code=") + 6, ($oResult0.Headers.Location.Length - ($oResult0.Headers.Location.IndexOf("?code=") + 6) ) )
  
    ## Cleanup websession...
    $oResult0 = $null
    $oWebSession = $null
    $aPostValues = $null
  
    ## Retrieve OAUTH Token...
    $vPostValues = "grant_type=authorization_code&client_id=" + $Client_ID + "&redirect_uri=" + $Redirect_URI + "&code=" + $sCode
    $oResult0 = Invoke-RestMethod -Method Post -Uri $ADFSOAUTHGetTokenUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $vPostValues -ContentType application/x-www-form-urlencoded
  
  }
  
  # Return Result and Cleanup...
  return $oResult0
  $vPostValues = $null
  $oResult0 = $null
}

PowerShell Remove Old Files From a Directory

Script for removal of old backup or logfiles from a specific directory. The example below removes all files from a directory that are older than 90 days. A log of all files that are removed is written to the windows eventlog.

# Global config settings...
[string]$sDirName = "D:\LogFiles"
[string]$sFileNameMask = "*.log"
[string]$sGCIMask = $sDirName + "\" + $sFileNameMask
[string]$sEventLogMessage = "### Cleanup Script Started... ###`r`n"

# Functions and Procedures...
function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Start Main script...
if (Test-Path $sDirName) {
get-childitem $sGCIMask | 
    % { 
        # Get File Last Write Time...
        [datetime]$dFileModTime = fGetFileModTime($_.fullname)
        if ($dFileModTime -lt (Get-date).AddDays(-90)) {
        remove-item $_.fullname
        $sEventLogMessage = $sEventLogMessage + "File " + $_.fullname + " (" + $dFileModTime.DateTime + ") removed...`r`n"
        }
    }
}

# Finish and Cleanup...
[string]$sEventLogMessage = $sEventLogMessage + "### Cleanup Script Finished... ###"
write-eventLog -LogName "Windows PowerShell" -Source "PowerShell" -EventID 30001 -Message $sEventLogMessage -EntryType Information

PowerShell Check File Modified Time

Script to check is a specific file exists and its modified time is less than 1 day in the past. This script uses some custum functions from a imported module.

# Import DBA modules...
Import-Module 'D:\Path\To\DefaultFunctionsModule.psm1'

# Global config settings...
[string]$sSubject1 = "SBO:CheckLogFiles Errors Found..."
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "mail@example.com"
[string]$sEventLogSource = "{eventlogsource}"

# Functions en Procedures...
function fnGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Start Main script...
if ((Test-Path $args[0]) -and ($args[1])) {
  # Request file modified time...
  [datetime]$dFileModTime = fnGetFileModTime("$($args[0])")
  # Alert if file modified time is older than 1 day...
  if ((Get-date).AddDays(-1) -gt $dFileModTime) {
  $sMessage1 = "ERROR: File modified time is older than 1 day...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
  fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  }
} else {
  if (($args[1])) {
    # Alert if file does not exist...
    $sMessage1 = "ERROR: File does not exist...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
    fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  } else {
      $sMessage1 = "ERROR: Missing argument... [CheckModTime.ps1 `"c:\path\to\file`" `"service`"]`r`n`r`nFilename: " + $args[0]  + "`r`nService: " + $args[1]    
      echo $sMessage1
  }
}

PowerShell Auto Add Database to SQL AllwaysOn Availability Group

This script adds all user databases which are not yet replicated to a specific SQL Server AlwaysOn Availability Group. After that a check procedure is started to check if replication state for all databases exists and are healthy. Alerts can be send to an e-mail address or to the Windows Eventlog (or both).

This script uses custum functions fnSendMail, fnWriteEventLog to send alert messages and fnSQLCmd for handeling SQL statements.

# Import DBA modules (functions)...
Import-Module 'AdminFunctions.psm1'

# Global config settings...
[string]$cAOGroupName = "AO Group Name (LISTENER)"
[string]$cAOListenerName = "LISTENER\INSTANCE"
[string]$cDatabaseBackupLocation = "\\servershare$\_AlwaysOnTempDir"
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "admin@example.com"

# Functions en Procedures...
function fnAddDatabase2AOGroup()
{
 param (
   [string]$sAOGroupName,
   [string]$sDatabaseName,
   [string]$sPrimaryServerName,
   [string]$sSecondaryServerName,
   [string]$sDatabaseBackupLocation
 )

 # Create a timestamp...
 [string]$sDate = Get-Date -UFormat %Y%m%d%H%M

 # Concat strings to create path...
 [string]$sDatabaseBackupFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".bak"
 [string]$sDatabaseLogFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".trn"
 [string]$sAOPrimaryPath = "SQLSERVER:\SQL\" + $sPrimaryServerName + "\AvailabilityGroups\" + $sAOGroupName
 [string]$sAOSecondaryPath = "SQLSERVER:\SQL\" + $sSecondaryServerName + "\AvailabilityGroups\" + $sAOGroupName

 # Backup Data + Log on primary...
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sPrimaryServerName)
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sPrimaryServerName) -BackupAction 'Log'

 # Restore Data + Log on secondary...
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sSecondaryServerName) -NoRecovery
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sSecondaryServerName) -RestoreAction 'Log' -NoRecovery

 # Add Database to Availability Group...
 Add-SqlAvailabilityDatabase -Path $sAOPrimaryPath -Database $($sDatabaseName)
 Add-SqlAvailabilityDatabase -Path $sAOSecondaryPath -Database $($sDatabaseName)
}

function fnSetAllDatabases2FullRecovery()
{
 param (
         [string]$sDBServerName
       )
  
  [string]$sQuery9 = "-- Declarations and temptables... `
                      DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max) `
                      CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max)) `
                      -- Insert Commands into temptable... `
                      INSERT INTO #tCommands (Command) `
                      SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' `
                      FROM [master].[sys].[databases] `
                      WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb') `
                      -- Execute commands from temptable... `
                      SELECT @iNumRecs = COUNT(*) FROM #tCommands `
                      WHILE @iRecNum <= @iNumRecs `
                      BEGIN `
                        SELECT @vCommand = Command FROM #tCommands `
                        WHERE ID = @iRecNum `
                        -- Execute command and increase record number... `
                        -- EXECUTE(@vCommand) --Alternative, Not preferred... `
                        EXECUTE sys.sp_executesql @stmt = @vCommand `
                        SET @iRecNum += 1 `
                      END `
                      -- Cleanup... ` 
                      DROP TABLE #tCommands"
  fnSQLCmd -sInstance $($sDBServerName) -sQuery $sQuery9 -sDatabase "master" 
}


### Start main script... ###

# First we set all databases to full recovery...
fnSetAllDatabases2FullRecovery -sDBServerName $($cAOListenerName)

# Retrieve AlwaysOn information from listener...
[string]$sQuery0 = "-- Retrieve AllwaysOn information and status... `
                    SELECT nim.ag_name, arcs.replica_id, arcs.replica_server_name, arcs.join_state, `
                    arcs.join_state_desc, ars.is_local, ars.role, ars.role_desc, `
                    ars.operational_state, ars.operational_state_desc, ars.recovery_health, `
                    ars.recovery_health_desc, ars.synchronization_health, ars.synchronization_health_desc `
                    FROM [master].[sys].dm_hadr_availability_replica_states ars `
                    INNER JOIN [master].[sys].dm_hadr_availability_replica_cluster_states arcs ON ars.replica_id = arcs.replica_id `
                    INNER JOIN [master].[sys].dm_hadr_name_id_map nim ON arcs.group_id = nim.ag_id"
if ($oResult0) { Remove-Variable -name oResult0 }
# Force in object so we can always count...
[Object[]]($oResult0) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery0 -sDatabase "master"

# Retrieve primary and secondary nodes for group...
foreach($rows0 in $oResult0) {
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "1")) { 
    [string]$cPrimaryServerName = $rows0["replica_server_name"] 
    [string]$cPrimaryReplicaID = $rows0["replica_id"]
  }
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "2")) { [string]$cSecondaryServerName = $rows0["replica_server_name"] }
}

# Retrieve database names...
[string]$sQuery1 = "-- Return all unreplicated databases without `
                    SELECT name FROM [master].[sys].[databases] `
                    WHERE name NOT IN ('master','tempdb','model','msdb','SSISDB') `
                    AND replica_id IS NULL"
if ($oResult1) { Remove-Variable -name oResult1 }
[Object[]]($oResult1) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery1 -sDatabase "master" 

# Process the rows returned...
foreach($rows1 in $oResult1) {
  if (($cPrimaryServerName) -and ($cPrimaryServerName)) {
  fnAddDatabase2AOGroup -sAOGroupName $cAOGroupName -sDatabaseName ($rows1["name"]) -sPrimaryServerName $cPrimaryServerName -sSecondaryServerName $cSecondaryServerName -sDatabaseBackupLocation $cDatabaseBackupLocation
  } 
}

# Check database replication state...
Start-Sleep -m 10000
[string]$sQuery2 = "-- Check database replication state... `
                    SELECT db.name, db.replica_id, synchronization_state, synchronization_health_desc `
                    FROM [master].[sys].[databases] db `
                    LEFT OUTER JOIN [master].[sys].[dm_hadr_database_replica_states] dbrs ON db.database_id = dbrs.database_id `
                    WHERE name NOT IN ('master','tempdb','model','msdb', 'SSISDB') `
                    AND (db.replica_id IS NULL OR synchronization_state <> 2)"
# Force in object so we can always count...
if ($oResult2) { Remove-Variable -name oResult2 }
[Object[]]($oResult2) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery2 -sDatabase "master"

# Send report if sync state <> 2 or database not replicated... 
if ($oResult2.Count -gt 0) {
  # Build HTML body...
  [string]$sHTML_Body1 = ""
  [string]$sHTML_Body1 += $oResult2 | ConvertTo-Html name, replica_id, synchronization_health_desc -Fragment
  [string]$sHTML_Body1 += ""
 
  # Build TEXT body...
  [string]$sResult2 = $oResult2 | Out-String
  [string]$sTEXT_Body1 = $sResult2

  # Send messages...
  [string]$sSubject1 = ($cAOListenerName.Replace("\", "_")) + ":Database(s) not replicated..."
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sHTML_Body1 -swIsBodyHTML

  # Write to Eventlog...
  $sTEXT_Body1 = $sSubject1 + $sTEXT_Body1
  fnWriteEventLog -sNode ($cAOListenerName.Replace("\", "_")) -sMessage $sTEXT_Body1
}