Custom queries in App Portal may not be applied, resulting in invalid data being synced.

[#1]

Summary

Under certain circumstances, custom queries may not be applied, resulting in invalid data being synced.

Symptoms

App Portal allows custom queries to be defined in order to control the users, devices, and relationships which are synced into the App Portal database. Under certain circumstances, these custom queries may not be applied, resulting in invalid data being synced.

If using a custom computer sync query, devices, which are not included in the custom computer query results are being added to the WD_Computer table.

Cause

When logging into App Portal, or requesting on behalf of a particular device, App Portal will attempt to sync the "single" user/computer data from SCCM. Unlike the nightly data sync, this "single" computer sync will not use the custom computer sync that was specified, instead, it will use the standard query, where data comes directly from v_R_System in the SCCM database.

Resolution

The issue is occurring because the single computer sync is looking at the wrong setting in the App Portal database, thus the custom computer query is not found.. To resolve the problem, it is necessary to create the missing setting.. To create this setting, perform the following steps;

1. Create a new row in WD_AppSettings which will be used to hold the missing setting. Use the following to create the empty row.:

insert into WD_AppSettings (KeyName, Value)
Values('SyncDTComputerQuery', '')

2. Use the following query to copy the custom query value used by the nightly sync into the missing setting used by the single computer sync:

update wd_appsettings
set value = (select value from wd_appsettings where keyname = 'SyncDTComputersSQLQuery')
where keyname = 'SyncDTComputerQuery'

Note: If you make a change to "Custom Computer Sync SQL Query ", then be sure that the second query is run again.

3. As App Portal will not delete records from the WD_Computer table on its own, it will be necessary to manually delete the records from the WD_Computer table. The query to delete the records from WD_Computer is as follows:

Delete from WD_Computer

4. Perform a full data sync by going to Admin->Deployment->Common and selecting the "Sync Data Now" button.

Additional Information

For additional information on defining custom sync settings in App Portal, please see Entering Common Deployment Technology Settings in the App Portal Administration Guide.