Search code examples
joinvbscriptwmiwqlsccm

Joining two tables in WQL/SCCM

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?

Solution

  • 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