Total Pageviews

Monday, January 6, 2014

A PowerShell Script to monitor enterprise wide disk space usage

The folks who are responsible for administering thousands of Windows Servers all know that monitoring disk space is crucial for application performance. When there are numerous Windows Servers, such as thousands, it becomes more difficult to know what is going on where; and thus putting an effective monitoring tool in place is absolutely necessary for proactive monitoring purposes.

I recently faced a challenge to collect disk space usage about more than 300 (SQL Servers) out of 2000 Windows Servers across the enterprise and send the collected information by e-mail. To complete the task, the output should be easy to understand and the server should contain a brief description so that we can quickly identify the purpose of the server. Following is a desirable output format.

Figure#1: Sample disk usage html report:

To achieve this goal, I have developed a custom PowerShell Script and scheduled it to run every 6 hours. It investigates all Windows Servers listed in a CSV file through Job. To schedule this script, I have used SQL Server Agent since I felt more comfortable with it and because it is easy to implement. However, Windows Schedule Task can also be used to do the same functions.

PowerShell Script:
Download the original Script: http://bit.ly/1cZNScb

#set-executionpolicy unrestricted

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Send email to all DBA
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function Send-EmailToDBA {
 param(
        [Parameter(Mandatory=$true)][string]$emailBody,
        [Parameter(Mandatory=$true)][string]$emailSubject
    )

    $EmailFrom = "noreply@myCompanyorg"
    $EmailTo = "abcxyz@myCompanyorg, abc@myCompanyorg, xyz@myCompanyorg"

    $SMTPServer = "smtpServer.org"

    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    $msg = new-object Net.Mail.MailMessage($EmailFrom,$EmailTo,$EmailSubject,$Emailbody)
    $msg.IsBodyHTML = $true
    $mailer.send($msg)
} # end of function


#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Get-DiskInfo
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function Get-DiskInfo {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
        [string[]]$FileFullPath = 'i:\ServerList\servers.txt',
        
        [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
        [decimal]$DiskThreshold = 10
    )
    
    BEGIN {}
    PROCESS {
        $SHBServers = (import-csv $FileFullPath -Header Server, Description)
        foreach ($computer in $SHBServers) {           

            $Server = $($Computer.Server).split("\")[0]
            # $disks =Get-WMIObject -ComputerName $computer.server Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
            $disks =Get-WMIObject -ComputerName $Server Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
           
            foreach ($disk in $disks ) {

               if ($disks.count -ge 1) {           
                   $Used= $disk.freespace / $disk.size * 100
                   $result =  @{'Server'=$computer.server;
                              'Server Description'=$computer.description;
                              'Volume'=$disk.VolumeName;
                              'Drive'=$disk.name;
                              'Size (gb)'="{0:n2}" -f ($disk.size / 1gb);
                              'Used (gb)'="{0:n2}" -f (($disk.size - $disk.freespace) / 1gb);
                              'Free (gb)'="{0:n2}" -f ($disk.freespace / 1gb);
                              '% free'="{0:n2}" -f ($disk.freespace / $disk.size * 100)}                         
                             

                   $obj = New-Object -TypeName PSObject -Property $result
                   if ($Used -lt $Diskthreshold){  
                        Write-Output $obj }
               }
            }
        }
    }
    END {}
} # end of function

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Script to generate disk usage report
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
$Today = [string]::Format( "{0:dd-MM-yyyy}", [datetime]::Now.Date )
$ReportFileName = "i:\Sarjen\Report\DiskUsage_$Today.html"

# Custom HTML Report Formatting
$head = @"
        <style>
            BODY{font-family: Arial; font-size: 8pt;}
            H1{font-size: 16px;}
            H2{font-size: 14px;}
            H3{font-size: 12px;}
            TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse; background-color:#D5EDFA}
            TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #94D4F7;}
            TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}        
        </style>
"@

    #define an array for html fragments
    $fragments=@()

    # Set free disk space threshold below in percent (default at 10%)
    [decimal]$thresholdspace = 20

    #this is the graph character
    [string]$g=[char]9608 

    # call the main function
    $Disks = Get-DiskInfo `
                -ErrorAction SilentlyContinue `
                -FileFullPath ("i:\Sarjen\SQLServers.txt") `
                -DiskThreshold $thresholdspace
           
    #create an html fragment
    $html= $Disks|select @{name="Server";expression={$_.Server}},
                  @{name="Server Description";expression={$_."Server Description"}},
                  @{name="Drive";expression={$_.Drive}},
                  @{name="Volume";expression={$_.Volume}},
                  @{name="Size (gb)" ;expression={($_."size (gb)")}},
                  @{name="Used (gb)";expression={$_."used (gb)"}},
                  @{name="Free (gb)";expression ={$_."free (gb)"}},
                  @{name="% free";expression ={$_."% free"}},           
                  @{name="Disk usage";expression={
                        $UsedPer= (($_."Size (gb)" - $_."Free (gb)")/$_."Size (gb)")*100
                        $UsedGraph=$g * ($UsedPer/4)
                        $FreeGraph=$g* ((100-$UsedPer)/4)
                        #using place holders for the < and > characters
                         "xopenFont color=Redxclose{0}xopen/FontxclosexopenFont Color=Greenxclose{1}xopen/fontxclose" -f $usedGraph,$FreeGraph }}`
        | sort-Object {[decimal]$_."% free"} `
        | ConvertTo-HTML -fragment
    
    #replace the tag place holders.
    $html=$html -replace "xopen","<"
    $html=$html -replace "xclose",">"
    
    #add to fragments
    $Fragments+=$html         

    #write the result to a file
    ConvertTo-Html -head $head -body $fragments `
     | Out-File $ReportFileName
    
      # Open the html file
      # ii $ReportFileName

    $Emailbody= (Get-Content $ReportFileName ) | out-string
    $EmailSubject = "Disk usage report - Drives less than $thresholdspace% free space"

    Send-EmailToDBA -EmailBody $Emailbody -EmailSubject $EmailSubject                          


Figure#2: CSV file for Server list with description: 

Figure#3: SQL Agent Job to run the PowerShell Script:

Pre-requisites:
To collect disk information from multiple servers, we need the following rights or privileges:

1.      WMI access to the target server.
2.      Privileges to run PowerShell Script in the source server. Run the following command:

set-executionpolicy unrestricted

3.      Have PowerShell version 2.0 or above.

Script Explanation:
1.      The PowerShell script reads the Server name (figure#2) from a CSV file. This CSV file contains the Server name and description.
2.      The Script will check the percentage of free disk space, for example 10% or 20%, etc.
3.      The output will be preserved in HTML format.
4.      An e-mail notification will be sent and the email body will contain the HTML table.

Running the Script:
The script can be run manually or through a Windows or SQL Agent Job. Figure#3 is an example of how to run it by utilizing SQL Agent Job.

Conclusion:
I am running this script against 200+ productions Windows Server and it took around 2 minutes to complete. Although this script does its job, there is room for improvement, such as adding error handling and logging. So if you enhance this script and improve its functionality, then I request that you share your modified script with me.

7 comments:

  1. This report is really nice and i am using it..Thanks a lot.

    ReplyDelete
  2. Hello Sarjen,
    The script seems to be good and the output will be very much usefull for most of Administrators.

    The script download link is not working.
    I tried running the script am gettting belwo given error.


    owerCLI D:\TestScripts> .\DiskInfo.ps1
    issing expression after unary operator '-'.
    t D:\TestScripts\DiskInfo.ps1:112 char:22
    - <<<< ErrorAction SilentlyContinue `
    + CategoryInfo : ParserError: (-:String) [], ParseException
    + FullyQualifiedErrorId : MissingExpressionAfterOperator

    PS D:\TestScripts> .\DiskInfo.ps1
    An empty pipe element is not allowed.
    At D:\TestScripts\DiskInfo.ps1:145 char:7
    + | <<<< Out-File $ReportFileName
    + CategoryInfo : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : EmptyPipeElement

    ReplyDelete
  3. Hi Sarjen,

    Thank you for the script.
    I have an issue with partition size above 1.000 GB.
    The graph bar remains blank (no green, no red).
    I'm trying to alter several parameter ({0:n2} to {0:n4}, $UsedPer/2 to $UsedPer/4, 9608 to 9603), but with no luck.
    Do you have any idea to solve it ?
    Thanks,

    Dok

    ReplyDelete
    Replies
    1. Schedule or run the script from a different server.

      Delete
  4. # call the main function
    $Disks = Get-DiskInfo `
    -ErrorAction SilentlyContinue `
    -FileFullPath ("i:\Sarjen\SQLServers.txt") `
    -DiskThreshold $thresholdspace


    Bro, what is i:\Sarjen\SQLServers.txt"

    ReplyDelete
  5. Doesn't work. Blanc HTML report.

    ReplyDelete