Unable to create SCCM collection using query

Garima Das 961 Reputation points
2022-11-16T15:51:15.587+00:00

Hi experts,

I want to create a dynamic SCCM Collection using query Rule. I have the SQL query but I am unable to map it as WQL query in SCCM.

Below is the SQL query that I have created:

select Name0, vrs.AADTenantID, User_Name0, Full_Domain_Name0, AD_site_Name0, Operating_System_Name_and0, operatingSystem0 , LastHW, LastActiveTime, Last_Logon_Timestamp0, Build01, Distinguished_Name0 from v_R_system vrs
join v_CH_ClientSummary ch on ch.ResourceID=vrs.ResourceID
where Operating_System_Name_and0 like '%workstation 10%'
and Operating_System_Name_and0 not like '%server%'
and operatingSystem0 like'%Windows 10 Pro%'
and Distinguished_Name0 like '%OU=Regional%'
and Distinguished_Name0 not like '%OU=Staging%'
and (Build01 like '10.0.19043'
or Build01 like '10.0.19044'
or Build01 like '10.0.19045')

I have tried but I am unable to create a WQL query to map this in the collection. Is there any way to use SQL query in Collection creation?

If not, how can I use this to create a WQL query?

Thanks

Microsoft Configuration Manager
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sherry Kissinger 3,801 Reputation points
    2022-11-16T16:49:49.54+00:00

    My Opinion: for a collection query, all you care about in the end is that the resourceid added fit all of your WHERE conditions. But I have a problem personally with some of your 'where' conditions.

    I have no idea why you think you need to filter on three things:
    Operating_System_Name_and0 like '%workstation 10%'
    AND
    Operating_System_Name_and0 not like '%server%'
    AND
    and operatingSystem0 like'%Windows 10 Pro%'

    If they are going to be a workstation, they won't be a server... so adding the 'not like a server' has no purpose.
    If you are filtering by Windows 10 Pro from the OperationsSystem as reported via Hardware Inventoy, I don't see a purpose in filtering by the OSNameAndVersion as reported by discovery.

    So to me, the only things you care about is...

    • Windows 10 Pro, as reported by inventory
    • OU like regional% (reported by Heartbeat Discovery)
    • even if it's in regional% for an OU, don't include the ones that might be in STAGING ou
    • 3 specific builds, as reported by discovery.

    So this is what I think you want... Oh, and try to avoid having leading % in a where like statement. There is no reason to make SQL work harder than it needs to.
    and... don't use "not like" in a WQL Collection Query (and should avoid it in sql too, it might mess you up). What you want is a subselect query... example below.

    Select SMS_R_System.ResourceID
    from SMS_R_System
    inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID
    where
    SMS_G_System_Operating_SYSTEM.Name like "Microsoft Windows 10 Pro%"
    and
    SMS_R_System.SystemOUName like "Your.F.Q.D.N/Something/Regional%"
    and
    SMS_R_System.Build in ("10.0.19043","10.0.19044","10.0.19045")
    and
    SMS_R_System.ResourceID NOT IN (
    Select SMS_R_System.Resourceid from SMS_R_System Where SystemOUName like "Your.F.Q.D.N/Something/Regional%Staging%"
    )

    Of course, adjust your SystemOUName to be correct for your OUs / FQDN

    1 person found this answer helpful.
    0 comments
  2. Rahul Jindal [MVP] 9,141 Reputation points MVP
    2022-11-16T16:21:29.757+00:00

    Here are some WQL examples based on a quick google search result -

    url

    Old, but still valid.

    0 comments