AppPortal database schema

brinda_arjunan asked a question.

September 9, 2019 at 12:11 PM

Can you please share AppPortal Database schema.

App Broker

CharlesW (Flexera Software)
7 years ago
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.
Expand Post
- brinda_arjunan
  7 years ago
  Thank you for sharing this info!
  Expand Post
  - CharlesW (Flexera Software)
    7 years ago
    No problem. If you have anything specific you are looking for, ask it in this post and I'll try to provide an answer..
    Expand Post
    brinda_arjunan
    7 years ago
    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.
    Expand Post
    CharlesW (Flexera Software)
    7 years ago
    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...
    Expand Post
    jdempsey (Flexera Software)
    7 years ago
    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 apJOIN WD_PackageRequests pr ON ap.RequestID_FK = pr.RequestIDLEFT JOIN WD_Profile p ON ap.ADGUID = p.ADGUIDLEFT JOIN WD_User u ON p.UniqueUserName = u.UniqueNameWHERE IsCurrentApprover = 1ORDER BY pr.OrderNo, ap.RequestID_FK, p.UniqueUserName, ap.GroupIndex
    Expand Post

Related Product Forums

→ Flexera One

→ Snow Atlas

→ FlexNet Manager

→ Snow License Manager

→ App Broker

Need help finding an answer?

Ask a Question →