Below listed the basic reports to display computers with particular add and remove application installed on computers.
Report to list all the computers which have particular software/application installed ?
SELECT
c.Name0,
a.DisplayName0
FROM
v_GS_ADD_REMOVE_PROGRAMS a,
v_R_System c
WHERE a.ResourceID = c.ResourceID
AND a.DisplayName0 like ‘%Adobe acrobat%‘
GROUP BY
c.Name0,
a.DisplayName0
ORDER BY c.name0
You can also add the prompt value to ask for application name as well.
Report to List all add and remove programs on Particular computer excludes software updates ?
SELECT A.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B
WHERE A.ResourceID = B.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’
AND Name0 = @computer
GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
ORDER BY A.DisplayName0
Prompt for creation @ computer:
select Name0 from v_GS_COMPUTER_SYSTEM
You can also apply the same report on to collection by simply adding prompt value
select b.name0 [computer Name],COUNT(DisplayName0)[ARP Count] from v_GS_ADD_REMOVE_PROGRAMS a,v_GS_COMPUTER_SYSTEM B,v_FullCollectionMembership C
where B.ResourceID=a.ResourceID and
a.ResourceID=C.ResourceID
and A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’
AND c.CollectionID = @Collection
group by B.Name0
SELECT b.Name0,a.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
WHERE A.ResourceID = B.ResourceID
AND A.ResourceID = C.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’
AND CollectionID =@Collection
GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0,B.Name0
ORDER BY b.name0
Prompt for collection:
SELECT CollectionID, Name FROM v_Collection
SCCM report to list all the computers which doesnt have particular application/software installed onto it ?
Query Report which display Computers without/doesn’t have specific software in Add Remove Programs for a given collection:
Select Distinct
sys.Netbios_Name0,
sys.User_Domain0,
sys.User_Name0
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
sys.ResourceID not in (select sys.ResourceID
from
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where
DisplayName0 = @displayname)
Prompt for Displayname:
select DisplayName0 from v_Add_Remove_Programs
