Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating