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
10.Edition
11.EditionID
12.ProductLEvel
13.ProductName
14.Isloaded
15.DataDir
16.Tempdir
17.LogDir
18.BackupDir
19.CubeDatabaseCount
20.CubeDatabaseList
21.CreatedTimestmp
22.LastSchemaUpdate
23.Uptime
24.IPAddress

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

******************************************************************************

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

$MailServer=’aqmail.powersql.com’

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

$Emlst=”jayaram@powersql.com”

$DirectoryToSaveTo=’C:\PowerSQL\’

$Filename=’SSASInventory’

#List of SSAS Server Instances here

$FilePath=”C:\PowerSQL\SSASList.txt”

# 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

{

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

{

param($fileName)

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

{

param($fileName)

Add-Content $fileName “</body>”

Add-Content $fileName “</html>”

}

Function writeServerInfo

{

param($fileName,$name,$ConnectionString,$FQDN,$OS,$CPU,$TotalRAM,$FreeRAM,$UsedRAM,$SQLServer,$Edition,$EditionID,$ProductLEvel,$ProductName,$Isloaded,$DataDir,$tempdir,$LogDir,$BackupDirvalue,$a,$Result1,$CreatedTimestamp,$LastSchemaUpdate,$Uptime,$IPAddress)

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

{

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 $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’

}

else

{

$ServerName=$instanceName

#Load AMO

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

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

$server.connect($ServerName)

$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

$db=$Server.databases

$db1=$db|select name

$result1=”

ForEach($db2 in $db1)

{

Increment $global:a

$result=”

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

$Result1=$Result

}

#Use of Global Variable

$global:a=0

function increment {

$global:a++

}

$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 ”)

{

$tempDir=’NA’

}

else

{

$tempdir=$tempDir.value

}

#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

$cores=0

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

{

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

}

else

{

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

$name=$s.name

$ConnectionString=$s.ConnectionString

#$OS

$cpu=Get-CPUs $ServerName

#$CPU

#$TotalRAM

#$FreeRAM

#$UsedRAM

#$SQLServer

$Edition=$s.Edition

$EditionID=$s.EditionID

$ProductLEvel=$s.ProductLEvel

$ProductName=$s.ProductName

$Isloaded=$s.Isloaded

$DataDir=$DataDir.value

$DataDir=$DataDir

$LogDir=$LogDir.value

$BackupDir=$BackupDir.value

#$a

#$Result1

$CreatedTimestamp=$s.CreatedTimestamp

$LastSchemaUpdate=$s.LastSchemaUpdate

$Uptime=Get-HostUptime $ServerName

#Find DomainName & IPAddress

$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name

$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′

}

else

{

$SQLServer=’Invalid’

}

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.To.Add($emailTo)

$email.Subject = $subject

$email.Body = $body

# initiate email attachment

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

$email.Attachments.Add($emailAttach)

#initiate sending email

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

$smtp.Send($email)

}

#Prepare Body of an Email – Formatted

$str1=’SSAS INVENTORY – COMPLETE DETAILS’

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

Image

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 SSAS and tagged , , , , , , , , . Bookmark the permalink.

One Response to SSAS – Automatic Inventory Generation – 24 Columns – HTML Attachment

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