maxwell.2k19
Member
- Messages
- 8
- Reaction score
- 0
- Points
- 1
Hi All,
I need to generate two reports,one report only for the applications installed on all my servers which should exclude windows updates, Other report is for both applications and windows updates installed on all my servers.
i have tried from default reporting "Count of all instances of software registered with Add or remove programs", but it is not helping me, i am trying below queries but i am getting errors.my requirement is to get in below format
Server Name, OS Version, Application Name, Publisher, Version, CollectionID, Experts help me on t his
Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID = 'CollectionID'
ORDER By SYS.Netbios_Name0, SP.ProductName, SP.ProductVersion
SELECT A.DisplayName0, A.Version0, Count(*) AS 'Count'
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 %'
AND CollectionID = @collection
GROUP BY A.DisplayName0, A.Version0
ORDER BY A.DisplayName0
I need to generate two reports,one report only for the applications installed on all my servers which should exclude windows updates, Other report is for both applications and windows updates installed on all my servers.
i have tried from default reporting "Count of all instances of software registered with Add or remove programs", but it is not helping me, i am trying below queries but i am getting errors.my requirement is to get in below format
Server Name, OS Version, Application Name, Publisher, Version, CollectionID, Experts help me on t his
Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID = 'CollectionID'
ORDER By SYS.Netbios_Name0, SP.ProductName, SP.ProductVersion
SELECT A.DisplayName0, A.Version0, Count(*) AS 'Count'
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 %'
AND CollectionID = @collection
GROUP BY A.DisplayName0, A.Version0
ORDER BY A.DisplayName0
