Wednesday, March 4, 2015

SCCM report for Exchange Server Versions

Below report will provide information of;
      - Host Name
      - Operating System
      - Exchange Product
      - Publisher of the product
      - Product Version
      - Product Installed date
****************************************************************************************************
SELECT DISTINCT
                      v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS [Product Name],
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher END AS Publisher,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion END AS Version,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 IS NULL)
                      THEN 'Unknown' ELSE CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 AS varchar) END AS [Install Date]
FROM         v_GS_INSTALLED_SOFTWARE_CATEGORIZED INNER JOIN
                      v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE     (v_GS_OPERATING_SYSTEM.Caption0 LIKE '%server%') AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%exchange%') AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%commvault%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Signatures%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%updates%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Plug-in%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Monitor%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Connector%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'% Language%'))
ORDER BY [Computer Name], [Product Name], Publisher, Version


*************************************************************************************************
More SCCM custom reports can be found here

No comments:

Post a Comment