COL_Devices_without_ClientUpdate_2012R2CU3

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SMS_ADVANCED_CLIENT_STATE on SMS_G_System_SMS_ADVANCED_CLIENT_STATE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = “x64-based PC” and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.DisplayName = “CCM Framework” and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version >= “5.00.7958.1000” and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version < “5.00.7958.1401”

COL_Devices_with_SCCM_Console

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%System Center 2012 R2 Configuration Manager Console%”

For 64-bit OS also use
or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “%Applicationname%”