SCCM SQL query to find the application installed

SCCM SQL query to find the application installed

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0,

, r.Full_Domain_Name0, vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%'

SQL Query for application installed with users:-

select r.netbios_name0, r.User_name0, vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0 from v_R_System r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Office 365%' and r.User_name0 in ('Lav.kush',’abishek.c’)

SQL Query for application installed with Devices:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%' and r.Netbios_Name0 in ('device1’, 'device2’, 'device2’)

SQL Query for application installed with Domain:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%' and r.Full_Domain_Name0 like '%INDIA%'

SQL Query for all application installed in all system: -

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

SQL Query for  all application installed for all devices in a Collection id: -

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 = 'Collection id'

ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion

SQL Query for  all application installed for all virtual machine:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where r.Netbios_Name0 like '%VM%' and vgs.ProductName0 like '%adobe%'