Computer Search Query

The SQL queries defined here return the SCCM computer objects that are to be available in SCCM Manager. It is mandatory that the [ResourceID] and [Name] columns are returned.

It also distinguishes between two types of queries.

DefaultQuery

This query is executed when scope security is enabled in the web service (Configuration: ComputerFilterEnabled = false). Thus, users see only computer objects to which they are authorized.

UserPermissionQuery

This query is executed when scope security is enabled in the web service (Configuration: ComputerFilterEnabled = true). Thus, users see only computer objects to which they are authorized.

For the scoping query to work, the SQL query must always be built according to the following principle.

• The columns vcol.[ResourceID] and vcol.[Name] must always be returned
• The SELECT query must be made on {COLLECTION_VIEW}, this value is internally automatically replaced by a collection view from the SCCM database (SCCM collection according to User Permissions)
• The JOIN clause should always be set to vcol.[ResourceID].

Example:

SELECT vcol.ResourceID, vcol.Name, Any other columns
FROM   {COLLECTION_VIEW} vcol
JOIN  any tables

Standard query (pre-installed)

SELECT
    CAST(vrs.ResourceID AS VARCHAR(50))                                          AS ResourceID
  , MAX(rasr.Resource_Names0)                                                    AS 'FQDN'
  , vrs.Name0                                                                    AS 'NAME'
  , os.Caption0                                                                  AS 'OPERATING_SYSTEM'
  , vrs.Resource_Domain_OR_Workgr0                                               AS 'DOMAIN'
  , vrs.User_Name0                                                               AS 'USER_NAME'
  , vrs.AD_Site_Name0                                                            AS 'AD_SITE_NAME'
  , MAX(ou.System_OU_Name0)                                                      AS 'ORGANIZATIONAL_UNIT'
  , DATEADD(hh, (DATEDIFF(hh, GETUTCDATE(), GETDATE())), csum.LastPolicyRequest) AS 'LAST_POLICY_REQUEST'
  , CAST ( vrs.Obsolete0 AS BIT )                                                AS 'OBSOLETE'
FROM
    {COLLECTION_VIEW} vcol
    LEFT JOIN
        v_R_System AS vrs
        ON
            vrs.ResourceID = vcol.ResourceID
    LEFT JOIN
        v_RA_System_SystemRoles AS RASS
        ON
            vrs.ResourceID = rass.ResourceID
    LEFT JOIN
        v_RA_System_SystemOUName AS ou
        ON
            vrs.ResourceID = ou.ResourceID
    LEFT JOIN
        v_CH_ClientSummary csum
        ON
            vrs.ResourceID = csum.ResourceID
    LEFT JOIN
        [v_GS_OPERATING_SYSTEM] os
        ON
            os.ResourceID =vrs.ResourceID
    LEFT JOIN
        v_RA_System_ResourceNames rasr
        ON
            vrs.ResourceID = rasr.ResourceID
WHERE
    rass.System_Roles0 IS NULL
GROUP BY
    vrs.ResourceID
  , vrs.Name0
  , os.caption0
  , vrs.Resource_Domain_OR_Workgr0
  , vrs.User_Name0
  , vrs.AD_Site_Name0
  , csum.LastPolicyRequest
  , vrs.Obsolete0