Configuration Manager with PowerShell and WQL

This post provides an example of how we can use Configuration Manager with PowerShell and WQL.

We often can’t obtain all the information we require form native configuration manager PowerShell cmdlets, and in such instances we can revert to using a WQL query.

In the below example, we join a few tables to obtain a device OU, free disk space, operating system, last hardware scan date, manufacturer and model.

$WQLquery = @"
select OS.Caption,S.Name,S.SystemOUName,LD.FreeSpace,WS.LastHardwareScan,CS.Manufacturer,CS.Model
from SMS_R_System AS S
inner join SMS_G_System_OPERATING_SYSTEM AS OS on OS.ResourceId = S.ResourceId 
inner join SMS_G_System_LOGICAL_DISK AS LD on LD.ResourceId = S.ResourceId
inner join SMS_G_System_WORKSTATION_STATUS AS WS on WS.ResourceId = S.ResourceId
inner join SMS_G_System_COMPUTER_SYSTEM AS CS on CS.ResourceId = S.ResourceId
where S.Name = '$assetName'
"@

$allResults = Get-WmiObject -Query $WQLquery -ComputerName $configManagerServer -Namespace "root\sms\site_$configManagerSiteCode"

foreach($result in $allResults) {

    $name = $result.S.Name
    $ou = $result.S.SystemOUName
    if ($null -ne $ou) {
        $ou = $ou[$ou.Count-1]
    }
    $freeBytes = $result.LD.Freespace #in MB
    $gb = [math]::Round(($freeBytes / 1024),2) #convert to GB
    $os = $result.OS.Caption
    $lhs = $result.WS.LastHardwareScan
    if ($null -ne $lhs) {
        $lhs = [System.Management.ManagementDateTimeConverter]::ToDateTime($lhs).toString("dd/MM/yyyy")
    }
    $man = $result.CS.Manufacturer
    $mod = $result.CS.Model

    write-host $name
    write-host $ou
    write-host $gb
    write-host $os
    write-host $lhs
    write-host $man
    write-host $mod

}