SCCM WQL Query - List version of a specific installed application?

Question

I'm struggling to create a WQL query for SCCM as I'm really new and rarely use it in a complex manner.

My goal is to list 3 things : Computer name - Display Name ("Google Chrome") - Display Version (of that Google Chrome entry)

I'm starting with this :

$SiteCode = "XXX"
$SiteServer = "XXX"


$query = @"

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version
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 = "Google Chrome"

"@


Get-WmiObject -namespace root\sms\site_$SiteCode -computer $SiteServer -query $query

The output is a bit strange to me :

__GENUS                          : 2
__CLASS                          : __GENERIC
__SUPERCLASS                     : 
__DYNASTY                        : __GENERIC
__RELPATH                        : 
__PROPERTY_COUNT                 : 2
__DERIVATION                     : {}
__SERVER                         : XXX
__NAMESPACE                      : root\sms\site_PR1
__PATH                           : 
SMS_G_System_ADD_REMOVE_PROGRAMS : System.Management.ManagementBaseObject
SMS_R_System                     : System.Management.ManagementBaseObject
PSComputerName                   : XXX

What am I missing here? Again i'm really new at this so I must be missing a key part of the logic.

If I remove :

, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version

The query works and shows me all the computers that have Chrome installed:

__GENUS          : 2
__CLASS          : SMS_R_System
__SUPERCLASS     : SMS_Resource
__DYNASTY        : SMS_BaseClass
__RELPATH        : 
__PROPERTY_COUNT : 1
__DERIVATION     : {SMS_Resource, SMS_BaseClass}
__SERVER         : XXX
__NAMESPACE      : root\sms\site_XXX
__PATH           : 
Name             : PXXX
PSComputerName   : XXX

but I want those 2 properties too, not just the computer name so I can confirm the version numbers.

Much appreciated.

The `SMS_` properties on that object are dictionaries. So you can do something like: ` | Select-Object -ExpandProperty SMS_G_System_ADD_REMOVE_PROGRAMS` and see what's within — Maximilian Burszley, Mar 08 '19 at 18:45
Of course! Sometimes I forget even the most basic stuff. Check the answer for how it can be solved :) — Rakha, Mar 08 '19 at 19:10

score 2 · Answer 1 · answered Mar 08 '19 at 19:11

2

The answer is to simply "expand" the dictionaries like pointed out by @TheIncorrigible.

So here is how I ended up doing it using the Name/Expression method in Select-Object:

$query = @"

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version
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 = "Google Chrome"

"@


Get-WmiObject -namespace root\sms\site_$SiteCode -computer $SiteServer -query $query | Select-Object @{name='ComputerName';expression={$_.SMS_R_System.Name} }, @{name='DisplayName';expression={$_.SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName} }, @{name='Version';expression={$_.SMS_G_System_ADD_REMOVE_PROGRAMS.Version} }

answered Mar 08 '19 at 19:11

Rakha

1,874
3
26
60

1

As a suggestion, I'd probably assign the wmi output to a variable, and pass it along as needed, maybe constructing my own `pscustomobject` to be returned if it's coming from a function rather than using calculated properties, for readability – Maximilian Burszley Mar 08 '19 at 19:14
Could you illustrate maybe how the PSCustomObject would be better if possible? I know how to build a custom object i'm just wondering the difference in how you suggest it. – Rakha Mar 08 '19 at 19:20
Cheers, wish Google landed me on this page sooner. – Snak3d0c Jun 17 '21 at 12:04

score 2 · Accepted Answer · answered Mar 08 '19 at 19:27

2

To expand on my comments in an alternate way to handle the problem at hand:

$wmiParams = @{
    Namespace    = 'root/sms/site_XXX'
    ComputerName = 'XXX'
    Query        = @'
SELECT SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version
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 = "Google Chrome"
'@
}
$smsResults = Get-WmiObject @wmiParams

foreach ($object in $smsResults) {
    [pscustomobject]@{
        ComputerName = $object['SMS_R_System']['Name']
        DisplayName  = $object['SMS_G_System_ADD_REMOVE_PROGRAMS']['DisplayName']
        Version      = $object['SMS_G_System_ADD_REMOVE_PROGRAMS']['Version']
    }
}

answered Mar 08 '19 at 19:27

Maximilian Burszley

18,243
4
34
63

What an elegant solution! First time I see the usage of [''] though, it looks a lot cleaner using the dot notation doesn't it ? $object.SMS_G_System_ADD_REMOVE_PROGRAMS.Version .Guess it's a matter of taste :) thanks again – Rakha Mar 08 '19 at 19:49
@Rakha For dictionary-like objects, they're equal. PowerShell is nice and converts those entries into properties you can access that way. In `C#`, however, working with WMI objects requires that notation, so I continue doing it that I don't forget. – Maximilian Burszley Mar 08 '19 at 19:58
Thank you for the information. One day I will learn C#, it's one of my goals :) Better start getting used to that notation then ;) – Rakha Mar 08 '19 at 20:00

SCCM WQL Query - List version of a specific installed application?

2 Answers2