Active participant

Custom User Sync SQL Query

Jump to solution

Does anyone have any examples of what they have used to limit the users that are returned?

0 Kudos
2 Solutions

Accepted Solutions
Flexera
Flexera

Re: Custom User Sync SQL Query

Jump to solution

To elaborate, the following would be a custom query which would filter out users which had the name support or service:

SELECT DISTINCT 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
Where vru.Name0 Not Like '%service%'
AND vru.Name0 Not Like '%support%'

One thing I forgot to mention previously is that after you add your custom user query, I'd recommend deleting the old data in WD_User prior to performing another sync.. This will ensure that only those users in the custom query will exist in WD_User.

Thanks
Charles

View solution in original post

0 Kudos
Moderator Moderator
Moderator

Re: Custom User Sync SQL Query

Jump to solution

Please note that there is a Custom User ID Sync SQL Query and a Custom User Sync SQL Query.  The examples from Charles put the WHERE clause on the Custom User Sync SQL Query; however, I always recommend putting any filter clauses on the Custom User ID Sync SQL Query.  The way the sync process works is that it first runs the user ID query to get a list of all user resource ID's that match the criteria.  Then we break that list up into pages based on the page size you've defined in your settings and we run the user query to get the user details for those resource ID's, one page at a time.  If you put the filter on the user ID query, it will bring back a smaller set of resource ID's, which will result in fewer pages, which will result in shorter sync times.

Here are a couple examples that I have used with customers to overcome various issues (in both cases, the Standard User Sync SQL Query can remain unchanged):

Collection-Based User Limiting

App Portal's licensing model has been changed (as of 2015) to user-based licensing instead of device-based licensing.  However, the limiting view/collection in the site settings was never changed to match, so in order to remain in compliance with your user-based license count, it is best to create an SCCM user collection containing only the user accounts that are licensed to use App Portal.  Then you can use a custom query to pull in only those users and ignore accounts like service accounts, test accounts, and admin accounts.

SELECT ResourceID AS UserResourceID
FROM v_CM_RES_COLL_CM10029D
ORDER BY SMSID

…where CM10029D is the collection ID of the desired user collection to use for limiting.  This will limit the list of resource ID's to only those users that are in the selected user collection.

Skip Duplicate User Records in SCCM

Under rare circumstances, I have seen customers where SCCM has more than one user record with the same user name but different resource ID's.  This is typically more common with computer records, where reimaging a machine with the same machine name can result in a new resource ID (and a similar custom query can be used for the Custom Computer ID Sync SQL Query).  In both cases, you'll see unique key constraint violation errors in the data sync error log.  To avoid this and only pull in non-duplicate user records from SCCM...

SELECT vru.ResourceID as UserResourceID
FROM v_R_User vru
WHERE (SELECT COUNT(ResourceID) FROM v_R_User WHERE User_Name0 = vru.User_Name0) = 1
ORDER BY vru.Name0
Note 1:  Users with duplicate records will not be synced into App Portal.  This will prevent them from using App Portal.  If you run into a user that is not able to use App Portal and you don't see their account in App Portal, but they do show up in SCCM, make sure there are not duplicate records in SCCM for that user.  If so, you'll need to remove all of the duplicate records and keep only the one you want (usually the one with the highest ResourceID).

Note 2:  If you don't use the custom query and you have duplicate records in SCCM, you will fail to sync more users into App Portal than just the duplicate users.  The way the sync process works, if it hits an error on a particular user, it will skip all of the other resource ID's in that page of ID's and continue with the next page.  If your page size is large, you may miss a lot of users.

Note 3:  Even if you do use this custom query, you may still hit unique key constraint violations.  This could happen if you complete a sync cycle and pull in a user record with a particular ResourceID.  Then at a later date, the same user has a new ResourceID (but only has one record for that user, i.e. not a duplicate).  In such situations, you would need to delete that user from the WD_User table in the App Portal database and run a fresh sync (or you could just modify the record to have the new ResourceID, which might actually be safer).
Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

View solution in original post

7 Replies
Flexera
Flexera

Re: Custom User Sync SQL Query

Jump to solution
The following would be the user sync query that App Portal uses by default.. You can modify this so that it will return the desired results.. While you can add new column names, do not remove any columns, as App Portal expects these.

SELECT DISTINCT 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

Obviously, you would typically add a where clause if you wanted to filter out a sub-set of users.

You can find links to some additional sync queries at the following location:

https://helpnet.flexerasoftware.com/appportal2019r1/default.htm#helplibrary/AP_Deployment_Common.htm...

NOTE: After you change a sync query, be sure to restart the ESD Service so that the new values will be picked up by the sync process.
Flexera
Flexera

Re: Custom User Sync SQL Query

Jump to solution

To elaborate, the following would be a custom query which would filter out users which had the name support or service:

SELECT DISTINCT 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
Where vru.Name0 Not Like '%service%'
AND vru.Name0 Not Like '%support%'

One thing I forgot to mention previously is that after you add your custom user query, I'd recommend deleting the old data in WD_User prior to performing another sync.. This will ensure that only those users in the custom query will exist in WD_User.

Thanks
Charles

View solution in original post

0 Kudos
Moderator Moderator
Moderator

Re: Custom User Sync SQL Query

Jump to solution

Please note that there is a Custom User ID Sync SQL Query and a Custom User Sync SQL Query.  The examples from Charles put the WHERE clause on the Custom User Sync SQL Query; however, I always recommend putting any filter clauses on the Custom User ID Sync SQL Query.  The way the sync process works is that it first runs the user ID query to get a list of all user resource ID's that match the criteria.  Then we break that list up into pages based on the page size you've defined in your settings and we run the user query to get the user details for those resource ID's, one page at a time.  If you put the filter on the user ID query, it will bring back a smaller set of resource ID's, which will result in fewer pages, which will result in shorter sync times.

Here are a couple examples that I have used with customers to overcome various issues (in both cases, the Standard User Sync SQL Query can remain unchanged):

Collection-Based User Limiting

App Portal's licensing model has been changed (as of 2015) to user-based licensing instead of device-based licensing.  However, the limiting view/collection in the site settings was never changed to match, so in order to remain in compliance with your user-based license count, it is best to create an SCCM user collection containing only the user accounts that are licensed to use App Portal.  Then you can use a custom query to pull in only those users and ignore accounts like service accounts, test accounts, and admin accounts.

SELECT ResourceID AS UserResourceID
FROM v_CM_RES_COLL_CM10029D
ORDER BY SMSID

…where CM10029D is the collection ID of the desired user collection to use for limiting.  This will limit the list of resource ID's to only those users that are in the selected user collection.

Skip Duplicate User Records in SCCM

Under rare circumstances, I have seen customers where SCCM has more than one user record with the same user name but different resource ID's.  This is typically more common with computer records, where reimaging a machine with the same machine name can result in a new resource ID (and a similar custom query can be used for the Custom Computer ID Sync SQL Query).  In both cases, you'll see unique key constraint violation errors in the data sync error log.  To avoid this and only pull in non-duplicate user records from SCCM...

SELECT vru.ResourceID as UserResourceID
FROM v_R_User vru
WHERE (SELECT COUNT(ResourceID) FROM v_R_User WHERE User_Name0 = vru.User_Name0) = 1
ORDER BY vru.Name0
Note 1:  Users with duplicate records will not be synced into App Portal.  This will prevent them from using App Portal.  If you run into a user that is not able to use App Portal and you don't see their account in App Portal, but they do show up in SCCM, make sure there are not duplicate records in SCCM for that user.  If so, you'll need to remove all of the duplicate records and keep only the one you want (usually the one with the highest ResourceID).

Note 2:  If you don't use the custom query and you have duplicate records in SCCM, you will fail to sync more users into App Portal than just the duplicate users.  The way the sync process works, if it hits an error on a particular user, it will skip all of the other resource ID's in that page of ID's and continue with the next page.  If your page size is large, you may miss a lot of users.

Note 3:  Even if you do use this custom query, you may still hit unique key constraint violations.  This could happen if you complete a sync cycle and pull in a user record with a particular ResourceID.  Then at a later date, the same user has a new ResourceID (but only has one record for that user, i.e. not a duplicate).  In such situations, you would need to delete that user from the WD_User table in the App Portal database and run a fresh sync (or you could just modify the record to have the new ResourceID, which might actually be safer).
Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

View solution in original post

Occasional contributor

Re: Custom User Sync SQL Query

Jump to solution
We have experienced an issue with the user sync encountering errors due to NULL values in the username coming from SCCM. Is there a Custom SQL Query I can put in place to prevent it from trying to sync these records? I've reached out to our SCCM resource to have them clean up the data, but I'd rather have something in place on our end to prevent this from happening in the future.
0 Kudos