You can find lots of these lists on various blogs, but I wanted to share some of the more useful non-standard queries I use.
Quick and dirty list of clients to show primary user and OS:
select SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonTimestamp, SMS_R_System.operatingSystem, SMS_G_System_COMPUTER_SYSTEM.SystemType from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
Bitlocker – Unencrypted Clients:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_ENCRYPTABLE_VOLUME on SMS_G_System_ENCRYPTABLE_VOLUME.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ENCRYPTABLE_VOLUME.DriveLetter = “C:” and SMS_G_System_ENCRYPTABLE_VOLUME.ProtectionStatus = 0
Bitlocker – Encrypted Clients:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_ENCRYPTABLE_VOLUME on SMS_G_System_ENCRYPTABLE_VOLUME.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ENCRYPTABLE_VOLUME.DriveLetter = “C:” and SMS_G_System_ENCRYPTABLE_VOLUME.ProtectionStatus = 1
Bitlocker – TPM Info for Unencrypted Machines:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_TPM.* from SMS_R_System inner join SMS_G_System_ENCRYPTABLE_VOLUME on SMS_G_System_ENCRYPTABLE_VOLUME.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_TPM on SMS_G_System_TPM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ENCRYPTABLE_VOLUME.DriveLetter = “C:” and SMS_G_System_ENCRYPTABLE_VOLUME.ProtectionStatus = 0
Hardware – Desktops:
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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (“3″,”4″,”6″,”7”)
Hardware – Laptops:
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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (‘8’, ‘9’, ’10’, ’11’, ’12’, ’14’, ’18’, ’21’)
Hardware – Microsoft Surfaces:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName 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.Model like “%Surface%”
Hardware – Physical Servers:
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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (’17’, ’22’, ’23’, ’28’)
Hardware – Virtual Machines:
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.Model like “%Virtual%”
Installed Role – Domain Controller:
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.Roles LIKE “%Domain_Controller%”
Installed Role – Hyper-V:
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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SERVICE.DisplayName like “Hyper-V Virtual Machine Management”
Preventative – Clients with <10gb Free Space
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_LOGICAL_DISK.FreeSpace from SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId where SMS_G_System_LOGICAL_DISK.DeviceID = “C:” and SMS_G_System_LOGICAL_DISK.FreeSpace <= 10000
Preventative – Predicted Hard Drive Failure:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName
from SMS_R_System
inner join SMS_G_System_DISK on SMS_G_System_DISK.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_DISK.Status = “Pred Fail” order by SMS_R_System.Name DESC
Preventative – Reboot Pending\Required
select SMS_R_SYSTEM.Name, SMS_R_System.LastLogonUserName from SMS_R_System AS SMS_R_System
inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid
where c.LastEnforcementMessageID = 9