Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...