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

SSAS – Automatic Inventory Generation – 24 Columns – HTML Attachment

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage. This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy to forget that the inventory has to be updated as well. For this reason, I like to have a script that creates the inventory, rather than have to keep a manual log of changes as they occur. This can take into many forms, comments and suggestions are most welcome. 

PowerShell allows me to do just that. PowerShell makes it easy to issue WMI (Windows Management Instrumentation) and AMO’s to pull the required details.
For example, we can keep track of the Name, the operating system name, version, free physical memory, service pack information and other configuration details.
Powershell offers a simple way of gathering a complete inventory of your physical servers.

The included columns are as follows

1. Name
2. ConnectionString
3. Domain Name
4. OS
5. CPU
6. TotalRAM
7. FreeRAM
8. UsedRAM
9. Version

 Copy and Paste the below code c:\PowerSQL\SSASInventory.PS1


#Change the First set of code as per your configuration details


#Multiple email recipients can be added using , for eg :-#Emlst=”dheim@powersql.com,jayaram@powerSQL.com”




#List of SSAS Server Instances here


# Before we do anything else, are we likely to be able to save the file?. if the directory doesn’t exist, then create it

if (!(Test-Path -path “$DirectoryToSaveTo”)) #create it if not existing


New-Item “$DirectoryToSaveTo” -type directory | out-null


$filename = “$DirectoryToSaveTo$filename.htm”

$ServerInventory = $filename

New-Item -ItemType file $ServerInventory -Force

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>Server 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  “table-layout:fixed;”

add-content $fileName  “}”

add-content $fileName  “–>”

add-content $fileName  “</style>”

Add-Content $fileName “</head>”

Add-Content $fileName “<body>”


# Function to write the HTML Header to the file

Function writeTableHeader



Add-Content $fileName “<tr bgcolor=#CCCCCC>”

Add-Content $fileName “<td nowrap>Name</td>”

Add-Content $fileName “<td nowrap>ConnectionString</td>”

Add-Content $fileName “<td nowrap>Domain Name</td>”

Add-Content $fileName “<td nowrap>OS</td>”

Add-Content $fileName “<td nowrap>CPU</td>”

Add-Content $fileName “<td nowrap>TotalRAM</td>”

Add-Content $fileName “<td nowrap>FreeRAM</td>”

Add-Content $fileName “<td nowrap>UsedRAM</td>”

Add-Content $fileName “<td nowrap>SQLServer</td>”

Add-Content $fileName “<td nowrap>Edition</td>”

Add-Content $fileName “<td nowrap>EditionID</td>”

Add-Content $fileName “<td nowrap>ProductLEvel</td>”

Add-Content $fileName “<td nowrap>ProductName</td>”

Add-Content $fileName “<td nowrap>Isloaded</td>”

Add-Content $fileName “<td nowrap>DataDir</td>”

Add-Content $fileName “<td nowrap>Tempdir</td>”

Add-Content $fileName “<td nowrap>LogDir</td>”

Add-Content $fileName “<td nowrap>BackupDir</td>”

Add-Content $fileName “<td nowrap>CubeDatabaseCount</td>”

Add-Content $fileName “<td nowrap>CubeDatabaseList</td>”

Add-Content $fileName “<td nowrap>CreatedTimestmp</td>”

Add-Content $fileName “<td nowrap>LastSchemaUpdate</td>”

Add-Content $fileName “<td nowrap>Uptime</td>”

Add-Content $fileName “<td nowrap>IPAddress</td>”

Add-Content $fileName “</tr>”


Function writeHtmlFooter



Add-Content $fileName “</body>”

Add-Content $fileName “</html>”


Function writeServerInfo



Add-Content $fileName “<tr>”

Add-Content $fileName “<td nowrap>$name</td>”

Add-Content $fileName “<td nowrap>$ConnectionString</td>”

Add-Content $fileName “<td nowrap>$FQDN</td>”

Add-Content $fileName “<td nowrap>$OS</td>”

Add-Content $fileName “<td nowrap>$CPU</td>”

Add-Content $fileName “<td nowrap>$TotalRAM</td>”

Add-Content $fileName “<td nowrap>$FreeRAM</td>”

Add-Content $fileName “<td nowrap>$UsedRAM</td>”

Add-Content $fileName “<td nowrap>$SQLServer</td>”

Add-Content $fileName “<td nowrap>$Edition</td>”

Add-Content $fileName “<td nowrap>$EditionID</td>”

Add-Content $fileName “<td nowrap>$ProductLEvel</td>”

Add-Content $fileName “<td nowrap>$ProductName</td>”

Add-Content $fileName “<td nowrap>$Isloaded</td>”

Add-Content $fileName “<td nowrap>$DataDir</td>”

Add-Content $fileName “<td nowrap>$tempdir</td>”

Add-Content $fileName “<td nowrap>$LogDir</td>”

Add-Content $fileName “<td nowrap>$BackupDir</td>”

Add-Content $fileName “<td nowrap>$a</td>”

Add-Content $fileName “<td nowrap>$Result1</td>”

Add-Content $fileName “<td nowrap>$CreatedTimestamp</td>”

Add-Content $fileName “<td nowrap>$LastSchemaUpdate</td>”

Add-Content $fileName “<td nowrap>$Uptime</td>”

Add-Content $fileName “<td nowrap>$IPAddress</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 $ServerInventory

Add-Content $ServerInventory “<table width=’100%’><tbody>”

Add-Content $ServerInventory “<tr bgcolor=’#CCCCCC’>”

Add-Content $ServerInventory “<td width=’100%’ height=’25′ align=’center’ colSpan=33><font face=’tahoma’ color=’#003399′ size=’2′><strong> Inventory Report</strong></font></td>”

Add-Content $ServerInventory “</tr>”

writeTableHeader $ServerInventory

foreach ($instanceName in Get-Content “$FilePath”)


#Test for unsuccessful Connection

if(!(Test-Connection -ComputerName $instanceName -Count 5 -ea 0))


Write-Warning ‘could not able to connect $instanceName’





#Load AMO

$loadInfo = [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null

[Microsoft.AnalysisServices.Server]$server = New-Object Microsoft.AnalysisServices.Server


$s=$server|select name,ConnectionString,Version,Edition,EditionID,ProductLEvel,ProductName,Isloaded,CreatedTimestamp,LastSchemaUpdate

#String Cancatenation – Databases are listed in a concatenated in a single string


$db1=$db|select name


ForEach($db2 in $db1)


Increment $global:a


$Result =$Result1+$db2.name+’,’



#Use of Global Variable


function increment {



$dataDir=$server.serverproperties|select name, value|where {$_.name -like “DataDir”}

$tempDir=$server.serverproperties|select name, value|where {$_.name -like “TempDir”}

$LogDir=$server.serverproperties|select name, value|where {$_.name -like “LogDir”}

$BackupDir=$server.serverproperties|select name, value|where {$_.name -like “BackupDir”}

if ($tempDir.value -eq ”)








#Function to Find HostUpTime

Function Get-HostUptime {

param ([string]$ComputerName)

$Uptime = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ComputerName

$LastBootUpTime = $Uptime.ConvertToDateTime($Uptime.LastBootUpTime)

$Time = (Get-Date) – $LastBootUpTime

Return ‘{0:00} Days, {1:00} Hours, {2:00} Minutes, {3:00} Seconds’ -f $Time.Days, $Time.Hours, $Time.Minutes, $Time.Seconds


#Function to Find CPUs

Function Get-CPUs {

param ($server)

$processors = get-wmiobject -computername $server win32_processor

$cpuSpeed = ((get-wmiobject -computername $server Win32_Processor).MaxClockSpeed)/1000


if (@($processors)[0].NumberOfCores)


$cores = @($processors).count * @($processors)[0].NumberOfCores




$cores = @($processors).count


$sockets = @(@($processors) |

% {$_.SocketDesignation} |

select-object -unique).count;

“Cores: $cores, Sockets: $sockets Speed: $cpuSpeed”;


$OS = (Get-WmiObject Win32_OperatingSystem -computername $servername).caption

$SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $servername | Select-Object Name, TotalVisibleMemorySize, FreePhysicalMemory

$TotalRAM = $SystemInfo.TotalVisibleMemorySize/1MB

$FreeRAM = $SystemInfo.FreePhysicalMemory/1MB

$UsedRAM = $TotalRAM – $FreeRAM

$RAMPercentFree = ($FreeRAM / $TotalRAM) * 100

$TotalRAM = [Math]::Round($TotalRAM, 2)

$FreeRAM = [Math]::Round($FreeRAM, 2)

$UsedRAM = [Math]::Round($UsedRAM, 2)

$RAMPercentFree = [Math]::Round($RAMPercentFree, 2)




$cpu=Get-CPUs $ServerName



















$Uptime=Get-HostUptime $ServerName

#Find DomainName & IPAddress


$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $ServerName | ? {$_.IPEnabled}).ipaddress

if ($s.version -like ’8*’)


$SQLServer=’SQL SERVER 2000′


elseif ($s.version -like ’9*’)


$SQLServer=’SQL SERVER 2005′


elseif ($s.version -like ’10.0*’)


$SQLServer=’SQL SERVER 2008′


elseif ($s.version -like ’10.5*’)


$SQLServer=’SQL SERVER 2008 R2′


elseif ($s.version -like ’11*’)


$SQLServer=’SQL SERVER 2012′






write-host $name $ConnectionString $FQDN $OS $CPU $TotalRAM $FreeRAM $UsedRAM $SQLServer $Edition $EditionID $ProductLEvel $ProductName $Isloaded $DataDir $tempdir $LogDir $BackupDir $a $Result1 $CreatedTimestamp $LastSchemaUpdate $Uptime $IPAddress

writeServerInfo $ServerInventory $name $ConnectionString $FQDN $OS $CPU $TotalRAM $FreeRAM $UsedRAM $SQLServer $Edition $EditionID $ProductLEvel $ProductName $Isloaded $DataDir $tempdir $LogDir $BackupDir $a $Result1 $CreatedTimestamp $LastSchemaUpdate $Uptime $IPAddress



#Close of Else part

writeHtmlFooter $ServerInventory

# Function to Send Email Attachment

Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)


#initate message

$email = New-Object System.Net.Mail.MailMessage

$email.From = $emailFrom


$email.Subject = $subject

$email.Body = $body

# initiate email attachment

$emailAttach = New-Object System.Net.Mail.Attachment $filePath


#initiate sending email

$smtp = new-object Net.Mail.SmtpClient($smtpServer)



#Prepare Body of an Email – Formatted


$str2=’Auto Generated Email, Do Not Reply!!’

$str3=’Thanks, ‘

$str3=’DBA Team’

$subject=”$str1 `r`n`n $str2 `r`n $str4 `r`n $str3″

#Call Function

SendEmail “pjayaram@powerSQL.com” $emlst “SSAS INVENTORY” $Subject $MailServer $ServerInventory


You can download the code here Code- SSAS Inventory

Thanks for reading. More to come……………………

Output -


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...