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.
Related Articles
App Broker not Removing Users from AD Groups when Uninstalling. 7Number of Views App Broker Catalog Items Not Syncing with ServiceNow 4Number of Views Fix Intune sync failure when ConfigMgr sync is enabled in App Broker 2025 R1 8Number of Views App Broker site down. Users unable to access App Broker site. 8Number of Views A list of commonly referenced App Broker database tables 9Number of Views
Hi, I am Reva - Ask me anything.
No new updates
Thanks for the feedback!
Your feedback has been saved.Rate this response:
Add Additional feedback ( Optional )
Are you sure you want to cancel
the case creation?
Are you sure you want to cancel the case creation?
Are you sure you want to close this case
| Products | Region | Phone Numbers |
|---|---|---|
| FlexNet Operations FlexNet Embedded FlexNet Publisher FlexNet Connect FlexNet Code Insight InstallAnywhere InstallShield |
North America * |
+1 630-332-2513 (toll) +1 877-279-2853 (toll-free in North America) |
| Europe * |
+44 1925 944367 (toll) +44 800 047 8642 (toll-free in Europe) |
|
| Japan * | +81 3-4540-5335 (select option 2) | |
| Australia * |
+61 3 9895 2177 +61 1800 560 603 (toll-free in Australia) |
|
|
Usage Intelligence (formerly
Revulytics) Compliance Intelligence |
Please use the Case Portal to submit your support ticket or reach out to your Revenera contact. | |
Case id: 00001065
Activity: Status change: 2 hours ago