PowerShell -Database Server Disk Space Checking

时间:2022-09-24 11:08:53

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 {
 width: 600px;
 padding: 0;
 margin: 0;
 border-collapse: collapse;
 }
 
 caption {
 padding: 0 0 5px 0;
 width: 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
}