cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

User Custom query to limit AppPortal import from SCCM to only include AD users with Enabled=True

Is it possible to limit AppPortal, using custom query, to only import users from SCCM that are marked as "Enabled" in Active Directory?   If we cannot determine user status from reading SCCM, is there a convention for creating a custom table in AppPortal that we can populate with only "Enabled" users from AD and then use that in the custom query to join with our SCCM query to limit to importing only the active users?

(1) Solution

Yes, that is an unfortunate side effect of using a non-SCCM source.  The sync process is actually different between an SCCM source and a non-SCCM source, and the non-SCCM source method is definitely less efficient.  If this performance is going to be an issue, then the only other option I could think of would be to either put your custom table in the SCCM database and use a custom sync with the ConfigMgr sync type; or if you're allowed to run "linked queries" across SQL servers, you could write the custom query in a way that uses the ConfigMgr sync type, connecting to the SCCM database, but then joins to your data on the App Broker SQL server using fully qualified references (e.g. [ServerName].[AppPortal].[dbo].[Custom_ActiveUsers]).

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

(8) Replies
CharlesW
By Level 12 Flexeran
Level 12 Flexeran

As far as I can tell, there would be no way to use SCCM user discovery to determine which users are disabled. SCCM will not sync disabled users, so if a user has already been discovered and their status is changed to disabled, then SCCM will not know about it.

Theoretically, you could create a table that would map the status of a user which you could join against in your user sync query. It would be a lot easier to do if this was on the SCCM Db server.. Otherwise, you'd probably have to do something like a linked query.. 

A third possible option would be a staging table that contained all of the user data for enabled users. You can find the default user sync query at the following link:

https://docs.flexera.com/appportal/2022r1sp1/ag/Content/helplibrary/APR_Settings_Deploy_Common.htm#sitemgmt_2290099139_1169975

Your staging table should generally contain the columns in the query (FirstName, LastName, DisplayName, etc.). Also, the mandatory column names are listed under the section labeled:

"NOTE: App Portal is specifically looking to read the following mandatory columns"

The idea of a staging table sounds useful but would that staging table have to reside on the SCCM database or could we join to a new staging table on our AppPortal database server using the custom query.   Our SCCM team is not too excited about creating a custom table on their side.   
We are also investigating running a purge routine in SCCM to get rid of disabled employees so they don't come across in our sync and would not be rediscovered.
Is there a naming convention to follow for creating tables on the AppPortal database so as not to conflict with future updates from Flexera?

What I have done in several cases is to create a custom table in the App Broker database and then run my custom sync queries against that custom table.  If I'm using SCCM or Jamf or some other DB source to combine with other custom data/logic, I usually set up an external sync process to sync those sources into App Broker as well.  For example, I might have a PowerShell script (or SQL job) that syncs v_R_User to a table in the App Broker database called Custom_v_R_User or SCCM_v_R_User or something like that, and then I'll have another table with my data to be joined (e.g. Custom_ActiveUserData).  Then my custom sync query can run a JOIN against those tables (or I guess you could "pre-join" everything into one table and custom query against that).  As for naming of tables, it doesn't really matter, though I would avoid using WD_xxxxx.  I normally either use Custom_ as a prefix or <InsertCustomerNameHere>_ as a prefix for the table names.

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".

I have created two custom tables in AppPortal database.  One table contains enabled AD users and the other table contains data from Configuration Manager.  Modified the "Custom User Sync Connection String" to use the AppPortal SQL database.  I click on "Test User Sync Settings" and it returns success.  I save changes and click on 'Sync Data Now'.  Problem is I get an error stating the table name I used is an invalid object, although it tested find and when I past the query in a SQL Query windows, it returns data as expected.   But when I sync data, DataSync.log indicates the table I'm referencing is an invalid object.
Exception occurred while getting the User IDs for sync Invalid object name 'Custom_ConfigMan_v_r_user'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

When you use a custom sync query that doesn't go directly against your SCCM database, you need to change the sync type.

2023-03-17_10-15-26_SyncType.jpg

Change "Syncing Users" to "Not Defined".  If you have it configured for "ConfigMgr", it will try to build the connection string from the server/database settings on the ConfigMgr tab instead of using the connection string you provide with the custom query.

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".

Thank you.
Changing the option to 'Not Defined' fixed that problem but now performance of the sync is extremely slow.
I have a primary key defined on UserResourceID but that does not seem to improve.   Syncing to configuration Manager took seconds and using tables local to the AppPortal database is taking about 4 minutes per 5000 record block.

create table dbo.Custom_ConfigMan_v_r_user
(
UserResourceID int not null 
  constraint Goodyear_ConfigMan_v_r_user_PK primary key,
FirstName nvarchar(64),
LastName nvarchar(64),
---

Yes, that is an unfortunate side effect of using a non-SCCM source.  The sync process is actually different between an SCCM source and a non-SCCM source, and the non-SCCM source method is definitely less efficient.  If this performance is going to be an issue, then the only other option I could think of would be to either put your custom table in the SCCM database and use a custom sync with the ConfigMgr sync type; or if you're allowed to run "linked queries" across SQL servers, you could write the custom query in a way that uses the ConfigMgr sync type, connecting to the SCCM database, but then joins to your data on the App Broker SQL server using fully qualified references (e.g. [ServerName].[AppPortal].[dbo].[Custom_ActiveUsers]).

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".

It is frustrating the following local query take 5 minutes to return only 5,000 records.  I have an PK index on UserResourceID.

Executing CUSTOM User query select * from ( select FirstName,LastName,DisplayName,OS,Office,City,
PostalCode,Department,Title,UserName,Email,
Manager,UserResourceID,ADSPath,FullName,
vru.Name,UniqueName,UserDomain,Company,UserOU
from Custom_ConfigMan_v_r_user vru
join Custom_AD_Enabled_Users ADUsers on
ADUsers.SamAccountName=vru.UserName) u WHERE EXISTS (SELECT * FROM (select T.c.value('.','nvarchar(50)') as Id from @Ids.nodes('/L/I') T(c)) Ids where u.UserResourceID = Ids.id)