Custom User Sync SQL Query

[goody612]

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

[CharlesW · Accepted 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

[CharlesW]

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?Highlight=custom%20user%20sync%20query

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.

[CharlesW · Accepted 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

[jdempsey · Accepted 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).

[patrick_quinla]

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.

[CharlesW]

Using a customer user sync ID query similar to the following should take care of the issue..

SELECT vru.ResourceID as UserResourceID
FROM v_R_User vru
WHERE Unique_User_Name0 IS NOT NULL

[patrick_quinla]

I put that statement into the 'Custom User ID Sync SQL Query' field but when I click 'Test User Sync Settings' I get an error message that says:
"ExecuteReader: CommandText property has not been initialized"

Any thoughts?

[CharlesW]

Ignore the test button error.. It is trying to validate all three fields pertaining to the custom user sync... You do not need to specify all three fields.. If you want to validate your query, just run it against the Config Mgr DB. If it works there, then it should also work in App Broker. The one thing you do need to do is restart the ESD Service after making a change to the settings.

[jeffrey_weidle]

Hi Charles,

I have a working Custom User Sync SQL Query as you know but I don't see my numbers matching up with my collection anymore. It seems that the "Licenses Used" number in App Portal aren't dropping to match my SCCM "licensing/limiting collection" they're only climbing. Should they drop or is it as you explained in the past when I changed the query and then needed to delete the WD_Users? Is it that the users only get added and not purged as they do in my SCCM collection with the AD query?

Thanks in advance ,

Jeff

@CharlesW 

[CharlesW]

What type of license are you using? I can't recall.. You can tell this by going to settings->general, and looking at the license Entity.. If USER, then the count is generated using the following query:

SELECT Count(UniqueName) from WD_User where lastupdate > DATEADD(day, -7, GETDATE()) and UniqueName is not null

The licensed collection is only used if you have a device based license.. In this case, the count should reflect the number of users in the specified license collection.  

[jeffrey_weidle]

Hi Charles,
We're on a user based license.
What I meant by licensed collection is the collection we have the custom query pointing to in SCCM to limit the numbers. That SCCM collection is querying AD so the numbers fluctuate as employees are terminated and added but they are not in App Portal (only rising, I noticed recently) I then thought about what you said that users are never purged only added. So I was wondering if that's what I'm experiencing.
See below:

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
Right JOIN v_CM_RES_COLL_LY202C54 vcm on vcm.ResourceID = vru.ResourceID
where ra.User_OU_Name0 = 'XXXXXXXX.COM/USER ACCOUNTS'


Kind regards,

Jeffrey Weidle
End User Devices
LBT 4-190
Office +1 713.309.4348
Cell +1 832.405.7516
Jeffrey.Weidle@LyondellBasell.com

[cid:image003.png@01D7110D.045511D0]

Information contained in this email is subject to the Disclaimer and Privacy Notice found by clicking on the following link: http://www.lyb.com/en/about-us/disclaimer

[jdempsey]

It looks like your collection JOIN is incorrect.  You are doing a RIGHT OUTER JOIN instead of using an INNER JOIN.  That means you will get a result for every user in v_R_User instead of only users that are in your collection.  If you run the query directly in SQL Management Studio, you should see the result, and that count should roughly match what you see in App Portal.  Also, as I've mentioned in previous threads, I would recommend removing that collection join from your Custom User Sync SQL Query and instead put it in your Custom User ID Sync SQL Query.

Try the following instead...

Custom User ID Sync SQL Query

SELECT vru.[ResourceID] AS [UserResourceID]
FROM [v_R_User] vru
INNER JOIN [v_CM_RES_COLL_LY202C54] vcm ON vcm.[ResourceID] = vru.[ResourceID]

...or you could go with the more simplified version like this, so you don't even need a JOIN...

SELECT [ResourceID] AS [UserResourceID]
FROM [v_CM_RES_COLL_LY202C54]
ORDER BY [SMSID]

Custom User Sync SQL Query

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 ra.User_OU_Name0 = 'XXXXXXXX.COM/USER ACCOUNTS'

Also, based on the query Charlie mentioned for calculating the license count, it looks like users that fall out of that collection will take 7 days before they are no longer counting against your license count.

[jeffrey_weidle]

So based on your last comments regarding the User license query, once I update the 2 queries do I not need to "delete the old data in WD_User prior to performing another sync" as Charles recommended in a previous thread? If that's the case than I would just need to restart the ESD service so that the new values will be picked up by the sync process.  Is that correct?

[jdempsey]

In theory, yes, that's correct.