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

AppPortal database schema

Can you please share AppPortal Database schema. 

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

Brinda,
Unfortunately, there is no published schema available for App Portal. Not sure if you are interested, but the following is overview of some of the most commonly referenced tables.

  • 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 weather or not 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 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 we service I sent yesterday 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.

Thank you for sharing this info!

No problem. If you have anything specific you are looking for, ask it in this post and I'll try to provide an answer..
Hi Charles,

I am trying to query SQL DB for getting pending approval requests , like approver name, levels. for gettting this info i would require different tables joined, so having a doc with reference to tables and fields will be helpful.
I know that you have already seen a query similar to the following before.. This will give you pending requests for a particular date range, the user name (if approver is in the WD_profile table), and the users position in the approval process.

Select requestID, p.UniqueUserName, ap.Position
from wd_ApprovalProcess ap
INNER join vMyRequestsCatalog pr on ap.requestid_fk = pr.requestid
left join wD_profile p on ap.ADGUID = p.ADGUID
WHERE Authorized = 0 AND Source = 0 AND Cancelled = 0
AND [DateTime] BETWEEN '2016-01-09 00:00:00' AND '2019-06-13 00:00:00'


For the most part, the WD_ApprovalProcess table will give you information about the approval process which is set for a request... I might also suggest that you look at the file C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml. This is where the queries for the standard reports that App Portal provides are stored. This might give you some ideas on how to build your queries. Another option would be to use SQL profiler to capture SQL queries being run against the App Portal DB.. I use this approach frequently if I want to see what is happening.. Of course, it is usually better to do this against a Dev/Test environment.. Much less noise...

Here is a sample query that returns Order Number, Request ID, Current Approver, and Current Approval Level.  Feel free to modify as desired to suit your needs.

SELECT pr.OrderNo AS [Order Number],
       ap.RequestID_FK AS [Request ID],
       u.Email + ' (' + p.ADGUID + ')' AS [Current Approver],
       ap.GroupIndex AS [Current Approval Level]
FROM WD_ApprovalProcess ap
JOIN WD_PackageRequests pr ON ap.RequestID_FK = pr.RequestID
LEFT JOIN WD_Profile p ON ap.ADGUID = p.ADGUID
LEFT JOIN WD_User u ON p.UniqueUserName = u.UniqueName
WHERE IsCurrentApprover = 1
ORDER BY pr.OrderNo, ap.RequestID_FK, p.UniqueUserName, ap.GroupIndex
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".