Remove the red warning that appears on our App Portal landing page

[NicOla]

I’d like to know how to remove the red warning that is appearing on our App Portal landing page, beside the Welcome <username> message

When the warning is clicked on this message is displayed: Your license count for App Portal is exceeded by 25% or more

I have read the KB article.

I believe the warning message is not true and is caused by users being synced into the WD_User table, but never removed.

When I run ‘SELECT Count(UniqueName) from WD_User where lastupdate > DATEADD(day, -7, GETDATE()) and UniqueName is not null’, I get a much much larger number than when I run our ‘Custom User ID Sync SQL Query’.

I was told by the APAC Flexera support team that there is an enhancement request in to fix this (as per my case # 01989936) and it could be fixed in the 2020 release. In the meantime …. is there a setting I can tick/untick to have that error message removed from our App Portal, it looks bad and is probably going to generate unnecessary worry/complaints with my users.

I see did see mentioned in a solution in a previous post ‘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’

I’m not sure how to safely delete the old WD_User data – also if we did delete the old user info, would we lose it completely (what if we wanted to do reporting – would all the old user data be lost?

Thanks in advance

[CharlesW]

I can't condone trying to hide or remove the warning icon... I think that we need to tackle the root of the problem.. It sounds like you are on the correct path.. A custom user sync query is the correct way to limit the count, and it sounds as if yours appears to be working as you would expect..  App Portal determines the license count by running the following query (I believe that this is done when the application pool recycles)

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

As you can see, it is checking the lastupdate column, and it should only count those users who have not been updated in 7 days... This should include any rows which are NOT returned by the custom user sync query you are using. 

honestly, I'd expect the above query to give you a count which would be somewhat similar to the number of rows that your custom user sync query is returning.. At what point did you add the custom user sync query? If it was in the last 7 days, then the results would not be reflected in the count, as the LastUpdate column would have been updated by the default user sync query..

As our APAC support team had mentioned, we typically suggest deleting all rows from WD_user, so that we can start fresh with the new custom user sync query results..  This should ensure that you have an accurate count, relative to the number of rows returned by your custom query.. You can safely delete rows from the WD_User table, as the rows are never referenced by ID's by App Portal, so you are not going to break the referential integrity of the DB by doing so.. You can do something as simple as running the query "delete from wd_user", and then use "sync data now" to sync the data again... I often times recommend creating a SQL job, run once a week, to purge records which are not being updated... This SQL Job would run the following two queries:

delete from wd_user 
where LastUpdate <= DATEADD(day,-7,GETDATE()) 

delete from wd_Computer
where LastUpdate <= DATEADD(day,-7,GETDATE()) 

It would be nice if App Portal did this for you, but ultimately, it will end up being the same as if you were to run the above two queries.. 

NOTE: Be sure to do an iisreset to ensure that a new count is getting picked up, after you've made changes to WD_User.

[CharlesW]

After discussing this post with Jim,  I stand corrected.. Periodically deleting rows form wd_user may be less than Ideal.. Locations which reference user “properties” such as DisplayName, FullName, etc.  would  not be able to display those properties (for example, request details under the My Requests page) as they are coming from the synced data in WD_User..  If you were to delete the rows from WD_user, it should not cause any errors in the product, however user data for old requests would not render completely.

As I ponder more, I’d have to say that the existing functionality which is in place to count licenses should be sufficient, since the license count is based off the lastupdate date.. I don’t see that engineering would ever be able to implement an enhancement to actually cleanup records old records from the WD_User table; otherwise the user “properties” properties might go missing for some older requests made by the deleted user.   

I'd expect that if you were to implement the custom user sync query, then things should take care of themselves after 7 days. 

[NicOla]

Hello, Thanks for that information. This custom user sync query was added by the Flexera consultant who set up our system for us (2+ years ago), we have not changed it since. When I run our Custom User ID Sync SQL Query I get 18,437. When I run our Custom User Sync SQL Query I also get 18, 437. When I run ‘SELECT Count(UniqueName) from WD_User where lastupdate > DATEADD(day, -7, GETDATE()) and UniqueName is not null’ the result is 23,049. Would you know why there is a difference between these numbers? Is there supposed to be? I also ran the following …. When I ran ‘select * from wd_user where LastUpdate <= DATEADD(day,-7,GETDATE())’ I got 3,737 rows returned. When I ran ‘select * from wd_Computer where LastUpdate <= DATEADD(day,-7,GETDATE())’ I got 37,877 rows returned. Do these numbers look normal? Thanks again

[CharlesW]

I must admit, things are not adding up here.. As mentioned, I'd expect that the problem should have taken care of itself, as the LastUpdate column would be only be updated (during the nightly data sync)  for those users who are returned as part of your custom user sync query.... Since the count is generated based on the following query, which takes the last update column into consideration:

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

the count "should" decrease as records age out.... Just in case, I ran a quick test, and confirmed that the LastUpdate column is not updated when the user is not returned by the custom sync query.

Can you check the WD_User table, and see how many rows there are which have an OU which does not match that specified in your custom user sync query (in the where clause).. Just trying to find the dependency. 

[NicOla]

Hi,

I checked the WD_user table. There now are 26,848 entries. 26, 844 of these are in the correct container. I used the UserOU column to verify that.

I had a look at the first ten users in that table. Seven out of that ten are no longer in the organisation and their records are no longer in SCCM. I understand that they should still be in the AppStore tables.

What is the SQL query "(SELECT Count( UniqueName) from WD_User where lastupdate > DATEADD(day, -7, GETDATE()) and UniqueName is not null " supposed to do, what users is it supposed to pick up?

Thanks again - in advance   🙂

[CharlesW · Accepted solution]

The query is supposed to count the number of uniqueUser names in the WD_user table where the users have been updated within the last 7 days. The users should only be updated if they are returned in your custom user sync query.. You mentioned that 7 out of 10 users are no longer in SCCM.. What is the lastUpdate column set to for these users? Is the lastUpdate column set to a date more than 7 days ago? If so, then these users should not be reflected in the license count.

[NicOla]

The error seems to have corrected itself. The red warning sign has disappeared and when I went to the General Tab under settings the correct (not inflated) number of licence was displayed.
We have never touched the original Custom Sync Queries that were set up for us.
I just re-ran the queries and there is a difference of 53 between the query used to calculate users and the custom sync query. Last week the difference was 4,476 (and we were getting the red warning message which has now disappeared).
I’m not sure what could have resolved this – maybe a server reboot, which would restart the App Portal Service?
Thanks for all the time you put into this, I’m just glad the warning will be removed now. I was told again by the Flexera support team in release 2020 R1 there will be better licence consumption calculations in place.

[jeffrey_weidle]

@CharlesW Hi Charles, 

Does this mean after updating my licensing collection and needing to delete the WD_User count so it's reflected correctly that I'll loose all old request data?

[jdempsey]

@jeffrey_weidle you won't lose your request data, but when viewing the request history under My Requests (with Show All Requests), you may notice some user information missing if the request is associated with a user that is no longer present in WD_User.  When looking at WD_PackageRequests in the database, you'll still be able to see the SAMAccountName (AD user name) of both the requester and the target user.

[jeffrey_weidle]

I guess my main concern isn't the ones that should be deleted, but once I rerun the sync to replace the licensed users in WD_User will the users be able to see their old "My Request" data after the sync?

Kind regards,

Jeffrey Weidle

[jeffrey_weidle]

Also, since we're having the user syncing discussion, I found this in the documentation and was never brought up before. Is there any correlation or significance to the this statement: IMPORTANT:If you are using a custom source for user syncing, be sure to set the Syncing Users property to Not Defined. ? Does it need to be set this way if a custom user sync scenario is being used?

[jdempsey]

---

@jeffrey_weidle wrote:

I guess my main concern isn't the ones that should be deleted, but once I rerun the sync to replace the licensed users in WD_User will the users be able to see their old "My Request" data after the sync?

---

As I stated, the request data is not deleted, so any users that are synced back to WD_User after the deletion will still see their old requests under My Requests.  The linkage is based on SAMAccountName (AD user name), not based on the ID number that is generated for the row in WD_User.

---

@jeffrey_weidle wrote:

Also, since we're having the user syncing discussion, I found this in the documentation and was never brought up before. Is there any correlation or significance to the this statement: IMPORTANT:If you are using a custom source for user syncing, be sure to set the Syncing Users property to Not Defined. ? Does it need to be set this way if a custom user sync scenario is being used?

---

The answer is sort of "it depends".  If I'm doing a custom sync query that's still going against the ConfigMgr database, I leave the source as ConfigMgr.  But if I'm making a query against a different data source, then I set it to Not Defined.  This setting primarily impacts the connection string that gets used, but there are also some slight differences in the back-end process the code uses to process the changes.  @CharlesW might be able to provide more detail (or correct me if I'm way off base).

[CharlesW]

@jdempsey - Your understanding is correct.. The setting simply controls the connection string being used.. If set to "not defined", then you must specify the connection string.. If set to Config Mgr, then App Portal will build the connection string automatically based on the deployment settings.