It is often difficult to diagnose approval issues, as most of the log files refer to the approvers AD GUID. Furthermore, when writing custom queries for various purposes, it may be necessary to report on the approval process, but the Approvers for a given request are always referenced using AD GUID's rather than unique user names (the approval process for requests are stored in the WD_ApprovalProcess table). Provided that the approver has logged in at some point, it may be possible to join on the WD_Profile table, to lookup a given unique user name, based on a GUID, but this will only work if the approver has logged into App Portal at some point. Using a custom user sync query is an easy way to sync additional user information into the WD_User table, provided that the information is being discovered in Configuration Managers User Discovery. By default, User Discovery discovers the AD Attribute ObjectGUID.. This attribute can then be synced into the App Portal DB, by adding the column name Object_Guid0 to a custom user sync query.. The following query illustrates the default user sync query, with an additional column added..
SELECT DISTINCT CONVERT([varchar](64),Object_GUID0, 2) as ADGUID, givenName0 AS FirstName, sn0 AS LastName, DisplayName0 AS DisplayName, Network_Operating_System0 AS OS, physicalDeliveryOfficeNam0 AS Office, l0 AS City, vru.postalCode0 AS PostalCode, department0 AS Department, title0 AS Title, User_Name0 AS UserName, mail0 AS Email, manager0 AS Manager, vru.ResourceID AS UserResourceID, distinguished_Name0 AS ADSPath, Full_User_Name0 AS FullName, Name0 AS Name, Unique_User_Name0 AS UniqueName, Windows_NT_Domain0 AS UserDomain, company0 AS Company, ra.User_OU_Name0 AS UserOU
FROM v_R_User AS vru
LEFT JOIN v_RA_User_UserOUName ra on ra.ResourceID = vru.ResourceID
Only the following was added to the default sync query:
CONVERT([varchar](64),Object_GUID0, 2) as ADGUID
This converts the binary value for Object_Guid0 in the Config Manager DB to a hex value, so that it is in the same format that App Portal uses. This custom query should then be added to the Custom User Sync Query setting under settings->Deployment->Common.. Upon saving the custom query, it will be necessary to restart the ESD Service so that the new value is picked up.. After selecting Sync Data Now to force a data sync, a new column will be added to the WD_User table, named ADGUID, which will contain the users AD GUID.
It should be noted that not only can this additional information be used in SQL queries for reporting, and debugging purposes, but any new columns that you add to your custom query can also be used as variables in your custom actions and notifications. In this particular case, a new variable named ##ADGUID## will be available.
