October 27, 2016 at 10:05 pm
Hi All,
Need some help in SQL Powershell. I got the below script from the powershell community. This report works perfectly fine. This is sql powershell report which gives info abt the database files info like name,size,freespace .....
Requirement 1: In the below line I want the instance name printed instead of "Computer Info" which is hard coded. I tried putting $insance put it didnt work!
$dbfl | ConvertTo-HTML -Fragment -PreContent 'Computer Info'
Requirement 2: In the report there is a column called "Free%", my requirement is , if the "Free%"<10 then the cell background color should change to YELLOW indicating a WARNING sign whenever user sees a report.
Complete Code:
=====================
function Get-DatabaseFileSpace {
param (
[string]$Instance = "LocalHost"
)
begin {
$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
} #begin
process {
$dbfl = foreach ($db in $svr.Databases) {
$dbname = $db.Name
foreach ($fg in $db.FileGroups) {
foreach ($fl in $fg.Files) {
$dirnm = $fl.FileName | Split-Path -Parent
$filnm = $fl.FileName | Split-Path -Leaf
$fl | select Name,
@{Name="DBName"; Expression={$dbname}},
@{Name="Directory"; Expression={$dirnm}},
@{Name="FileName"; Expression={$filnm}},
@{Name="Size(MB)";Expression={$fl.Size/1024}},
@{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
@{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}} ,
@{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
@{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
} #foreach ($fl in $fg.Files)
} #foreach ($fg in $db.FileGroups)
foreach ($fl in $db.LogFiles) {
$dirnm = $fl.FileName | Split-Path -Parent
$filnm = $fl.FileName | Split-Path -Leaf
$fl | select Name,
@{Name="DBName"; Expression={$dbname}},
@{Name="Directory"; Expression={$dirnm}},
@{Name="FileName"; Expression={$filnm}},
@{Name="Size(MB)";Expression={$fl.Size/1024}},
@{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
@{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}},
@{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
@{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
} #foreach ($fl in $db.LogFiles)
} #foreach ($db in $svr.Databases)
} #process
end{$dbfl} #end
} #Get-DatabaseFileSpace
$instances = "Srv1","Srv2","Srv3"
$instanceReportHTML = foreach ($instance in $instances) {
#Get the data from the instance
$dbfl = Get-DatabaseFileSpace -Instance $instance
#Convert the data to a HTML fragment and send it to $instanceReportHTML
#$dbfl | ConvertTo-HTML -Title "Report for 1" -Fragment
$dbfl | ConvertTo-HTML -Fragment -PreContent 'Computer Info'
}
# Template for HTML
$html = @"
BODY{
background-color:white;
}
TABLE{
font-family: Arial;
font-size: 12px;
width:100%;
height:75%;
border-width: 1px;
border-style: solid;
border-color: black;
border-collapse: collapse;
}
TH{
border-width: 1px;
padding: 0px;
border-style: solid;
border-color: black;
color:white;
background-color: green;
}
TD{
border-width: 1px;
padding: 0px;
border-style: solid;
border-color: black;
background-color:white
}
Database File Space Report
$instanceReportHTML
"@
$body = $html | Out-String
$EmailFrom = "xxxxx@gmail.com"
$EmailTo = "aaa@gmail.com,bbb@gmail.com"
$Subject = "Datafile size report"
$SMTPServer = "smtp.gmail.com"
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("xxxxx@gmail.com", "pwd123");
$message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $body)
$message.IsBodyHtml = $true;
$SMTPClient.Send($message)
October 28, 2016 at 2:55 am
What type does it think $instance is?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 28, 2016 at 2:55 am
Does $instance need to be in quotes? (Single or double - check to see which one allows variable expansion.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 28, 2016 at 3:03 am
As for formatting, I found using a search engine helped. My search came up with, amongst others, the following:
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply