Credentials in Powershell

I had the opportunity to attend the Madison Fishing Expo a few weekends ago.  It was a great way to stay excited for the upcoming fishing year during these cold winter months.  I didn’t get any new information, but I did let my son pick out a few cheap lures to add to his tackle box.

Choosing fishing lures

The warm weather has completely melted the ice off our area lakes (nice and early!), but we, along with almost the entire rest of the country, got a round of winter weather this week, so we’re back to almost a foot of snow on the ground.  It’ll be at least a few more weeks before I launch the boat for the first time this year.

The company I work for has been in the process of strengthening its security posture for the last few years.  Recently, they took the step of creating separate administrator accounts to use when we are doing things that require administrative permissions.  Up until now, I only had one account – an administrator-level account.  I expected at least a few things to break once they turned off my higher privileges, and those expectations were met.  The thing I’m going to touch on today is Powershell privileges.

I use a Powershell script that is run daily to collect various health statistics regarding my SQL databases and servers.  This script is run from Windows Task Scheduler, and is run from my laptop using my Windows AD account user.  Once that user lost its admin privileges, a few of the collection methods failed.  In order to get them to work, I needed to plug in my admin account for that specific method.  I found a neat way to do that using Powershell’s Credential object.

First, I stored the account password in a text file.  The password is encrypted and placed as a file on the disk by using the following Powershell command:

20170316 Powershell Credential Create Encrypted PW

Opening the new file contains the following:

20170316 Powershell Credential Encrypted PW

So you can see that the password is definitely encrypted.

Now I can reference that file whenever I need to enter credentials.

#Create a credential for connecting to the server
$user = "Domain\adminuser"
$pw = cat "C:\Temp\Password.txt" | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user, $pw

#Access the Disk Info using my new credntial
$disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3" -Credential $cred;

Using this method you can pass credentials to your Powershell script without having to store them in plain text on your computer. The only downside in my case is I will have to update my encrypted password file whenever my admin account has a password change.

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.

Querying Oracle through Powershell

I’ve been dealing with some back pain this summer/fall that has made fishing uncomfortable.  I’m still able to do it, but when you’re in constant pain, it takes the fun out of doing pretty much everything but laying down.  That, along with the general busyness of the summer and fall, have kept the fishing down to a minimum.  However, I did get out for a weekend and found that while musky fishing was difficult, slip-bobber fishing was better.  I’d never slip-bobber fished until this year, but I have found it’s really enjoyable.  You can get the bait right above the bottom at any depth, and that presentation seems to work well when the fish aren’t as aggressive.

This brings me to my second note – I love catching smallmouth bass.  They fight really hard for their size, with deep runs and long battles.  Below is one of two that I caught at dusk on a weedy finger that stuck out into the main basin of the lake.

late-summer-smallmouth-bass

As you’d expect, querying Oracle database from Powershell isn’t as easy as querying SQL Server.  Powershell and SQL Server are both Microsoft technologies, so they play together pretty nicely.  With Oracle, their are a couple ways to connect to the database.  You can use the OLE DB database connector or the Oracle Client version.  I tried using the OLE DB connector first, but found I was unable to get it going.  So I switched over to the Oracle Client, which was a bit more complicated in the setup, but worked.

In order to use the Oracle Client, you have to install it on your system first.  I will warn you that this is not a minor install.  The installer itself is 600 MB, and you need to make sure you grab the correct version (32 vs 64 bit), depending on your OS.  Once installed you should be ready to go.  Here is the basic pattern for creating the connection and querying data:

#Create connection to Oracle
 $OracleconnectionString = "Data Source=OracleDatabaseServer;User Id=dgugg;Password=myPass;Integrated Security=No"
 $OracleSchedulerQueryString = "select IMAGE_ID, DESCRIPTION from FMSH_IMAGE WHERE Rownum < 11 ORDER BY IMAGE_ID DESC"
 [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()
 $OraImages = $OracleCommand.ExecuteReader()
 $OraCounter = $OraImages.FieldCount
 While ($OraImages.Read()) {
    for ($o=0; $o -lt $OraCounter; $o = $o++ ) {
        $OraImages.GetName($o) = $OraImages.GetValue($o)
        }
    }
$OracleConnection.Close()

Here is the gist of the code:

  • Define a connection string to the Oracle database.
  • Define your SQL statement
  • Load the Oracle Client Assembly into this instance of Powershell
  • Define and create your Oracle Connection Object
  • Define and create your Oracle command
  • Open your connection to Oracle
  • Execute your SQL into a recordset
  • Loop through the recordset from beginning to end
    • In each loop, print out the field name and field value

There are a few gotchas to keep in mind:

  1. Don’t end your SQL statement with a semicolon.  I did this and couldn’t figure out why it wasn’t working.  Once I took it out it was smooth sailing.
  2. Don’t forget to escape Powershell special characters.  I was doing a query from the V$Database view and again ran into problems.  Once I escaped the dollar sign ($) with a back tick (`), everything once again started working.

Stay tuned for my final blog in this series where I will use this connection to Oracle as well as the previously posted connection to SQL Server to create a workbook that has daily monitoring of my database health stats.

Querying SQL Server through Powershell

I got Up North this last weekend.  It was the first weekend in a couple months, and I definitely haven’t had the time for fishing like I have in years past.  This last weekend was no exception, as we spent most of the time re-sealing the logs in Grandpa’s cabin.

log-sealing-cabin

I did get out for an hour or two of row-trolling, but didn’t manage any bites.  It’s a bit sad because usually this is my favorite time of year to fish, but there have just been too many other things going on this year.  I did get the top picture above of the lake in the early morning behind the rowboat.  I love the look of a still lake in the morning.

I have found a great use for Powershell in my daily DBA tasks.  I use it to create an Excel workbook each day with various health checks on my SQL Servers.  It is great to look in a single workbook and see all the potential issues across all the servers highlighted.  I have also begun to add my Oracle servers to the same process.  In this post I’m going to share the basics for connecting to SQL Server.  The next post will do the same for Oracle, and the final post in this series will show my full script for creating the workbook.

#Load the dll that contains the SQL Server objects. 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
#Create the SQL Server instance.  This will be the domain name of the server 
$instance = mySQLServer 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

It’s that easy.  Now within the SQL Server, I can reference different areas I may want to monitor:

#SQL Agent jobs 
$jobs=$srv.JobServer.Jobs 

#Databases 
$dbs = $srv.Databases 
#Loop through each database 
ForEach ($db in $dbs) 
{ 
#Database Name 
$db.Name 
#Last backup date 
$db.LastBackupDate 
}

Stay tuned for my next post which will show how to connect to and query Oracle database.  The final post in this series will show how to use the above script to create an Excel workbook that gives an awesome summary of the health of my SQL Server databases and Oracle databases.

Start a Windows Service with Powershell

I have finally scheduled an ice fishing trip. I’m going to be taking my son out on Sunday for a little while. I have to admit that I’m a bit nervous about ice fishing. I haven’t been out too many times, so I don’t really know how to judge how safe the ice is. Additionally, we’ve had an extremely mild winter. With the exception of one week where it was far below freezing, it has been oscillating back and forth over the freezing point daily. Yesterday was relatively warm – the temperature was probably between 32 and 40 degrees throughout the day. Today is colder, with temps in the 20s. The next post should have reports from the upcoming fishing trip.
I was recently tasked with creating a powershell script to make sure a Windows Service had started correctly. One of our applications would often start but then run into a phantom error a short time later and stop. We have a Task Scheduler job that stops the service, then starts it again a short time later each night. I decided to create a script that would check the status of the service. If it is started, then quit. If it is stopped, attempt to start it and wait five minutes, then do it again. Keep repeating the process until the service starts and stays started. I also decided to add a logging component to this script, which I had never done before. I though it would be useful to be able to see how many times the script had to try to start the service each time. Here is the script:

$Logfile = 'C:\AppName\Powershell Start AppName Log.log'

Function LogWrite
{
   Param ([string]$logstring)
   Add-content $Logfile -value $logstring
}


$ServiceName = 'AppName'
$arrService = Get-Service -Name $ServiceName
LogWrite "$(Get-Date) Start AppName Service powershell script started."

while ($arrService.Status -ne 'Running')
{
   LogWrite "$(Get-Date) Service is currently in $($arrService.Status) status"
   Start-Service $ServiceName
   LogWrite "$(Get-Date) Attempting to start AppName service"
   Start-Sleep -seconds 300
   $arrService.Refresh()
   if ($arrService.Status -eq 'Running')
   {
       LogWrite "$(Get-Date) Service has started successfully."
   }
   else
   {
       LogWrite "$(Get-Date) Service has failed to start, preparing to retry."
   }
}

This is another example of how we can use Powershell to make our lives easier.

The Power of Powershell

My brother-in-law’s 48″ muskie caught while pre-fishing for the 2015 Rhinelander Hodag muskie challenge was an epic battle. The fish hit the lure 10 – 15 feet from the boat, yet managed to stay in the water for what felt like several minutes before submitting to the net. Having the memory of a different large fish that we had lost right at the net about a month earlier, I was desperate not to mess this one up. I wanted to get the net ready as soon as possible, so instead of hooking my lure to the clip right above the reel, I reeled it up as close to the end of the pole as the leader would allow. This ended up being a mistake since the fish swam circles around the boat several times, and I had to carefully move the pole each time. At the same time, I was holding the net and trying not to get it hooked on anything inside the boat. It was a high pressure situation where I had to do several things at once, and we ultimately succeeded in landing the giant fish.

Jason's muskie
Powershell is great for doing multiple things at once. Maybe not technically at the same time, but the skilled DBA can create scripts to automate many different tasks which can be run one right after another. This is a great way to accomplish a large amount, which would take quite a while to do manually. I have a set of scripts that create an Excel workbook each day with a set of metrics about each of my instances, allowing me to see any possible problems immediately upon getting in each morning. The script shows failed SQL Agent jobs, backup ages, disk usage, and file used size. It highlights in red anything that needs attention, such as files that need to be manually grown or databases that haven’t been backed up recently. I am not great at powershell, but I was able to find some examples online and modify them to work with my current environment.

One personal goal I have for the upcoming year is to find more ways to use powershell to increase my efficiency. In order to begin doing that, I’ve started to practice using powershell. As I mentioned in my previous post, I’m using http://adventofcode.com/ to help with this challenge.
The second day’s challenge involves a list of dimensions of boxes that need to be wrapped up and tied with a bow by Santa’s elves. Given the list, which contains 1,000 length X width X height measurements, I need to get the total amount of feet needed where each needs the surface area of each box plus a little extra slack, the area of the smallest size. I also needed to get the ribbon around the gift, which was the smallest perimeter around the box plus a litle extra for bow, equal to the cubic volume of the box. I learned some basic commands in Powershell that I either hadn’t learned or had forgotten, such as how to ingest a text file into a variable and how to split an array. In the end, it took a few tries but I got the answer correct for both parts of the question. Below is my final code for each.

$dimensions = get-content N:\adventday2input1.txt
$feet = 0
foreach ($dim in $dimensions) {
[int]$l,[int]$w,[int]$h = $dim.split('x',3)
$feet = $feet + $l*$w*2
$feet = $feet + $l*$h*2
$feet = $feet + $h*$w*2
$intmin = [math]::min($l*$w,$l*$h)
$feet = $feet + [math]::min($intmin,$w*$h)
}
$feet
$dimensions = get-content N:\adventday2input1.txt
$feet = 0
foreach ($dim in $dimensions) {
[int]$l,[int]$w,[int]$h = $dim.split('x',3)
$intmin = [math]::min(2*$l+ 2*$w,2*$l+2*$h)
$feet = $feet + [math]::min($intmin,2*$w+2*$h)
$feet = $feet + $l*$w*$h
}
$feet

In summary, Powershell is a great tool and I’m looking forward to increasing my use of it as 2016 progresses.