Joining two tables in WQL/SCCM

Question

I think I'm being really stupid here.

I'm using vbscript. I've connected to an SCCM server

Set locator = CreateObject("WbemScripting.SWbemLocator")
Set connection  = locator.ConnectServer("SERVERNAME", "Root\SMS\SITENAME")

I then have a WMI WQL query:

Set Collections = connection.ExecQuery("SELECT LastStatusTime,AdvertisementID,
  LastStateName,AdvertisementName 
  FROM SMS_ClientAdvertisementStatus 
  INNER JOIN SMS_Advertisement 
  ON SMS_Advertisement.AdvertisementID = SMS_ClientAdvertisementStatus.AdvertisementID 
  WHERE  LastStateName = 'Succeeded' 
  AND LastStatusTime > '2012-09-25'")

FOR each Collection in Collections 

        Collection.LastStatusTime 
        Collection.AdvertisementID

Next

I think there's a gap in my understanding of WQL. I seem to be able to join these two WQL "tables" in this query, but I can only return values from SMS_ClientAdvertisementStatus.

If I try to return anything from SMS_Advertisement, the table I've joined, I just get an error.

Can you join "tables" in WQL - if they even are tables? Or do I have to have a nested query? Or is there another way of returning data from two tables?

score 1 · Answer 1 · answered Sep 25 '12 at 14:19

1

The WQL language is just a subset of SQL and doesn't supports the JOIN statement, instead you can use the ASSOCIATORS OF in some cases.

answered Sep 25 '12 at 14:19

RRUZ

134,889
20
356
483

score 1 · Accepted Answer · answered Sep 25 '12 at 18:12

1

WQL doesn't support JOINs, but you can use MOF to define WMI classes that contain data from multiple classes. See here:

Creating a New Instance from Old Properties

answered Sep 25 '12 at 18:12

Anonimista

742
1
5
12

@cogumel0 Can you prove it with relevant resource/reference and a practical example ? You might be able to execute the queries without visible errors but the joins will not work. – Vasil Nikolov Oct 11 '17 at 15:58

score 0 · Answer 3 · answered Nov 24 '14 at 06:58

0

WQL does support joins. Here is a sample working query, which lists the names of devices which match with collection names. Works in SCCM 2012.

select SMS_R_SYSTEM.Name from SMS_R_System inner join SMS_Collection as Systems on Systems.Name = SMS_R_System.Name

answered Nov 24 '14 at 06:58

Divyanand M S

47
2

This is incorrect, WQL does not support joins. Please see the list of operators here supported by WQL https://msdn.microsoft.com/en-us/library/aa394606(v=vs.85).aspx – verisimilitude May 17 '17 at 07:26
I tested the WQL query before posting here. It does work. This blog also talks about it - https://social.technet.microsoft.com/wiki/contents/articles/12050.example-wql-queries-for-configuration-manager.aspx – Divyanand M S May 18 '17 at 13:50
For clarity, it appears [SCCM extends WQL](https://social.microsoft.com/Forums/en-US/e0d450fa-cba4-432e-a244-d6e92337dab1/wql-script-for-sccm-using-the-inner-join-function?forum=Offtopic) which adds some support for JOINs @verisimilitude – Maximilian Burszley May 28 '19 at 18:46

score 0 · Answer 4 · answered Nov 05 '17 at 22:43

0

I had a similar issue when trying to use JOIN statements in my PowerShell SCCM / ConfigManager queries and found this to be a great solution:

https://gallery.technet.microsoft.com/scriptcenter/SCCM-2012-WMI-query-with-0daea30c#content

I believe the methods could translate to other languages too.

answered Nov 05 '17 at 22:43

Josh Wright

422
3
12

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Nik Nov 05 '17 at 23:03

Joining two tables in WQL/SCCM

4 Answers4

Linked