Skip to main content

Web Service - Computer Search

The SQL queries defined here return the SCCM computer objects that should be available in SCCM Manager. The [ResourceID] and [Name] columns must be returned.

{Installation path}\Resources\SecurityConfiguration\UserPermissions.xml

In addition, there are two types of queries.

DefaultQuery

This query is executed when scope security (configuration: ComputerFilterEnabled = false) is disabled in the web service. As a result, all users see all computer objects that are returned.

UserPermissionQuery

This query is executed when scope security (configuration: ComputerFilterEnabled = true) is enabled in the web service. As a result, users see only the computer objects for which they have permission.

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

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

Example:

SELECT vcol.ResourceID, vcol.Name, beliebige weitere Spalten
FROM   {COLLECTION_VIEW} vcol
JOIN   beliebige Tabellen

Default 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
Back to top