Using Powershell to create a nightly SQL health check

I had a great opportunity through work to go on a hunting weekend last week.  I usually prefer to fish, especially at this time of year, but I found the hunting to be a lot of fun.  I saw a total of 16 deer, 3 of them being bucks.  The only deer in shooting range were two small doe, and I let them go.  It was very cool to see the deer all around me but not aware of my presence.  I’d love to get back out, but this coming weekend starts gun hunting here in Wisconsin, and I’d prefer not to be in the woods with all the bullets that will be flying around.

hunt-guard

My last two posts showed how to query SQL Server and Oracle using Powershell.  In this post I’m going to share my Powershell script that I use to create a nightly monitoring workbook in Excel.  Each morning when I get in I open that workbook to be sure everything is well in my databases.  The workbook is divided into four tabs:

  1. Scheduled Jobs
  2. Backups
  3. Database file space
  4. Server disk space

I use the information in these worksheets to investigate failed jobs, make sure my backups are up to date, grow any database files that need to be grown, and make sure my servers aren’t running out of space.  Please note I found a script similar to this online and adapted it for my purposes.  The idea behind this monitoring is not original, but my implementation is.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false

$WB = $Excel.Workbooks.Add()
$WB.Worksheets.Add()
$WB.Worksheets.Add()
$WB.Worksheets.Add()
$now = [datetime]::now.ToString('yyyyMMdd')
$a = "\\Servername\IT\Gugg\Powershell\ServerMonitoring\ServerMonitoring$now.xlsx"
#write-host $a
$WB.SaveAs($a)

#################################################################################
#####                             SQL Agent Jobs                                #####

$Sheet = $WB.Worksheets.Item(1)
$Sheet.Name = "SQL Agent Jobs"

#Counter variable for rows
$intRow = 1

#Header for SQL Server
$Sheet.Cells.Item($intRow,1) = "SQL Server"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 10
$intRow++

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"
      $Sheet.Cells.Item($intRow,4) = "ENABLED"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server Agent job status information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $jobs=$srv.JobServer.Jobs

     #Formatting using Excel


ForEach ($job in $jobs)  
{ 

       # Formatting for the failed jobs 
       if ($job.LastRunOutcome -eq 0) 
       { 
           $fgColor = 3 
       } 
       else 
       { 
           $fgColor = 0 
       } 
   

       $Sheet.Cells.Item($intRow, 1) =  $job.Name 
       $Sheet.Cells.Item($intRow, 2) =  $job.LastRunOutcome.ToString() 
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
       $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
       $Sheet.Cells.Item($intRow, 4) =  $job.IsEnabled

    
            
       $intRow ++ 
   
} 
   $intRow ++ 


}

#Header for Oracle
$Sheet.Cells.Item($intRow,1) = "ORACLE"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 30
$intRow++

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{

#Create column headers
     $Sheet.Cells.Item($intRow,1) = "DATABASE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "OWNER"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,4) = "LAST RUN DATE"
      $Sheet.Cells.Item($intRow,5) = "ENABLED"

     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleSchedulerQueryString = "with cte as (SELECT sjrd.job_name ,sjrd.owner ,sjrd.status ,TO_CHAR(sjrd.actual_start_date ,'yyyy-mm-dd HH24:MI:SS') as ACTUAL_START_DATE ,sjrd.error# ,asj.ENABLED ,ROW_NUMBER() OVER (Partition by sjrd.JOB_NAME ORDER BY sjrd.ACTUAL_START_DATE DESC) as rn FROM dba_scheduler_job_run_details sjrd INNER JOIN ALL_SCHEDULER_JOBS asj on sjrd.JOB_NAME = asj.JOB_NAME) select JoB_Name ,owner ,Status ,ACTUAL_START_DATE ,ENABLED from cte where rn = 1"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleSchedulerQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraSchedJobs = $OracleCommand.ExecuteReader()
     $OraCounter = $OraSchedJobs.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraSchedJobs.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 5) {
            $Sheet.Cells.Item($intRow, 1) = $OraSchedJobs.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraSchedJobs.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraSchedJobs.GetValue($o+2)
            if ($OraSchedJobs.GetValue($o+2) -ne "SUCCEEDED") 
                { 
                    $Sheet.Cells.item($intRow, 3).Interior.ColorIndex = 3 
                } 
            $Sheet.Cells.Item($intRow, 4) = $OraSchedJobs.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraSchedJobs.GetValue($o+4)
            $intRow++
        }
    }
    $OracleConnection.Close()
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

##############################################################################
#####                    Database Backups                                #####

#Add a new sheet
$Sheet = $WB.Worksheets.Item(2)
$Sheet.Name = "Database Backups"

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST FULL BACKUP"
      $Sheet.Cells.Item($intRow,3) = "LAST DIFF BACKUP"
      $Sheet.Cells.Item($intRow,4) = "LAST LOG BACKUP"
      $Sheet.Cells.Item($intRow,5) = "LAST BACKUP AGE(DAYS)"

     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #Formatting using Excel


ForEach ($db in $dbs)  
{ 
   if ($db.Name -ne "tempdb") #We do not need the backup information for the tempdb database 
   {         
       if($db.LastBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups 
       { 
           $fullBackupDate="Never been backed up" 
           $fgColor3="red" 
       } 
       elseif(($db.LastBackupDate) -lt ((Get-Date).AddDays(-7)))
       {
              $fullBackupDate="{0:g}" -f  $db.LastBackupDate
           $fgColor3="red"
       }
       else 
       { 
           $fullBackupDate="{0:g}" -f  $db.LastBackupDate
           $fgColor3 = "xlnone"
       } 
       
       $Sheet.Cells.Item($intRow, 1) = $db.Name 
       $Sheet.Cells.Item($intRow, 2) = $fullBackupDate
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor3
       
       if($db.LastDifferentialBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups 
       { 
           $diffBackupDate="Never had differential taken"
           $fgColor4 = 15
       } 
       elseif(($db.LastDifferentialBackupDate) -lt ((Get-Date).AddDays(-2)))
       {
              $diffBackupDate="{0:g}" -f  $db.LastDifferentialBackupDate
           $fgColor4=3
       }
       else 
       { 
           $diffBackupDate="{0:g}" -f  $db.LastDifferentialBackupDate 
           $fgColor4=1
       }
    
         
       $Sheet.Cells.Item($intRow, 3) = $diffBackupDate
       $Sheet.Cells.item($intRow, 3).Font.ColorIndex = $fgColor4
   
       #We use the .ToString() Method to convert the value of the Recovery model to string and ignore Log backups for databases with Simple recovery model 
       if ($db.RecoveryModel.Tostring() -eq "SIMPLE")
       { 
           $logBackupDate="N/A"
           $fgColor5 = 15
           $fgColor6 = "xlNone"
       } 
       else 
       { 
           if($db.LastLogBackupDate -eq "1/1/0001 12:00 AM")  
           { 
               $logBackupDate="Never been backed up" 
               $fgColor5 = 1
                  $fgColor6 = 3
           } 
           elseif(($db.LastLogBackupDate) -lt ((Get-Date).AddHours(-5)))
           {
                  $logBackupDate= "{0:g}" -f $db.LastLogBackupDate
               $fgColor5 = 1
                  $fgColor6 = 3
           }
           else 
           { 
               $logBackupDate= "{0:g}" -f $db.LastLogBackupDate
               $fgColor5 = 1
               $fgColor6 = "xlNone"
           } 
            
       } 
    
       $Sheet.Cells.Item($intRow, 4) = $logBackupDate
       $Sheet.Cells.Item($intRow, 4).Font.ColorIndex = $fgColor5
       $Sheet.Cells.Item($intRow, 4).Interior.ColorIndex = $fgColor6
    
       #Define your service-level agreement in terms of days here 
       if ($db.LastBackupDate -gt $db.LastDifferentialBackupDate)
           {
            if ($db.LastBackupDate -gt $db.LastLogBackupDate)
                {
                    $LatestBackup = $db.LastBackupDate
                }
            else
                {
                    $LatestBackup = $db.LastLogBackupDate
                }
        }
        elseif ($db.LastDifferentialBackupDate -gt $db.LastBackupDate)
        {
            if ($db.LastDifferentialBackupDate -gt $db.LastLogBackupDate)
                {
                    $LatestBackup = $db.LastDifferentialBackupDate
                }
            else
                {
                    $LatestBackup = $db.LastLogBackupDate
                }
        }
        else
        {
            $LatestBackup = $db.LastLogBackupDate
        }
        
        
       $NumDaysSinceLastBackup = ((Get-Date) - $LatestBackup).Days #We use Date Math to extract the number of days since the last full backup  
       if ($NumDaysSinceLastBackup -gt 1) 
       { 
           $fgColor = 3 
       } 
       else 
       { 
           $fgColor = 0 
       } 
    
       $Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastBackup 
       $Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor 
    
    
            
       $intRow ++ 
    
       } 
   } 
   $intRow ++ 


}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++
     
     $Sheet.Cells.Item($intRow,1) = "DATAFILE BACKUPS"
     $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 50
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     
     $intRow++

      $Sheet.Cells.Item($intRow,1) = "TABLESPACE"
      $Sheet.Cells.Item($intRow,2) = "FILE TYPE"
      $Sheet.Cells.Item($intRow,3) = "BACKUP TYPE"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "BACKUPSET TIME"


     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleDFBUQueryString = "with cte as (select DF_TABLESPACE,FILE_TYPE,BACKUP_TYPE,FNAME,BS_COMPLETION_TIME, ROW_NUMBER () OVER (PARTITION BY DF_TABLESPACE,FILE_TYPE,BACKUP_TYPE,FNAME ORDER BY BS_COMPLETION_TIME DESC) rn from V`$BACKUP_FILES WHERE FILE_TYPE = 'DATAFILE') SELECT DF_TABLESPACE, FILE_TYPE, BACKUP_TYPE, FNAME, BS_COMPLETION_TIME FROM cte WHERE rn = 1"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleDFBUQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraDFBU = $OracleCommand.ExecuteReader()
     $OraCounter = $OraDFBU.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraDFBU.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 5) {
            $Sheet.Cells.Item($intRow, 1) = $OraDFBU.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraDFBU.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraDFBU.GetValue($o+2)
            $Sheet.Cells.Item($intRow, 4) = $OraDFBU.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraDFBU.GetValue($o+4)
            $intRow++
        }
    }
    $intRow++
    $Sheet.Cells.Item($intRow,1) = "OTHER FILE BACKUPS"
     $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 54
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     
     $intRow++

      $Sheet.Cells.Item($intRow,1) = "BACKUP TYPE"
      $Sheet.Cells.Item($intRow,2) = "FILE TYPE"
      $Sheet.Cells.Item($intRow,3) = "BACKUP TIME"
      
      
      #Format the column headers
     for ($col = 1; $col -le 3; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }
    
    $intRow++
    #We're going to use the existing connection, which is already open, but we need a new query string and command object
    $OracleOFBUQueryString = "with cte as ( select BACKUP_TYPE, FILE_TYPE, NVL(COMPLETION_TIME,BS_COMPLETION_TIME) COMPLETION_TIME, ROW_NUMBER () OVER (PARTITION BY BACKUP_TYPE, FILE_TYPE ORDER BY NVL(COMPLETION_TIME,BS_COMPLETION_TIME) DESC) rn from V`$BACKUP_FILES WHERE FILE_TYPE<> 'DATAFILE') SELECT BACKUP_TYPE, FILE_TYPE, COMPLETION_TIME FROM cte WHERE rn = 1"
    $OracleCommandOF = New-Object System.Data.OracleClient.OracleCommand($OracleOFBUQueryString, $OracleConnection)
    $OraOFBU = $OracleCommandOF.ExecuteReader()
     $OraCounterOF = $OraOFBU.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraOFBU.Read()) {
        for ($o=0; $o -lt $OraCounterOF; $o = $o + 3) {
            $Sheet.Cells.Item($intRow, 1) = $OraOFBU.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraOFBU.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraOFBU.GetValue($o+2)
            $intRow++
        }
    }
    
    $OracleConnection.Close()
    $intRow++
     
}

$Sheet.UsedRange.EntireColumn.AutoFit()

#############################################################################################################
#####                                    Database Disk Space                                                #####

#Create a new Excel object using COM 
$Sheet = $WB.Worksheets.Item(3)
$Sheet.Name = "Database File Space"

#Counter variable for rows
$intRow = 1



#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "AUTOSHRINK"
      $Sheet.Cells.Item($intRow,3) = "RECOVERY MODEL"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "SIZE (MB)"
      $Sheet.Cells.Item($intRow,6) = "SPACE USED (MB)"
      $Sheet.Cells.Item($intRow,7) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

#     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
#     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

#     $dbs = $s.Databases

     #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

     #Formatting using Excel

     ForEach ($db in $dbs) 
     {
     $FileGroups = $db.FileGroups
        ForEach($FileGroup in $FileGroups)
        {
        $Files = $FileGroup.Files
            ForEach($File in $Files)
            {
#              #Divide the value of SpaceAvailable by 1KB 
#              $FileSpaceAvailable = $File.SpaceAvailable/1KB 

              #Format the results to a number with three decimal places 
    #          $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

              $Sheet.Cells.Item($intRow, 1) = $db.Name
     
               #Change the background color of the Cell depending on the AutoShrink property value 
               if ($db.AutoShrink -eq "True")
              {
                   $fgColor = 3
              }
              else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 2) = $db.AutoShrink 
              $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor

              $Recovery = $db.RecoveryModel
              Switch ($Recovery)
              {
                 Full { $RecoveryText = 'Full' }
                 BulkLogged { $RecoveryText = 'Bulk-Logged?' }
                 Simple { $RecoveryText = 'Simple' }
                 Default { $RecoveryText = 'unable to determine' }
                }

              $Sheet.Cells.Item($intRow, 3) = $RecoveryText
              $Sheet.Cells.Item($intRow, 4) = $File.FileName
              $Sheet.Cells.Item($intRow, 5) = "{0:N0}" -f ($File.Size/1024)

              #Change the background color of the Cell depending on the SpaceAvailable property value 
              if (($File.UsedSpace / $File.Size * 100) -gt 95.00)
              {
                   $fgColor = 3
              }
               else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 6) = "{0:N0}" -f ($File.UsedSpace/1024 )
              $Sheet.Cells.Item($intRow, 7) = 1-($File.UsedSpace / $File.Size)
              $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
              $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"

              $intRow ++
                  }
            }
        #Continue with log files
        $LogFiles = $db.LogFiles
        ForEach ($LogFile in $LogFiles)
            {
                $Sheet.Cells.Item($intRow, 1) = $db.Name
     
               #Change the background color of the Cell depending on the AutoShrink property value 
               if ($db.AutoShrink -eq "True")
              {
                   $fgColor = 3
              }
              else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 2) = $db.AutoShrink 
              $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor

              $Recovery = $db.RecoveryModel
              Switch ($Recovery)
              {
                 Full { $RecoveryText = 'Full' }
                 BulkLogged { $RecoveryText = 'Bulk-Logged?' }
                 Simple { $RecoveryText = 'Simple' }
                 Default { $RecoveryText = 'unable to determine' }
                }

              $Sheet.Cells.Item($intRow, 3) = $RecoveryText
              $Sheet.Cells.Item($intRow, 4) = $LogFile.FileName
              $Sheet.Cells.Item($intRow, 5) = "{0:N0}" -f ($LogFile.Size/1024)

              #Change the background color of the Cell depending on the SpaceAvailable property value 
              if (($LogFile.UsedSpace / $LogFile.Size * 100) -gt 95.00)
              {
                   $fgColor = 3
              }
               else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 6) = "{0:N0}" -f ($LogFile.UsedSpace/1024 )
              $Sheet.Cells.Item($intRow, 7) = 1-($LogFile.UsedSpace / $LogFile.Size)
              $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
              $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"

              $intRow ++
            }

     }

$intRow ++

}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "SPACE MANAGEMENT"
      $Sheet.Cells.Item($intRow,3) = "ARCHIVELOG MODE"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "SIZE (MB)"
      $Sheet.Cells.Item($intRow,6) = "SPACE USED (MB)"
      $Sheet.Cells.Item($intRow,7) = "PERCENT FREE"
      
      #Format the column headers
     for ($col = 1; $col -le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleSchedulerQueryString = "SELECT  Substr(df.tablespace_name,1,20) , dt.SEGMENT_SPACE_MANAGEMENT, vd.LOG_MODE, Substr(df.file_name,1,80), Round(df.bytes/1024/1024,0), decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)), 1 - decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes),3)) FROM  DBA_DATA_FILES DF INNER JOIN dba_tablespaces dt on DF.TABLESPACE_NAME = dt.tablespace_name LEFT OUTER JOIN (SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E ON  e.file_id = df.file_id LEFT OUTER JOIN (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f ON df.file_id  = f.file_id CROSS JOIN v`$database vd ORDER BY df.tablespace_name, df.file_name"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleSchedulerQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraSchedJobs = $OracleCommand.ExecuteReader()
     $OraCounter = $OraSchedJobs.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraSchedJobs.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 7) {
            $Sheet.Cells.Item($intRow, 1) = $OraSchedJobs.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraSchedJobs.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraSchedJobs.GetValue($o+2)
            $Sheet.Cells.Item($intRow, 4) = $OraSchedJobs.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraSchedJobs.GetValue($o+4)
            $Sheet.Cells.Item($intRow, 6) = $OraSchedJobs.GetValue($o+5)
            $Sheet.Cells.Item($intRow, 7) = $OraSchedJobs.GetValue($o+6)
            $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"
            if ($OraSchedJobs.GetValue($o+6) -lt 0.05) 
                { 
                    $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = 3 
                } 
            $intRow++
        }
    }
    $OracleConnection.Close()
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

############################################################################################################################
######################                    Server disk space                                     ############################

#Create a new Excel object using COM 
$Sheet = $WB.Worksheets.Item(4)
$Sheet.Name = "Server Disk Space"

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DRIVE"
      $Sheet.Cells.Item($intRow,2) = "SIZE GB"
      $Sheet.Cells.Item($intRow,3) = "FREE SPACE GB"
      $Sheet.Cells.Item($intRow,4) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
    
    
    $disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3";
    
    foreach($disk in $disks)
    {
         $percentFree = ($disk.FreeSpace / $disk.Size)
         if ($percentFree -lt .15)
         {
             if ($percentFree -lt .05)
            {
                $fgColor7 = 3
            }
            else
            {
                $fgColor7 = 6
            }
         }
         else
         {
             $fgColor7 = "xlNone"
         }
        
         $Sheet.Cells.Item($intRow,1) = $disk.DeviceID
         $Sheet.Cells.Item($intRow,2) = "{0:N1}" -f ($disk.Size / 1073741824)
         $Sheet.Cells.Item($intRow,3) = "{0:N1}" -f ($disk.FreeSpace / 1073741824)
         $Sheet.Cells.Item($intRow,4) = $percentFree
         $Sheet.Cells.item($intRow, 4).NumberFormat = "0.00%"
         $Sheet.Cells.Item($intRow,4).Interior.ColorIndex = $fgColor7
         
         $intRow++
    }
    
    $intRow++
}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DRIVE"
      $Sheet.Cells.Item($intRow,2) = "SIZE GB"
      $Sheet.Cells.Item($intRow,3) = "FREE SPACE GB"
      $Sheet.Cells.Item($intRow,4) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
    
    
    $disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3";
    
    foreach($disk in $disks)
    {
         $percentFree = ($disk.FreeSpace / $disk.Size)
         if ($percentFree -lt .15)
         {
             if ($percentFree -lt .05)
            {
                $fgColor7 = 3
            }
            else
            {
                $fgColor7 = 6
            }
         }
         else
         {
             $fgColor7 = "xlNone"
         }
        
         $Sheet.Cells.Item($intRow,1) = $disk.DeviceID
         $Sheet.Cells.Item($intRow,2) = "{0:N1}" -f ($disk.Size / 1073741824)
         $Sheet.Cells.Item($intRow,3) = "{0:N1}" -f ($disk.FreeSpace / 1073741824)
         $Sheet.Cells.Item($intRow,4) = $percentFree
         $Sheet.Cells.item($intRow, 4).NumberFormat = "0.00%"
         $Sheet.Cells.Item($intRow,4).Interior.ColorIndex = $fgColor7
         
         $intRow++
    }
    
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

$WB.Save()
$WB.Close()
$Excel.Application.Quit()
cls

There are a few things to note:

  • I keep the list of SQL Servers and Oracle Servers in text files on a folder share.  That is the\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt and

    \\Servername\IT\Gugg\Powershell\SQL_Servers.txt that you are seeing over and over.

  • You’ll notice several places where the Excel cells are shaded red or yellow, depending upon conditions.  It helps to see potential problems highlighted when going through the new workbook each morning.
  • I use Windows Task Scheduler to kick this powershell script off each morning at 6 AM.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s