Skip to main content

WebService - Computer search

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.

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

It also distinguishes between two types of queries.

DefaultQuery

This query is executed if the scope security (configuration: ComputerFilterEnabled = false)  is disabled in the web service. Thus all users will see all computer objects that are returned.

UserPermissionQuery

This query is executed if scope security (configuration: ComputerFilterEnabled = true) is enabled in the web service. 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, beliebige weitere Spalten
FROM   {COLLECTION_VIEW} vcol
JOIN   beliebige Tabellen

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