Monitoring disk space utilization of server(s) is critical and important job for any administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 10 years, I have faced and handled/managed lot of issues with disk space. This article takes us through the in-detail steps to read each drive and report every drive details based on threshold values. The output is integrated with excel charts. The step by step process quickly take us through the disk space utilization details of server(s). You'll basically feed a list of servers to watch over, and it will report back on these for you, meaning you could also use it as a more general "daily server disk space report"
↑ Return to Top
This article talks about the use of credentials. The credentials can be used to query an external servers which have trust relationship between the domains. Also, list various methods to secure the password. The process iterates through a list of servers and drives that you have listed in a csv file. Checking for disk space status of every listed drive and its status may fall under one of the four status that are defined as critical, warning, low and good. If the disk in question is below the threshold then increment the corresponding status counter which acts as a data source for depicting excel charts. The nice thing about this script is that it will consolidate health status of each listed disks and gives a summary that need your attention (you set the threshold as per requirement because size of the drive may vary from server to server).
Get-credential always pop-up dialog box for entering a password, however, you can save your securestring password to a file or directly feed the password. The problem with this is that the password will be exposed to anyone with access to the file.
The Get-Credential displays a window to enter credential details. This will appear every time when you run the script.The $credential variable store the username and password. It's then fed to the respective queries for further processing.
clear
$credential = Get-Credential
foreach ( $args in get-Content c:\server.txt ) {
get-WmiObject win
32
_logicaldisk -Credential $credential -ComputerName $args -Filter
"Drivetype=3"
|
ft SystemName,DeviceID,VolumeName,@{Label=
"Total SIze"
;Expression={$_.Size /
1
gb -as [int] }},@{Label=
"Free Size"
;Expression={$_.freespace /
gb -as [int] }} -autosize
}
The password is hard coded in the script. Of course the problem with this is that your password will be exposed to anyone with access to the script file.
$User =
'hqnt\abcd'
$Pass = ConvertTo-SecureString
'abcd@2015'
-AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
_logicaldisk -ComputerName $args -Credential $Credentials -Filter
First, Password has to be written to a file
ps:\>read-host -AsSecureString |ConvertFrom-SecureString |Out-File C:\SecurePassword.txt
Second, The credentials are read from the file using PSCredential class. You don't need to re-enter the password over and over again.
'hqnt\abcdv'
$pass= cat C:\passwordstring.txt |ConvertTo-SecureString
_logicaldisk -ComputerName $args -Credentials $cred -Filter
_logicaldisk -ComputerName $args -Filter
Server,Drive,LowTh,WarnTh,CritTh
HQDBSP00
8
,E:,
,
5
3
,F:,
20
18
,G:,
HQSPDB99
01
HQSPDB09
HQSPDB80
This below code defines the output file location and filename. The $date variable hold the date formatting part. Its then appended to $filename to generate more meaningful filename. For example, c:\DMZ_Server_Space_Utilization-2016-09-16
$DirectoryToSaveTo =
"c:\"
$date=Get-Date -
format
"yyyy-MM-d"
$Filename=
"DMZ_Server_Space_Utilization_$($date)"
This portion of code decides whether to pass credentials or not. The Get-credential always pop-up dialog box for entering a password, however you can save your securestring password to a file or directly feed the password. The problem with this is that the password will be exposed to anyone with access to the file. If you want to use the default login credentials then you don't need to mention anything in the code. You can comment the line of code.
'abcd'
''
abcd@
#2016
' -AsPlainText -Force
PowerShell is made available to use Excel component by invoking excel.application COM object that will allow us to work with excel to add data and format that data. The New-object -ComObject creates a new excel object using COM components and it made visible for troubleshooting using visible property set to true. Once everything is defined, we can call the respective methods and properties to create workbook by adding sheet as its item
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
#you can see the Excel sheet and helpful in troubleshooting
$Excel.
visible
= $True
#After instantiating the excel objects, It's a time use its respective methods and properties
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(
)
FileFormat numbers in Mac Excel. These are the main file formats in Windows Excel 2007-2016:
51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx) 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm) 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb) 56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
$xlOpenXMLWorkbook=[int]
51
$Sheet.Cells.Item($row,$column)=
'Computername'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =
48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
'DeviceID'
#Making the font Bold and adjusting the background color (using the Interior.ColorIndex property of each cell
'VolumeName'
'TotalSizeGB'
'UsedSpaceGB'
'FreeSpaceGB'
'%Free'
'Status'
#Set up a header filter
$headerRange = $Sheet.Range(
"a3"
"o3"
$headerRange.AutoFilter() | Out-Null
Import-Csv C:\server.csv|%{
$cserver = $_.Server
$cdrivelt = $_.Drive
$clowth = $_.LowTh
$cwarnth = $_.WarnTh
$ccritth = $_.CritTh
$diskinfo= Get-WmiObject -Class Win
_LogicalDisk -ComputerName $cserver -Filter
"DeviceID='$cdrivelt'"
-Credential $Credentials
ForEach ($disk in $diskinfo)
{
If ($diskinfo.Size -gt
0
) {$percentFree = [Math]::round((($diskinfo.freespace/$diskinfo.size) *
100
))}
Else {$percentFree =
#Process each disk in the collection and write to spreadsheet
$Sheet.Cells.Item($row,
)= $disk.__Server
2
)= $disk.DeviceID
)= $disk.VolumeName
4
)= [math]::Round(($disk.Size /
GB),
)= [math]::Round((($disk.Size - $disk.FreeSpace)/
6
)= [math]::Round(($disk.FreeSpace /
7
)= (
"{0:P}"
-f ($disk.FreeSpace / $disk.Size))
#Determine if disk needs to be flagged for warning or critical alert
If ($percentFree -le $ccritth) {
) =
"Critical"
$critical++
#Check to see if space is near empty and use appropriate background colors
$range = $Sheet.Range((
"A{0}"
-f $row),(
"H{0}"
-f $row))
$range.Select() | Out-Null
#Critical threshold
$range.Interior.ColorIndex =
} ElseIf ($percentFree -gt $ccritth -AND $percentFree -le $cwarnth) {
"Warning"
$warning++
ElseIf ($percentFree -ge $cwarnth -AND $percentFree -lt $clowth) {
"Low"
$
low
++
12
} Else {
"Good"
$good++
$row++
$Sheet.Cells.Item($row,$Column) =
'Critical'
'Warning'
'Low'
'Good'
$Column =
#Critical formula
$Sheet.Cells.Item($row,$Column)=$critical
#Warning formula
$Sheet.Cells.Item($row,$Column)=$warning
#
formula
$Sheet.Cells.Item($row,$Column)=$
#Good formula
$Sheet.Cells.Item($row,$Column)= $good
#Configure the chart
##Use a
D Pie Chart
$chart.ChartType =
70
$chart.Elevation =
40
#Set the location of the chart
$sheet.Shapes.Item(
"Chart 1"
).Placement =
).Top =
30
).Left =
600
<# .SYNOPSIS Name : Disk Space Utilization Report (Get-DiskSpaceExcel.ps1) Description : Get disk space usage information from remote server(s) with WMI and output Excel file Author : Prashanth Jayaram * Some ideas extracted Joe Prox Excel Charting. Refer the reference section for more information * Select list of servers from a CSV file * Get remote Servers information with WMI and PowerShell : * Disk (Disk type, letter, capacity in GB, free space in GB, % free , Status + display a Excel output) .INPUT .csv file with servers to activate .OUTPUTS Console outputs : You can alter the code to write the data to file or console .NOTES Version: 1.0 Author: Prashanth Jayaram Creation Date: 2016-26-09 Purpose/Change: Initial script development .EXAMPLE .\Get-DiskSpaceExcel.ps1 #> ######################################################################################### #DirectoryPath - Make sure you have enough rights to write to that path ######################################################################################### #### Spreadsheet Location - Make sure you change as per your requirement
"DMZ_Server_Space_Utilication_$($date)"
##Get-credential
always
pop-up dialog box for entering a password, however you can save your securestring password to a file or directly feed the password.
##The problem with this is that the password will be exposed to anyone with access to the file.
####User Credentials to access servers
"ccov648"
"thanVitha@2015"
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path
"$DirectoryToSaveTo"
)) #create it if not existing
New-Item
-type directory | out-null
#PowerShell is made available to use Excel component by invoking excel.application COM object that will allow us to work with excel to add data and
that data.
#Save the initial row so it can be used later to create a border
#Counter variable for rows
$intRow = $row
#FileFormat numbers in Mac Excel
#These are the main file formats in Windows Excel
2007
-2016:
= xlOpenXMLWorkbook (without macro's in
-2013
, xlsx)
52
= xlOpenXMLWorkbookMacroEnabled (with or without macro's in
, xlsm)
50
= xlExcel
(Excel Binary Workbook in
with or without macro's, xlsb)
56
(
97
-2003
in Excel
, xls)
#def
ine the sheet name
$sheet.Name =
'DiskSpace'
$Sheet.Activate() | Out-Null
#Create a Title for the first worksheet
$row =
'Disk Space Information'
$range = $Sheet.Range(
"a1"
"h2"
$range.Merge() | Out-Null
# [Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) | select @{n=
"Name"
;e={
"$_"
}},value__
#To fetch the list of alignment values using the
above
the enumerator.
$range.VerticalAlignment =
-4160
#Give it a nice Style so it stands out
$range.Style =
'Title'
#Increment row for next set of data
$row++;$row++
$initalRow = $row
#Create a header for Disk Space Report; set each cell to Bold and add a background color
#Increment Row and reset Column back to first column
$critical=
$warning=
=
$good=
#Get the drives and filter out CD/DVD drives
#Add
a border for data cells have used with the VerticalAlignment property.
#[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | select @{n=
$row--
$dataRange = $Sheet.Range((
-f $initalRow),(
..
| ForEach {
$dataRange.Borders.Item($_).LineStyle =
$dataRange.Borders.Item($_).Weight =
#Auto fit everything so it looks better
$usedRange = $Sheet.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null
$sheet = $excel.Worksheets.Item(
$beginChartRow = $Row
$endChartRow = $row
$chartRange = $Sheet.Range((
-f $beginChartRow),(
"d{0}"
-f $endChartRow))
$chart = $sheet.Shapes.AddChart().Chart
#Give it some color
).Fill.ForeColor.TintAndShade = .
34
).Fill.ForeColor.ObjectThemeColor =
).Fill.BackColor.TintAndShade = .
765
).Fill.TwoColorGradient(
$chart.SetSourceData($chartRange)
$chart.HasTitle = $True
$chart.ApplyLayout(
69
$chart.ChartTitle.Text =
"Disk Space Report"
$chart.ChartStyle =
26
$chart.PlotVisibleOnly = $False
$chart.SeriesCollection(
).DataLabels().ShowValue = $True
).DataLabels().Separator = (
"{0}"
-f [char]
10
).DataLabels().Position =
#Critical
).Points(
).Format.Fill.ForeColor.RGB =
255
#Warning
65535
#Low
265535
#Good
5287936
#Hide the data
#$chartRange.EntireRow.Hidden = $True
'DiskInformation'
$filename =
"$DirectoryToSaveTo$filename.xlsx"
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$Excel.Saved = $True
$Excel.Close()