SQL versus WQL

Duchemin, Dominique 2,006 Reputation points
2022-05-10T01:07:44.74+00:00

Hello,

I have a SQL query:

select sys.name0, Creation_Date0, os.Caption0,WINSPrimaryServer0, WINSSecondaryServer0 from v_R_System sys
join v_GS_NETWORK_ADAPTER_CONFIGUR NAC on NAc.ResourceID=sys.ResourceID
join v_GS_OPERATING_SYSTEM os on os.ResourceID=sys.ResourceID
join v_FullCollectionMembership col on col.ResourceID = sys.ResourceID
where
OS.Caption0 like '%server%'
and nac.IPEnabled0='1'
and col.CollectionID = 'UCP00242'
and (NAC.WINSPrimaryServer0 IS NULL)
or NAC.WINSSecondaryServer0 IS NULL)

order by Name0 ASC

and a WQL for the collection

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System
inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where
SMS_R_System.OperatingSystemNameandVersion like "%server%" and SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPEnabled = 1 and SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.WINSPrimaryServer is null and SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.WINSSecondaryServer is null


I have a result of 216 items in SQL and 192 only in the WQL. I noticed in SQL there are some servers which appear multiple time… but I am not sure how to identify them versus the collection which is displaying only 1 item per server.

Thanks,
Dom

Microsoft Configuration Manager
0 comments No comments
{count} votes

Accepted answer
  1. AllenLiu-MSFT 42,746 Reputation points Microsoft Vendor
    2022-05-10T07:16:56.053+00:00

    Hi, @Duchemin, Dominique

    Thank you for posting in Microsoft Q&A forum.

    The conditions in your SQL query is (NAC.WINSPrimaryServer0 IS NULL or NAC.WINSSecondaryServer0 IS NULL) means we need one of the two is NULL or both to be NULL.

    The conditions in the WQL
    SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.WINSPrimaryServer is null and SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.WINSSecondaryServer is null
    means we only need both to be NULL.


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Garth 5,801 Reputation points
    2022-05-10T10:18:26.783+00:00

    Your queries are not the same for everything. For example you are looking at os name from r system in one case and os in another.

    0 comments No comments