There is currently no published schema available for the  App Broker database.  The following is an overview of some of the most commonly referenced tables.  These are often queried for reporting and troubleshooting purposes: WD_AppSettings – Stores the majority of the settings from the Admin tab WD_PackageRequests – used to store the data for requests which have been submitted. Likely the most referenced table. WD_WebPackages – Used to store information about catalog items which have been created. WD_SiteToAdvert – Used to store information about mappings between catalog items and SCCM collections/advertisements. WD_ApprovalProcess – Used to store approvers mappings for requests which have been submitted.. In general, the WD_ApprovalXXX tables store information about approvals.. Some of these are difficult to decipher. WD_ApproverStatus – Stores alternate approver mappings and whether or not an approver is out of office. WD_Actions – Stores mappings of actions to catalog items and the “events” which trigger them (on submit, on fail, etc…). WD_WebPackages – Stores web service actions which have been created.. Referenced by WD_Actions. WD_Profile – Contains information about users and their associated user GUID from AD WD_Localizations – Stores localized strings (including English) WD_Notifications – Stores notification text(email) WD_Languages – Stores languages that are currently enabled. WD_ITSM_XXX – Stores information about ITSM actions (Remedy, ServiceNow) WD_MailQueue- Stores information about notifications which have been sent, and which need to be sent.. Older data is purged automatically to prevent the table from growing too large. WD_ErrorLog – Stores errors. Same information as is typically written to log files. WD_MyAppsAlert – Stores information about alerts which have been created by MyApps.. This is a necessity for debugging many My Apps issues. WD_Question – Stores information about questions which have been created. WD_ResponseQuestionAuditTrail – Stores answers to questions in addition to changes to answers. WD_RequestTarget- Stores the “target” user/device for a request.. Tied to WD_PackageRequests table. WD_NodeSecurity – Stores Admin permissions for users accessing the site.. Generally, only referenced when there is a problem. A typical problem would consist of a “bad” GUID being stored in the table. See the ValidateSecurityGUIDs.asmx  web service to help identify. WD_CatalogSecurity – Stores catalog permissions. Sync related tables   – Populated during the nightly data sync from SCCM discovery data. WD_User - Users imported from SCCM WD_Computer - Computers imported from SCCM WD_UserComputerMap – mappings between users and computer imported from SCCM. In most cases, the built in reports  (those available under the Reporting tab) in App Broker will reference many of the above tables. The queries used by the built in reports are actually stored in a XML file named AdminStudio.Reports.xml.. This file can be found in the web/ReportDefinition folder under the App Broker install directory.. The queries in this file can be a handy reference for those looking to create their own custom queries and reports.       ... View more

Variables can play an important part when defining custom commands and web service actions.. In addition, they are frequently used in notifications. When editing a notification or a command there is a variable editor which will provide some of the variables which may be available.. The App Broker documentation lists a few of the variables which can be resolved, but it is by no means a complete list. The single best source for a list of variables can be found in the web service integration.asmx. This web service has a function named GetResolvedRequestVariables. You can invoke this web service by pasting http://localhost/esd/ws/integration.asmx into a browser on the App Broker server.. You will have to enter a RequestID for a request which has been submitted previously..  Once the invoke button is selected, a list of all available variables will be displayed in the browser, for the RequestID in question.. While the variables may vary somewhat between one request an the next, the list will be fairly static.. It should be noted that these variables will generally work with any command or notification.. There are exceptions to this rule, however.  Those notifications pertaining to My Apps Alerts (those starting with the name My Apps) are very limited when it comes to resolving variables. Typically, a notification pertaining to My Apps can only resolve those variables which are defined in the default notification.. They will not resolve any additional variables. Likewise, those variables which are defined in the My Apps Notifications can not be used elsewhere, or even in other My Apps notifications.  ADDITIONAL INFO: The following screen capture shows where the variable editor is located, when editing a notification (the editor is not obvious): The following links are also pertinent to variables in App Broker: Variables Reference Custom Variables ... View more

Symptoms:  One or more reclamation campaigns have been created, but software is not being reclaimed by the My Apps process.  Diagnosis: First, look at the WD_MyAppsAlert table. Are there any alerts (i.e. rows) recently created? If not, then be sure that the scheduled task named App Portal - Process Computers for My Apps Alerts exists and is running.. If the scheduled task runs, but completes very quickly, then try running the AppPortal.App.MyAppsExecutables.exe from an administrative command prompt, to see if alerts get processed. AppPortal.App.MyAppsExecutables.exe is located in the directory <InstallDir>/Utilities/MyApps.  When the scheduled task is run, or AppPortal.App.MyAppsExecutables.exe is invoked directly, you should see some type of activity logged in MyApps_ProcessAlerts.log. If there is still no activity logged, make sure that you have a reclamation campaign defined, and that “Enable License Reclamation Policy” has been selected for the campaign. If new alerts have been created recently, then look at the RequestID and packageID columns in the WD_MyAppsAlert table in the App Broker DB. Assuming that you have selected the “Automatically uninstall unused software without any user intervention” option for the Reclamation Campaign, you should see a value in both the RequestID and PackageID columns. If both of these columns are not populated, then it will typically indicate a problem. If the PackageID column does not have a value, then it will generally be caused by the following: The catalog item to which the reclamation campaign is tied (using the flexeraID), does not have an uninstall collection. The catalog item does not have the “Enable Uninstallation (remove from collection)” checkbox selected. There is more than one catalog item referencing the same FlexeraID, and one or more of the catalog items do not meet the above criteria. As a best practice, only a single catalog item should reference a given FlexeraID. An easy way to check the first two items is to simply look at the Has Uninstall Program column for the reclamation campaign, as shown below: If the RequestID column does not have a value, then this will generally be caused by the following: The target device for the alert is no longer active in SCCM.. Query the view v_R_System, in the Configuration Manager database to see if the target device can be found. The Advanced License Check is enabled for the catalog item to which the campaign is tied. The advanced license check will prevent the reclamation from being submitted, due to a known issue in App Broker. If the advanced license check can not be disabled, then contact support, as there is a hotfix available for both App Portal 2019R1 and App Portal 2020R1. The following shows the catalog item setting which enables the Advanced License Check: NOTE: Once an alert has been created, and if there was a problem processing the alert, then the alert will not be recreated again until the threshold period for the campaign has elapsed. As such, if a problem is discovered after alerts have been created, it is recommended that the affected alerts be deleted from the WD_MyAppsAlert table, and the scheduled task invoked again to recreate the alerts.   ... View more