Blog Post

PowerShell – SSRS Inventory – Automatic – Excel File – EMAIL


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.

Download the file and save as SSRSInventory.PS1.

It has five mandatory parameters

  1. InputFileName – Text File contains a list of SQL 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 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.

Note: The code is tested on PowerShell 2.0 with 2005,2008, 2008 R2 SQL SSRS instances

The Excel sheet comprises following columns

  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
Function Call:-
PS C:\Blog> .\SSRSInventory.ps1 -InputFileName C:\server.txt -DirectoryToSaveTo C:\ -To -From -SMTP
 Download :- SSRSInventory