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




About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s