Posted by on December 22, 2013

Out-of-the-box reporting in SCCM 2012 can handle most reporting needs, but usability suffers when the application in question is deployed across the enterprise environment, and in various patch levels.

I recently solved a specific reporting need for Adobe Acrobat by creating some custom reports to produce a list of workstations based on the selected version.

SCCM Custom Report - Version Dropdown List

SCCM Custom Report – Version Dropdown List

The version dropdown list is driven by the following SQL query:

SELECT DISTINCT v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductVersion0
FROM dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED
WHERE (dbo.V_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE 'Adobe Acrobat XI Pro%'
AND dbo.V_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher LIKE 'Adobe%'
AND dbo.V_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductVersionO LIKE '11%')
ORDER BY ProductVersion0 ASC

The report shows the workstation name, along with the associated user name, account, and location (information which is missing on the standard software reports). Here is the SQL driving the body of the report:

IF OBJECT_ID(’tenpdb..#TempTable9’) IS NOT NULL
DROP TABLE #TempTable9;

SELECT Resourced, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductVersion0
INTO [#TempTable9]
FROM dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED
WHERE (dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE 'Adobe Acrobat XI Pro%'
AND dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher LIKE ‘Adobe%’)

SELECT SYS.Netbios_Name0 AS WKID,
USERS.UserName0 AS [User ID],
USERS.Full_User_Name0 AS [User Name],
SYS.AD_Site_Name0 AS [Site Name],
AID.NormalizedPublisher AS [Vendor Name],
AID.NormalizedName AS [Product Name],
AID.ProductVersion0 AS [Product Version]
FROM [#TempTable9] AS AID
INNER JOIN dbo.v_R_System_Valid SYS ON aid.ResourcelD = SYS.ResourcelD
INNER JOIN dbo.v_R_User AS USERS ON SYS.User_Name0 = USERS.User_Name0
ORDER BY WKID ASC

However, the above query can be optimized for speed and to reduce server load. Since the report will already have a version number parameter (appropriately named “Version” in this example) in place, let’s reduce the dataset in the query by adding another WHERE condition:

WHERE (dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE 'Adobe Acrobat XI Pro%'
AND dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher LIKE 'Adobe%'
AND dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductVersion0 = @Version)

The alternative would be to keep the query as-is and apply a filter on the entire dataset (not recommended), shown here for illustration only:

Report Builder - Apply Dataset Filter

Report Builder – Apply Dataset Filter

Posted in: SCCM, SSRS, Technical

Comments

Be the first to comment.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: