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

Lazy sysadmins last week:

Lazy sysadmins after WannaCry got them:

If you want to read about the nitty gritty fun of the WannaCry outbreak, check out Troy Hunt’s blog: https://www.troyhunt.com/everything-you-need-to-know-about-the-wannacrypt-ransomware/

Patch your stuff!  No excuses.