SCCM – Application installation summary report

Posted in : Microsoft, System Center Av Tobias Sandberg Översätt med Google ⟶

8 years ago

This post will describe how to create an application installation summary report in SCCM. This is a great tool to have when you’re for example doing a health check of your applications and different versions of them. The report is built in Microsoft SQL Server Report Builder and you can see the final result on the pictures down below (press on the pictures to maximize).
I will now describe the different pieces of the report so you can make it fit in your environment:
Add the following datasets to the report, just make sure it is applicable to your environment:

ApplicationCount

IF (@Wildcard IS NULL or @Wildcard = '')
Select
            Count(v_R_System.Name0) as InstalledCount,
            v_Add_Remove_Programs.DisplayName0 as 'Software Name',
            v_Add_Remove_Programs.Version0 as Version,
			v_add_remove_programs.Publisher0 as Publisher
from
            dbo.v_Add_Remove_Programs
            join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
Where
            DisplayName0 = @ApplicationName
Group by v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.Version0,v_add_remove_programs.Publisher0
Order by Version0 Desc
Else
Select
            Count(v_R_System.Name0) as InstalledCount,
            v_Add_Remove_Programs.DisplayName0 as 'Software Name',
            v_Add_Remove_Programs.Version0 as Version,
			v_add_remove_programs.Publisher0 as Publisher
from
            dbo.v_Add_Remove_Programs
            join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
Where
            DisplayName0 like @Wildcard
Group by v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.Version0,v_add_remove_programs.Publisher0
Order by Version0 Desc

ExpandComputerName

IF (@Wildcard IS NULL or @Wildcard = '')
Select
    Distinct v_r_System.Name0 as ComputerName,
    v_Add_Remove_Programs.DisplayName0 as 'Software Name',
    v_Add_Remove_Programs.Version0 as Version,
    v_add_remove_programs.Publisher0 as Publisher
from
   dbo.v_Add_Remove_Programs
   join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
Where
    DisplayName0 like @ApplicationName
Order by Computername
ELSE
Select
    Distinct v_r_System.Name0 as ComputerName,
    v_Add_Remove_Programs.DisplayName0 as 'Software Name',
    v_Add_Remove_Programs.Version0 as Version,
    v_add_remove_programs.Publisher0 as Publisher
from
   dbo.v_Add_Remove_Programs
   join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
Where
    DisplayName0 like @Wildcard
Order by Computername

ExpandApplicationName

Select
    distinct v_Add_Remove_Programs.DisplayName0 as 'Software Name'
from
   dbo.v_Add_Remove_Programs
Where not DisplayName0='' and not DisplayName0= '%driver%'
Order by 'Software Name'

Next step is to create the layout as shown in the picture below. When the layout is done put in the data from the datasets and the two expressions you can see under the picture.

In the first expression we get the application name from the list search or the wildcard search:

=IIF(Parameters!Wildcard.Value Is Nothing, Parameters!ApplicationName.Value,Parameters!Wildcard.Value)

In the second expression we join the computers within each software version result:

=Join(LookupSet(Fields!Version.Value,Fields!Version.Value, Fields!ComputerName.Value , "ExpandComputerName"),vbcrlf)

Last step is to save the report and try it out.
The report gives you a choice of choosing an application from a drop-down list (where all the applications in your environment will show up) or through a wildcard search. It will then show the selected application (and/or the specific version of your choice) in a list with parameters such as how many computers the application is installed on and the names of the computers.

Tags : SCCM, SQL, SQL Reporting, SSRS