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).
... View more
