WMI Queries

Uninstallation string :-

select SMS_R_System.Name, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_INSTALLED_SOFTWARE.UninstallString,
SMS_R_System.IPAddresses, SMS_R_System.AgentTime,
SMS_R_System.LastLogonTimestamp
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 "%Visio Standard%"
or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Premium%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Professional%"

list of machine have Office installed :-

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
where SMS_R_System.ResourceId in (select SMS_R_System.ResourceID 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 "Microsoft office Professional Plus 2010" or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office standard 2010")
and SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1


list of machine have Office 2016 installed :-

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName,
SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate 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 "%office%2016%"


SCCM Client active machines :-

select distinct *  from  SMS_R_System where SMS_R_System.ClientType = 1

SCCM Client inactive since 90 Days:-

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


OS Report:

select SMS_G_System_OPERATING_SYSTEM.OSLanguage, SMS_G_System_OPERATING_SYSTEM.CountryCode,
 SMS_G_System_OPERATING_SYSTEM.InstallDate, SMS_G_System_OPERATING_SYSTEM.Locale,
 SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.LastLogonUserName,
SMS_R_System.LastLogonUserDomain, SMS_R_System.Name from  SMS_R_System
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_OPERATING_SYSTEM.OSLanguage
where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90)
and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select Name from SMS_R_System
where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName = "Heartbeat Discovery"))