Displaying SCCM hardware and Add Remove Program information

• James.Bowtell

  SSC Veteran

  Points: 206

  More actions

  November 30, 2017 at 8:38 am

  #324870

  Hi All,

  I've created a dataset using SCCM sql data and now trying to present the infomation using SQL Server Report Builder. What i'm trying to achieve is that when the report is generated the machines name and hardware is displayed along with all it's installed software registered in Add Remove Programs (One report per page)
  
  
SELECT
  v_GS_COMPUTER_SYSTEM.Name0 AS [v_GS_COMPUTER_SYSTEM Name0]
  ,v_GS_COMPUTER_SYSTEM.Manufacturer0
  ,v_GS_COMPUTER_SYSTEM.Model0
  ,v_GS_PC_BIOS.SerialNumber0
  ,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0
  ,v_GS_PROCESSOR.Name0 AS [v_GS_PROCESSOR Name0]
  ,v_GS_OPERATING_SYSTEM.Caption0
  ,v_Add_Remove_Programs.DisplayName0
  ,v_Add_Remove_Programs.Version0
FROM
  v_GS_OPERATING_SYSTEM
  INNER JOIN v_GS_COMPUTER_SYSTEM
    ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
  INNER JOIN v_GS_PROCESSOR
    ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PROCESSOR.ResourceID
  INNER JOIN v_Add_Remove_Programs
    ON v_Add_Remove_Programs.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
  INNER JOIN v_GS_X86_PC_MEMORY
    ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
  INNER JOIN v_GS_PC_BIOS
    ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
  INNER JOIN v_FullCollectionMembership
    ON v_FullCollectionMembership.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
  v_FullCollectionMembership.CollectionID = @CollectionID
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Update for Microsoft%'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Security Update for %'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Update for %'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Microsoft % MUI %'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Hotfix for Windows%'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Update for Office %'
  AND v_Add_Remove_Programs.DisplayName0 NOT LIKE 'Update for Outlook %'