Posted in : Microsoft, System Center By Tobias Sandberg Translate with Google ⟶

6 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.

View of the report in Microsoft SQL Server Report Builder.


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.

List of all the applications in your environment.


Wildcard search that overrides the application list search.


A list with the computers that have the application installed.


The view of the report run in Internet Explorer.

Tags : SCCM, SQL, SQL Reporting, SSRS

Personlig rådgivning

Vi erbjuder personlig rådgivning med författaren för 1400 SEK per timme. Anmäl ditt intresse i här så återkommer vi så snart vi kan.

Add comment

Your comment will be revised by the site if needed.