Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

PowerSQL – SQL Inventory Generation – Email – High Level Details

Inventory allows anyone to quickly and easily assess any environment. For few servers, answering  Inventory questions might not be so difficult.  But if you’re managing ‘N’ number of SQL Server instances then its going to be tedious job and you’re going to need an organized system for tracking this kind of information. Simplest and an easiest way to pull high level sql instance details are given below. There are two ways to retrieve the instance details

First Method – The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks

C:\>SQLCMD –L [-L list servers clean output]

Second Method- You need to change Emlst(Email List) and MailServer Parameters in the below code to get formatted HTML output.

Copy and Paste the below code Inventory.PS1 and run it

************************************************************

$MailServer=”aqmail01.PowerSQL.com”

$Emlst=”pjayaram@PowerSQL.com”

$Inventory = “Inventory.htm”

New-Item -ItemType file $Inventory -Force

# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
Add-Content $fileName “<html>”
Add-Content $fileName “<head>”
Add-Content $fileName “<meta http-equiv=’Content-Type’ content=’text/html; charset=iso-8859-1′>”
Add-Content $fileName ‘<title>SQL Inventory Report </title>’
add-content $fileName ‘<STYLE TYPE=”text/css”>’
add-content $fileName “<!–”
add-content $fileName “td {“
add-content $fileName “font-family: Tahoma;”
add-content $fileName “font-size: 11px;”
add-content $fileName “border-top: 1px solid #999999;”
add-content $fileName “border-right: 1px solid #999999;”
add-content $fileName “border-bottom: 1px solid #999999;”
add-content $fileName “border-left: 1px solid #999999;”
add-content $fileName “padding-top: 0px;”
add-content $fileName “padding-right: 0px;”
add-content $fileName “padding-bottom: 0px;”
add-content $fileName “padding-left: 0px;”
add-content $fileName “}”
add-content $fileName “body {“
add-content $fileName “margin-left: 5px;”
add-content $fileName “margin-top: 5px;”
add-content $fileName “margin-right: 0px;”
add-content $fileName “margin-bottom: 10px;”
add-content $fileName “”
add-content $fileName “table {“
add-content $fileName “border: thin solid #000000;”
add-content $fileName “}”
add-content $fileName “–>”
add-content $fileName “</style>”
Add-Content $fileName “</head>”
Add-Content $fileName “<body>”
add-content $fileName “<table width=’100%’>”
add-content $fileName “<tr bgcolor=’#CCCCCC’>”
add-content $fileName “<td colspan=’4′ height=’25′ align=’center’>”
add-content $fileName “<font face=’tahoma’ color=’#003399′ size=’4′><strong>SQL Inventory Report – $date</strong></font>”
add-content $fileName “</td>”
add-content $fileName “</tr>”
add-content $fileName “</table>”
}

# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName “<tr bgcolor=#CCCCCC>”
Add-Content $fileName “<td width=’10%’ align=’center’>ServerName</td>”
Add-Content $fileName “<td width=’50%’ align=’center’>InstanceName</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>IsClustered</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>Version</td>”
Add-Content $fileName “</tr>”
}

Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName “</body>”
Add-Content $fileName “</html>”
}

Function writeDiskInfo
{
param($filename,$Servername,$Instance,$IsClustered,$Version)
Add-Content $fileName “<tr>”
Add-Content $fileName “<td>$servername</td>”
Add-Content $fileName “<td>$Instance</td>”
Add-Content $fileName “<td>$IsClustered</td>”
Add-Content $fileName “<td>$Version</td>”
Add-Content $fileName “</tr>”
}

Function sendEmail
{
param($from,$to,$subject,$smtphost,$htmlFileName)
[string]$receipients=”$to”
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}
writeHtmlHeader $Inventory
Add-Content $Inventory “<table width=’100%’><tbody>”
Add-Content $Inventory “<tr bgcolor=’#CCCCCC’>”
Add-Content $Inventory “<td width=’100%’ align=’center’ colSpan=4><font face=’tahoma’ color=’#003399′ size=’2′><strong> Inventory Details</strong></font></td>”
Add-Content $Inventory “</tr>”

writeTableHeader $Inventory

$dp = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()|select servername,Instancename, isclustered, version | where {$_.isclustered -notlike “” }|sort-object servername
foreach ($item in $dp)
{
if( $item.instancename -eq “”)
{
$item.instancename=”Default Instance”
}
Write-Host $item.servername $item.instancename $item.isClustered $item.Version
writeDiskInfo $Inventory $item.servername $item.instancename $item.isClustered $item.Version
}
Add-Content $Inventory “</table>”

writeHtmlFooter $Inventory
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
sendEmail PoweSQL@PowerSQL.com “$emlst” “Inventory Report – $Date” $MailServer $Inventory

Output:-

third


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...