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

App Portal Database Schema Question

We are trying to find out the name of the AD Group(s) per package.  I have this query but it is giving me a GUID for the AD Group.  How do I get the AD Group name?  I can't find the table or view to find that information.

SELECT T0.*, T1.PackageTitle, T1.PackageVisible, T1.AddtoGroupAction
FROM [AppPortal].[dbo].[WD_PackageSecurity] T0
JOIN WD_WebPackages T1 ON T1.PackageID = T0.PackageID_FK
Order by PackageID_FK

Thank you!

(1) Solution

Try the attached script.  It will return a list of all non-archived catalog items with their Package ID, Title, and Group Name (written to CSV).  If you only want catalog items that have an AD group associated, you should be able to just remove the "LEFT" from the SQL query.  Before running the script, you'll need to edit the config file to update your connection string and domain controller.

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

(4) Replies

You won't find anything in the database other than the GUIDs.  These are AD GUIDs, so take a look at /ESD/ws/integration.asmx?op=GetGroupForADGuid on your App Broker server.  You can call that SOAP web method to translate the GUID into a group name (through AD lookup).

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 @jdempsey.  I can look up the GroupGUID individually, is there a way to get them all?  Management wants a report of all packages and the AD group.  We have hundreds.

Try the attached script.  It will return a list of all non-archived catalog items with their Package ID, Title, and Group Name (written to CSV).  If you only want catalog items that have an AD group associated, you should be able to just remove the "LEFT" from the SQL query.  Before running the script, you'll need to edit the config file to update your connection string and domain controller.

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 @jdempsey, that worked great!   I appreciate you providing that script. 😊