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

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
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