Posted in : Microsoft, System Center Av Tobias Sandberg Översätt med Google ⟶
7 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
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