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 -
