cls
function get-diskratio()
{
param([Parameter(Position=0, Mandatory=$true)] $DiskString
)
if (
$DiskString -eq ""){
Write-Output $null ;
}
else
{ Write-Host $DiskString.substring($DiskString.IndexOf("|")+1,$DiskString.length-$DiskString.IndexOf("|")-2)
Write-Output $DiskString.substring($DiskString.IndexOf("|")+1,$DiskString.length-$DiskString.IndexOf("|")-2)
}
}
# Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
Add-Type -AssemblyName System.Xml.Linq
#Create data table to hold the results
$dataTable = New-Object system.Data.DataTable "Results"
#Specify the column names for the data table
$col1 = New-Object system.Data.DataColumn CFree,([string])
$col2 = New-Object system.Data.DataColumn CTotalSize,([string])
$col3 = New-Object system.Data.DataColumn DFree,([string])
$col4 = New-Object system.Data.DataColumn DTotalSize,([string])
$col5 = New-Object system.Data.DataColumn EFree,([string])
$col6 = New-Object system.Data.DataColumn ETotalSize,([string])
$col7 = New-Object system.Data.DataColumn FFree,([string])
$col8 = New-Object system.Data.DataColumn FTotalSize,([string])
$col9 = New-Object system.Data.DataColumn GFree,([string])
$col10 = New-Object system.Data.DataColumn GTotalSize,([string])
$col11 = New-Object system.Data.DataColumn HFree,([string])
$col12 = New-Object system.Data.DataColumn HTotalSize,([string])
$col13 = New-Object system.Data.DataColumn QFree,([string])
$col14 = New-Object system.Data.DataColumn QTotalSize,([string])
$col15 = New-Object system.Data.DataColumn RFree,([string])
$col16 = New-Object system.Data.DataColumn RTotalSize,([string])
$col17 = New-Object system.Data.DataColumn SFree,([string])
$col18 = New-Object system.Data.DataColumn STotalSize,([string])
$col19 = New-Object system.Data.DataColumn ServerName,([string])
$col20 = New-Object system.Data.DataColumn Priority,([string])
#Add the columns to the data table
$dataTable.Columns.Add($col1)
$dataTable.Columns.Add($col2)
$dataTable.Columns.Add($col3)
$dataTable.Columns.Add($col4)
$dataTable.Columns.Add($col5)
$dataTable.Columns.Add($col6)
$dataTable.Columns.Add($col7)
$dataTable.Columns.Add($col8)
$dataTable.Columns.Add($col9)
$dataTable.Columns.Add($col10)
$dataTable.Columns.Add($col11)
$dataTable.Columns.Add($col12)
$dataTable.Columns.Add($col13)
$dataTable.Columns.Add($col14)
$dataTable.Columns.Add($col15)
$dataTable.Columns.Add($col16)
$dataTable.Columns.Add($col17)
$dataTable.Columns.Add($col18)
$dataTable.Columns.Add($col19)
$dataTable.Columns.Add($col20)
# load the server list from rtwebstatdb81
$centralserver = New-Object ("Microsoft.SqlServer.Management.Smo.Server") 'rtwebstatdb81'
$centraldb=$centralserver.Databases| Where-Object {$_.name -eq 'Performance'} |
foreach {
$dt=$_.ExecuteWithResults(" select distinct ServerName,isnull(Priority,1) Priority from dbo.ServerNames order by Priority asc ")
Foreach ($t in $dt.Tables)
{
Foreach ($r in $t.Rows)
{
$serverName = $r.Item('ServerName');
$Priority = $r.Item('Priority');
# Create sql server object
$server1 = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$connectionString = "Data Source="+$serverName+";Integrated Security=true;Initial Catalog=master;"
# the sql to catch the disk info
$QueryString = 'SET NOCOUNT ON
exec sp_configure ''show advance'',1
reconfigure
exec sp_configure ''Ole Automation Procedures'',1
reconfigure
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
DECLARE @isSqlServer2000 BIT
SELECT @isSqlServer2000 = CASE WHEN CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(15), SERVERPROPERTY(''productversion'')),
0,
CHARINDEX(''.'',
CONVERT(VARCHAR(15), SERVERPROPERTY(''productversion''))))) > 8
THEN 0
ELSE 1
END
IF @isSqlServer2000 <> 1
BEGIN
EXEC sp_configure ''show advance'', 1
RECONFIGURE
EXEC sp_configure ''Ole Automation Procedures'', 1
RECONFIGURE
END
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate ''Scripting.FileSystemObject'',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,''GetDrive'', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,''TotalSize'', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@servername AS ServerName ,
MAX(CASE WHEN drive = ''C'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS CFree ,
MAX(CASE WHEN drive = ''C'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS CTotalSize ,
MAX(CASE WHEN drive = ''D'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS DFree ,
MAX(CASE WHEN drive = ''D'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS DTotalSize ,
MAX(CASE WHEN drive = ''E'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS EFree ,
MAX(CASE WHEN drive = ''E'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS ETotalSize ,
MAX(CASE WHEN drive = ''F'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS FFree ,
MAX(CASE WHEN drive = ''F'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS FTotalSize ,
MAX(CASE WHEN drive = ''G'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS GFree ,
MAX(CASE WHEN drive = ''G'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS GTotalSize ,
MAX(CASE WHEN drive = ''H'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS HFree ,
MAX(CASE WHEN drive = ''H'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS HTotalSize ,
MAX(CASE WHEN drive = ''Q'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS QFree ,
MAX(CASE WHEN drive = ''Q'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS QTotalSize ,
MAX(CASE WHEN drive = ''R'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS RFree ,
MAX(CASE WHEN drive = ''R'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS RTotalSize ,
MAX(CASE WHEN drive = ''S'' THEN CAST(FreeSpace * 100 / 1024 AS decimal(9, 2)) / 100 END) AS SFree ,
MAX(CASE WHEN drive = ''S'' then CAST(TotalSize * 100 / 1024 AS decimal(9, 2)) / 100 END) AS STotalSize
FROM #drives
DROP TABLE #drives
IF @isSqlServer2000 <> 1
BEGIN
EXEC sp_configure ''show advance'', 1
RECONFIGURE
EXEC sp_configure ''Ole Automation Procedures'', 0
RECONFIGURE
END
'
$connection=New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString= $connectionString
$cmd=$connection.CreateCommand()
$cmd.CommandType=[System.Data.CommandType]::Text
$cmd.CommandText=$QueryString
$connection.Open()
$reader=$cmd.ExecuteReader()
while($reader.Read()){
$row = $dataTable.NewRow()
$row.CFree = "{0:N2}" -f $reader['CFree'];
$row.CTotalSize ="{0:N2}" -f $reader['CTotalSize'];
$row.DFree = "{0:N2}" -f $reader['DFree'];
$row.DTotalSize ="{0:N2}" -f $reader['DTotalSize'];
$row.EFree ="{0:N2}" -f $reader['EFree'];
$row.ETotalSize = "{0:N2}" -f $reader['ETotalSize'];
$row.FFree= "{0:N2}" -f $reader['FFree'];
$row.FTotalSize ="{0:N2}" -f $reader['FTotalSize'];
$row.GFree= "{0:N2}" -f $reader['GFree'];
$row.GTotalSize ="{0:N2}" -f $reader['GTotalSize'];
$row.HFree="{0:N2}" -f $reader['HFree'];
$row.HTotalSize ="{0:N2}" -f $reader['HTotalSize'];
$row.QFree= "{0:N2}" -f $reader['QFree'];
$row.QTotalSize ="{0:N2}" -f $reader['QTotalSize'];
$row.RFree= "{0:N2}" -f $reader['RFree'];
$row.RTotalSize ="{0:N2}" -f $reader['RTotalSize'];
$row.SFree="{0:N2}" -f $reader['SFree'] ;
$row.STotalSize ="{0:N2}" -f $reader['STotalSize'] -replace ",","";
$row.ServerName="{0:N2}" -f $reader['ServerName'];
$row.Priority=$Priority
$dataTable.Rows.Add($row)
}
$connection.Close();
}
}
}
# get the data from the result and format it into html
$tableFragment1=$dataTable |Select-Object Priority,ServerName,
@{name="CFree/Ratio";expression={$_.CFree+'GB|'+ "{0:N2}" -f ($_.CFree/$_.CTotalSize*100)+'%'}},
@{name="DFree/Ratio";expression={$_.DFree+'GB|'+ "{0:N2}" -f ($_.DFree/$_.DTotalSize*100)+'%'}},
@{name="EFree/Ratio";expression={$_.EFree+'GB|'+ "{0:N2}" -f ($_.EFree/$_.ETotalSize*100)+'%'}},
@{name="FFree/Ratio";expression={$_.FFree+'GB|'+ "{0:N2}" -f ($_.FFree/$_.FTotalSize*100)+'%'}},
@{name="GFree/Ratio";expression={$_.GFree+'GB|'+ "{0:N2}" -f ($_.GFree/$_.GTotalSize*100)+'%'}},
@{name="HFree/Ratio";expression={$_.HFree+'GB|'+ "{0:N2}" -f ($_.HFree/$_.HTotalSize*100)+'%'}},
@{name="QFree/Ratio";expression={$_.QFree+'GB|'+ "{0:N2}" -f ($_.QFree/$_.QTotalSize*100)+'%'}},
@{name="RFree/Ratio";expression={$_.RFree+'GB|'+ "{0:N2}" -f ($_.RFree/$_.RTotalSize*100)+'%'}},
@{name="SFree/Ratio";expression={$_.SFree+'GB|'+ "{0:N2}" -f ($_.SFree/$_.STotalSize*100)+'%'}}| ConvertTo-HTML
$xml = [System.Xml.Linq.XDocument]::Parse( $tableFragment1)
for ($i=2 ;$i -le 10; $i=$i+1)
{
if($i -eq 2)
{
$threhold1=8;$threhold2=10;$threhold3=15
}
else { $threhold1=8;$threhold2=12;$threhold3=18}
# Format the column based on whatever rules you have:
switch($xml.Descendants("{http://www.w3.org/1999/xhtml}td") | Where { ($_.NodesBeforeSelf() | Measure).Count -eq $i } )
{ {$threhold1 -gt (get-diskratio($_.Value)) } { $_.SetAttributeValue( "style", "color: red;font-weight:bold"); continue }
{$threhold2 -gt (get-diskratio($_.Value)) } { $_.SetAttributeValue( "style", "background: orange;"); continue }
{$threhold3 -gt (get-diskratio($_.Value))} { $_.SetAttributeValue( "style", "background: yellow;"); continue } }
}
$users = "alex.tian@morningstar.com" # List of users to email your report to (separate by comma)
$fromemail = "SqlServerDiskSpace@morningstar.com"
$server = "internalmail.morningstar.com" #enter your own SMTP server DNS name / IP address here
$subject="Database Server Disk Space Checking was Executed at "+(get-date).ToString()
# assemble the HTML for our body of the email report.
# email template
$HTMLmessage1 = @"
<font color=""black"" face=""Verdana, Arial"" size=""3"">
<u><b>Database Server Disk Space Report</b></u>
<br/>
<br/>
<style type="text/css">
/* CSS Document */
body {
font: normal 11px auto "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #4f6b72;
background: #E6EAE9;
}
a {
color: #c75f3e;
}
table {
600px;
padding: 0;
margin: 0;
border-collapse: collapse;
}
caption {
padding: 0 0 5px 0;
600px;
font: italic 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
text-align: right;
}
th {
font: bold 12px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #4f6b72;
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
border-top: 1px solid #C1DAD7;
letter-spacing: 2px;
text-align: center;
padding: 5px 4px 5px 6px;
background: #CAE8EA url(images/bg_header.jpg) no-repeat;
}
th.nobg {
border-top: 0;
border-left: 0;
border-right: 1px solid #C1DAD7;
background: none;
}
td {
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
background: #fff;
font-size:11px;
padding: 5px 4px 5px 6px;
color: #4f6b72;
}
td.alt {
background: #F5FAFA;
color: #797268;
}
th.spec {
border-left: 1px solid #C1DAD7;
border-top: 0;
background: #fff url(images/bullet1.gif) no-repeat;
font: bold 10px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
}
th.specalt {
border-left: 1px solid #C1DAD7;
border-top: 0;
background: #f5fafa url(images/bullet2.gif) no-repeat;
font: bold 10px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #797268;
}
/*---------for IE 5.x bug*/
html>body td{ font-size:13px;}
</style>
<body BGCOLOR=""white"">
$xml
</body>
"@
$regexsubject = $tableFragment1
$regex = [regex] '(?im)<td>'
# if there was any row at all, send the email
if ($regex.IsMatch($regexsubject)) {
send-mailmessage -from $fromemail -to $users -subject $subject -BodyAsHTML -body $HTMLmessage1 -priority High -smtpServer $server
}