Skip to main content

WebService - Computer search

DieThe hierSQL definiertenqueries SQL-Abfragendefined gebenhere diereturn SCCM-Computerobjekte zurück, die imthe SCCM Managercomputer zurobjects Verfügungthat stehenare sollen.to Dabeibe müssenavailable zwingendin dieSCCM SpaltenManager. It is mandatory that the [ResourceID] undand [Name] zurückgegebencolumns werden.are returned.

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

AußerdemIt wirdalso zwischendistinguishes zweibetween Artentwo vontypes Abfragenof unterschieden.queries.

DefaultQuery

DieseThis Abfragequery wirdis ausgeführt,executed wennif diethe Scope-Securityscope security (Konfiguration:configuration: ComputerFilterEnabled = false)  imis Webservicedisabled abgeschaltetin ist.the Somitweb sehenservice. alleThus Benutzerall alleusers Computerobjektewill diesee zurückgegebenall werden.computer objects that are returned.

UserPermissionQuery

DieseThis Abfragequery wirdis ausgeführt,executed wennif diescope Scope-Securitysecurity (Konfiguration:configuration: ComputerFilterEnabled = true) imis Webserviceenabled eingeschaltetin ist.the Somitweb sehenservice. BenutzerThus, nurusers Computerobjektesee zuonly denencomputer Sieobjects berechtigtto sind.which they are authorized.

DamitFor diethe Scoping-Abfragescoping funktioniert,query mussto diework, SQL-Abfragethe immerSQL nachquery folgendemmust Prinzipalways aufgebautbe sein.built according to the following principle.

  • DieThe Spaltencolumns vcol.[ResourceID] undand vcol.[Name] müssenmust immeralways zurückgegebenbe werdenreturned
  • DieThe SELECT-AbfrageSELECT mussquery aufmust be made on {COLLECTION_VIEW}, erfolgen,this dabeivalue wirdis dieserinternally Wertautomatically internreplaced automatischby durcha einecollection Collection-Viewview ausfrom derthe SCCM-DatenbankSCCM ersetztdatabase (SCCM-CollectionSCCM lautcollection according to User Permissions)
  • DieThe JOIN-KlauselJOIN sollteclause immershould aufalways be set to vcol.[ResourceID] gesetzt werden.

Beispiel:Example:

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

Standard-AbfrageStandard query (vorinstalliert)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