Converting SQL To WQL

Question

I created this query in SQL. It works fine. I confirmed the data I was getting was both accurate and what I wanted.

SELECT 
   v_R_System.Name0,
   v_MeteredUser.UserName,
   v_R_User.Full_User_Name0,
   User_Principal_Name0,
   physicalDeliveryOfficeNam0,
   v_GS_SoftwareFile.FileName,
   v_GS_SoftwareFile.FileVersion,
   MAX(v_MonthlyUsageSummary.LastUsage) as "LastUsedDate",
   GETDATE() as "CurrentDate",
   DATEDIFF(day,MAX(v_MonthlyUsageSummary.LastUsage),GETDATE())
FROM v_MeteredUser 
          INNER JOIN v_MonthlyUsageSummary ON v_MeteredUser.MeteredUserID = v_MonthlyUsageSummary.MeteredUserID
          INNER JOIN v_GS_SoftwareFile ON v_MonthlyUsageSummary.FileID = v_GS_SoftwareFile.FileID
          INNER JOIN v_R_System ON v_MeteredUser.UserName = v_R_System.User_Name0
          INNER JOIN v_R_User ON v_MeteredUser.UserName= v_R_User.User_Name0
WHERE v_GS_SoftwareFile.FileName in ('extra.exe')
GROUP BY v_R_System.name0,
         v_MeteredUser.UserName,
         v_R_User.Full_User_Name0,
         User_Principal_Name0,
         physicalDeliveryOfficeNam0,
         v_GS_SoftwareFile.FileName,
         v_GS_SoftwareFile.FileVersion
HAVING (DATEDIFF(day, MAX(v_MonthlyUsageSummary.LastUsage), GETDATE()) >90)
ORDER BY v_R_System.Name0,
         v_MeteredUser.UserName,
         v_R_User.Full_User_Name0,
         User_Principal_Name0,
         physicalDeliveryOfficeNam0

I wanted to eventually translate this over so I could use it in SCCM to populate a collection with whatever software I wanted to do a 90 day removal on. I started converting everything over line by line, confirming the names, and that the same items existed in SCCM. Then I started to remove lines I was pretty sure I didn’t need (there are a few more I think I can remove). After some trial and error I came to this.

SELECT
      SMS_R_System.Name,
      SMS_MeteredUser.UserName,
      SMS_R_User.FullUserName,
      SMS_R_User.UserPrincipalName,
      SMS_R_User.physicalDeliveryOfficeName,
      SMS_G_System_SoftwareFile.FileName,
      SMS_G_System_SoftwareFile.FileVersion
FROM SMS_MeteredUser
             INNER JOIN SMS_MonthlyUsageSummary ON SMS_MeteredUser.MeteredUserID = SMS_MonthlyUsageSummary.MeteredUserID
             INNER JOIN SMS_G_System_SoftwareFile ON SMS_MonthlyUsageSummary.FileID = SMS_G_System_SoftwareFile.FileID
             INNER JOIN SMS_R_System ON SMS_MeteredUser.UserName = SMS_R_System.Name
             INNER JOIN SMS_R_User ON SMS_MeteredUser.UserName = SMS_R_User.UserName
WHERE
     SMS_G_System_SoftwareFile.FileName in ('extra.exe') AND
     (DATEDIFF(day, SMS_MonthlyUsageSummary.LastUsage, GETDATE()) >90)
ORDER BY SMS_R_System.Name

The collection wasn't populating so I opened the query to try something and got an error, which would explain why it wasn't populating. The error I am getting is:

The specified resource class does not match the current collection type.

I am not 100% sure what I am doing wrong. I have a similar query that works fine.

Answer 1

There are several factors here that come into play (like that WQL only supports a subset of the features of SQL, or that not all names or even columns translate directly) but I think the most important thing here is that a query based collection can not operate on any query result.

The advantage of this seems to be that you don't even have to take care that your result is identifiable as a list of devices. You could select only one ambigous column from v_r_system and it would still work (in fact after saving you would notice that the query was modified automatically to include e.g. the ResourceID). It also is no problem for SCCM to populate the collection if the result has multiple columns for one record.

The downside is that you cannot just "guarantee" by your own design that what you produce is a valid list. In order for it to populate a collection you have to follow some guidelines. Now I am not really sure what is possible or whether there are exception but from what I found out by trial, the best idea is to always start with SMS_R_SYSTEM as your base class for device collections (and SMS_R_USER for user collections). If you join the rest to that class SCCM will always be able to populate your collection.

So with that in mind I would just rearrange the selection which should not be problematic with the inner joins (I hope my limited knowledge of SQL is not betraying me here) so it would become:

SELECT
    SMS_R_SYSTEM.ResourceID,
    SMS_R_SYSTEM.Name,
    SMS_MeteredUser.UserName,
    SMS_R_User.FullUserName,
    SMS_R_User.UserPrincipalName,
    SMS_G_System_SoftwareFile.FileName,
    SMS_G_System_SoftwareFile.FileVersion
FROM SMS_R_System       
    INNER JOIN SMS_MeteredUser ON SMS_MeteredUser.UserName = SMS_R_System.LastLogonUserName
    INNER JOIN SMS_MonthlyUsageSummary ON SMS_MeteredUser.MeteredUserID = SMS_MonthlyUsageSummary.MeteredUserID
    INNER JOIN SMS_G_System_SoftwareFile ON SMS_MonthlyUsageSummary.FileID = SMS_G_System_SoftwareFile.FileID
    INNER JOIN SMS_R_User ON SMS_MeteredUser.UserName = SMS_R_User.UserName
WHERE       
    SMS_G_System_SoftwareFile.FileName in ('extra.exe') AND
    (DATEDIFF(day, SMS_MonthlyUsageSummary.LastUsage, GETDATE()) >90)

which worked fine as a query to populate my collection. Notice I had to remove 'physicalDeliveryOfficeName' because it is not present on my system, but you could in fact remove any of the properties because the query does not care for them anyway (and this sort of query will also only ever work in the "direct query edit" and never in the query designer anyway.

In general I would say while you correctly noticed that a direct translation from SQL to WQL is not possible I would still say it is a good starting point as long as you keep in mind the possible downfalls because one huge upside is just the speed. I tried some debugging via WQL on your example and in my environment most of the classes cannot even be queried without qualifying some parameter because it would take several minutes and just time out. In SQL the most it took was 15 seconds and that makes checking of your output just so much more convenient.