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

        WHERE

       

        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 = "" # List of users to email your report to (separate by comma)

$fromemail = ""

$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

}