sqlpowershell.files.wordpress.com

Powershell offers a simple way of gathering a complete inventory of your physical serversNameConnectionStringDomain NameOSCPUTotalRAMFreeRAMUsedRAMSQLServerEditionEditionIDProductLEvelProductNameIsloadedDataDirTempdirLogDirBackupDirCubeDatabaseCountCubeDatabaseListCreatedTimestmpLastSchemaUpdateUptimeIPAddressCopy and Paste the below code c:\PowerSQL\SSASInventory.PS1******************************************************************************#Change the First set of code as per your configuration details$MailServer='aqmail.'#Multiple email recipients can be added using , for eg :-#Emlst="dheim@,jayaram@"$Emlst="jayaram@"$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 itif (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing { New-Item "$DirectoryToSaveTo" -type directory | out-null }$filename = "$DirectoryToSaveTo$filename.htm" $ServerInventory = $filenameNew-Item -ItemType file $ServerInventory -ForceFunction 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 "</style>"Add-Content $fileName "</head>"Add-Content $fileName "<body>"}# Function to write the HTML Header to the fileFunction 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 .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=0function 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 HostUpTimeFunction Get-HostUptime {param ([string]$ComputerName)$Uptime = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ComputerName$LastBootUpTime = $Uptime.ConvertToDateTime($Uptime.LastBootUpTime)$Time = (Get-Date) - $LastBootUpTimeReturn '{0:00} Days, {1:00} Hours, {2:00} Minutes, {3:00} Seconds' -f $Time.Days, $Time.Hours, $Time.Minutes, $Time.Seconds}#Function to Find CPUsFunction 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}).ipaddressif ($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 $IPAddresswriteServerInfo $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 partwriteHtmlFooter $ServerInventory# Function to Send Email AttachmentFunction sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath){#initate message$email = New-Object .Mail.MailMessage $email.From = $emailFrom$email.To.Add($emailTo)$email.Subject = $subject$email.Body = $body# initiate email attachment $emailAttach = New-Object .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='Appvion DBA Team'$subject="$str1 `r`n`n $str2 `r`n $str4 `r`n $str3"#Call Function SendEmail "pjayaram@" $emlst "SSAS INVENTORY" $Subject $MailServer $ServerInventory ................
................

In order to avoid copyright disputes, this page is only a partial summary.

To fulfill the demand for quickly locating and searching documents.

It is intelligent file search solution for home and business.

Literature Lottery

To fulfill the demand for quickly locating and searching documents.

Related download

Related searches