SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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




$Inventory = “Inventory.htm”

New-Item -ItemType file $Inventory -Force

# Function to write the HTML Header to the file
Function writeHtmlHeader
$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
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
Add-Content $fileName “</body>”
Add-Content $fileName “</html>”

Function writeDiskInfo
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
$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)
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



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.


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

Loading comments...