SCCM/MECM Basic SQL Queries: -

 ******************Devices with OS details********************************

Select vr.Netbios_Name0,VGS.caption0 from V_GS_OPERTING_SYSTEM VGS

inner join v_r_system vr on vr.resourceid =vgs.resourceid

******* Device with LastHW, Last SW, Lastpolicyrequest and ClientActive Status***

select distinct vr. Netbios_Name0, vch. LastHw, vch.LastSW, vch.lastpolicyrequest,c=vch.clientactivestatus from v_ch_clienthealth vch

Inner join v_R_System vr on vr.resourceid=vch.machineid

**************ARP report********************************************

SELECT  

    VR.Name0 AS 'Computer Name',

    ar.DisplayName0 AS 'Application Name',

    ar.Version0 AS 'Version',

    ar.Publisher0 AS 'Publisher',

    ar.InstallDate0 AS 'Install Date'

FROM 

    v_R_System AS VR

INNER JOIN  v_Add_Remove_Programs AS ar  ON SYS.ResourceID = ar.ResourceID

*******************ARP report with Install source file details. ********

select VR.netbios_Name0, VR.Operating_System_Name_ando, VGS.ARPDisplayName0, vgs.ProductVersion0, vgs. InstallSource0, vgs.LocalPackage0 from v_R_System VR

Inner join v_GS_INSTALLED_SOFTWARE VGS on VGS.ResourceID=VR.ResourceID

Where vgs.ARPDisplayName0 like '%Enter the AppName%

**Application group Apps details**

select vapp.DisplayName, vapp.AdminComments, vapp.Manufacturer, vapp.SoftwareVersion, vapp.ObjectTypeID from v_applications vapp where vapp.ObjectTypeID='224'

*****Application group apps details***

select from v_AppGroupDisplayProperties -- It will show application group apps details

**Application group reference Apps details**

select fn.DisplayName, fn.Manufacturer, fn.SoftwareVersion, fn.NumberOfApplicationGroups, fn.NumberOfDeployments, fn. NumberOfDependedDTs, fn.NumberOfDevicesWithApp, fn.DateCreated, fn.DateLastModified, fn.LastModifiedBy, fn.CreatedBy from fn_ListLatestApplicationCIs(1033) fn 

where fn.NumberOfApplicationGroups >=1