다음을 통해 공유

SSAS inventory: automatic gathering of server details (HTML/XLS)

  • 아티클

Inventory 

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. 

Inventory capturing using AMO and WMI objects using Powershell

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.

Pre-requisites -

  1. Windows PowerShell 2.0 must be installed
  2. Permission to access all SSAS instances
  3. Permission to create a outputFile

Windows PowerShell 2.0 is installed by default on newer versions of the Windows operating systems.You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically. The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features, including the SQLASCmdlets module and SQLAS provider used for navigating the Analysis Services 

object hierarchy.

Parameters -

The Script has three Mandatory Parameters

  1. InputFileName - Text File contains a list of SSAS Servers  
  2. DirectoryToSave - Folder where you want to store the file
  3. OutputFileName - Name of the file( extension is not required

Included Column Information -

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

SSAS Inventory  - HTML Format

http://gallery.technet.microsoft.com/Powershell-Automatic-SSAS-85ef5b69

SSAS  Inventory - Excel Format

http://gallery.technet.microsoft.com/Powershell-Automatic-SSAS-db510158

How to Send it as an e-email attachment

http://gallery.technet.microsoft.com/PowerShell-SQL-Inventory-e9b92dac