FEW New queries

WMI query for application deployment for lower version: 

select distinct 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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%XXXX%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion < "10.0.0.10361"


WMI query for Workstations:

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_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.SystemRole = "Workstation"


Query to get Application not installed machines report :

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 where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName Like "XXXXX for Windows 7.50  (XXXX)%" )

WMI Query for list of machine with disk space :

select SMS_R_System.Name, SMS_G_System_DISK.Name, SMS_G_System_DISK.Size, SMS_G_System_DISK.Status from  SMS_R_System inner join SMS_G_System_DISK on SMS_G_System_DISK.ResourceId = SMS_R_System.ResourceId



WMI query for application installed machines list : 

select distinct 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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%XXXXX for Windows 7.50  (XXXX)%"


SQL query for asset report machine names with EmailID's :

select Distinct

v_r_system.netbios_name0,v_r_user.Full_User_Name0, v_r_user.User_Name0,v_r_user.Mail0,v_R_System.AD_Site_Name0,v_r_system.User_Domain0,v_r_system.Operating_System_Name_and0,

v_r_system.Last_Logon_Timestamp0
from v_r_user
join v_r_system on v_r_system .user_name0= v_r_user.User_name0

order by v_r_system.netbios_name0

Collection based application installed machines report :

Declare @CollID char(8)       
Set @CollID = 'SMSXXXXX1' 
SELECT Distinct
COMP.Name0 AS 'Machine Name',     
 COMP.UserName0 AS 'Username',
 v_R_system.User_Domain0 AS ' Domain Name',
 v_R_System.AD_Site_Name0,
ARP.Publisher0 AS 'Publisher',       
ARP.DisplayName0 AS 'Program',     
ARP.Version0 AS 'Version',       
ARP.InstallDate0 AS 'Install Date',     
 ARP.ProdID0 AS 'Product ID',
 v_R_system.Last_Logon_Timestamp0 
FROM v_Add_Remove_Programs ARP       
JOIN v_FullCollectionMembership FCM 
on ARP.ResourceID = FCM.ResourceID       
JOIN v_GS_COMPUTER_SYSTEM COMP           
on ARP.ResourceID = COMP.ResourceID
JOIN v_R_System on v_R_System.ResourceID = ARP.ResourceID     
WHERE FCM.CollectionID = @CollID           
AND ARP.DisplayName0 like '%XXXXXXX%'       
AND ARP.ProdId0 NOT LIKE '%(KB%)%'           
AND ARP.DisplayName0 NOT LIKE '%Update%'