Tuesday, October 8, 2019

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%'



No comments: