Share via

Powershell: SQL Server inventory scripts

  • Article

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. 

It has five mandatory parameters 

  1. InputFileName - Text File contains a list of SQL/SSAS/SSRS Servers  -C:\Server.txt(Example)
  2. DirectoryToSave - Folder where you want to store the file
  3. ToID - To email Address
  4. FromID - From Email Address
  5. SMTP - SMTP Adress

Pre-requisites are

  1. Windows PowerShell 2.0 must be installed
  2. Permission to access all SQL instances
  3. Permission to create an 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.

The all three PowerShell scripts require an input file, Directory to Save and Email parameters

Details

For Example-The C:\SQLList.txt file should list of SQL instances like below         

apdbsp15

apdbsp17\CRM2011

SQL Server Inventory

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The output is comprised of following columns

  1. InstanceName
  2. ComputerNamePhysicalNetBIOS
  3. NetName
  4. OS
  5. OSVersion
  6. Platform
  7. Product
  8. edition
  9. Version
  10. VersionString
  11. ProductLevel
  12. DatabaseCount
  13. HasNullSaPassword
  14. IsCaseSensitive
  15. IsFullTextInstalled
  16. Language
  17. LoginMode
  18. Processors
  19. PhysicalMemory
  20. MaxMemory
  21. MinMemory
  22. IsSingleUser
  23. IsClustered
  24. Collation
  25. MasterDBLogPath
  26. MasterDBPath
  27. ErrorLogPath
  28. BackupDirectory
  29. DefaultLog
  30. ResourceLastUpdatetime
  31. AuditLevel
  32. DefaultFile
  33. xp_cmdshell
  34. Domain
  35. IPAddress

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

PS C:\> .\SQLServerInventory.ps1 -InputFileName C:\SQLserver.txt -DirectoryToSaveTo C:\ -To pram@abc.com -From pram@abc.com -SMTP mail.abc.com 

SSAS Inventory

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.

Analysis Management Objects (AMO) is the complete library of programmatically accessed objects that enables an application to manage a running instance

of Microsoft SQL Server Analysis Services. This section explains AMO concepts, focusing on major objects, how and when to use them, and the way they are interrelated.

The 24 columns output are listed are given below

  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

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

PSC:\.\SSASInventoryExcel.ps1 -InputFileName C:\SSASList.txt -DirectoryToSaveTo c:\-OutputFileName SSASInventoryExcel

SSRS Inventory:-

The MSReportServer_ConfigurationSetting class represents the installation and runtime parameters of a report server instance. These parameters are stored in the configuration file for the report server

The 33 columns output are listed are given below

  1. Name
  2. Domain Name
  3. OS
  4. DatabaseServerName
  5. CPU
  6. TotalRAM
  7. FreeRAM
  8. UsedRAM
  9. Version
  10. DatabaseName
  11. ExtendedProtectionLevel
  12. ExtendedProtectionScenario
  13. InstanceName
  14. SenderEmailAddress
  15. SendUsingSMTPServer
  16. ServiceName 
  17. SMTPServer 
  18. VirtualDirectoryReportManager
  19. VirtualDirectoryReportServer 
  20. IsInitialized
  21. IsReportManagerEnabled
  22. IsSharePointIntegrated 
  23. IsWebServiceEnabled 
  24. IsWindowsServiceEnabled 
  25. MachineAccountIdentity 
  26. PathName
  27. SecureConnectionLevel 
  28. WindowsServiceIdentityActual 
  29. WindowsServiceIdentityConfigured
  30. ConnectionPoolSize
  31. DatabaseQueryTimeout
  32. Uptime
  33. IPAddress

http://gallery.technet.microsoft.com/PowerShell-SSRS-Inventory-de5d2682

PS C:\> .\SSRSInventory.ps1 -InputFileName C:\SSRSList.txt -DirectoryToSaveTo C:\ -To pram@abc.com -From pram@abc.com -SMTP mail.abc.com  

References

See Also