topic Re: Appvolumes and PowerBi in App Volumes

Appvolumes and PowerBi

djbradp1 — Thu, 01 Dec 2022 15:22:42 GMT

Does anyone know which table contains the list of users to appstack assignments? (same list that shows under the 'appstack assignments' section under directory>users in the GUI)

I am building a view that shows list of licensed applications with costs per user in PowerBI by joining data between these tables using SQL.

List of appstacks: dbo.snapvols

List of applications: dbo.snapvol_apps

List of users: dbo.users

List of Licensed Applications with costs: Manually created table

The table (snapvol_app_assignments) I would expect this to be in is empty.

Re: Appvolumes and PowerBi

fabio1975 — Thu, 01 Dec 2022 22:34:42 GMT

Ciao

I took a look at my APPvolume test DB and I think it looks like this:

In the table dbo.app_assignment_entities I find the mapping:

user (target_id) --> assigning the application (app_assignment_id)

As you noticed in the dbo.users table I find my users, so from that table I associate the target_id (ID column of the table) to my user:

while in the dbo.app_assignments table I translate the application assignment id (app_assignment_id, in this table it is l’id, found in the dbo.app_assignment_entities table) into the actual application id (app_product_id)

I translate the defined ID of the application (app_product_id) to the name of the application in Table dbo_app_products

I give a practical example my user "Piccoli Brividi" has 3 applications associated so:

In the table dbo.app_assignment_entities I find in column target_id three times the value 2
in the dbo.users table I find that id 2 corresponds to "Piccoli Brividi"

at this point we translate the application, "Piccoli Brividi" has the apps_Assignment_id 9,15 and 16

In the dbo.app_assignments table I find that ID 9 is associated with app_product_id 5, 15 7 and 16 2
I go to the table dba_approducts and find out the name of the applications associated with my user "Piccoli Brividi"

App_product_id (id) 5 --> FoxitWin11

App_product_id (id) 7 --> IBM personal communication

App_product_id (id) 2 --> notepad++

and I correspond to what I see from GUI.

Re: Appvolumes and PowerBi

Lovetta65 — Sat, 03 Dec 2022 04:42:40 GMT

I'm especially stayed aware of the article and I will get many benefits from it. Subsequently, thank you for sharing it.

@djbradp1 myBayCare wrote:
Does anyone know which table contains the list of users to appstack assignments? (same list that shows under the 'appstack assignments' section under directory>users in the GUI)
I am building a view that shows list of licensed applications with costs per user in PowerBI by joining data between these tables using SQL.
List of appstacks: dbo.snapvols
List of applications: dbo.snapvol_apps
List of users: dbo.users
List of Licensed Applications with costs: Manually created table
The table (snapvol_app_assignments) I would expect this to be in is empty.

Re: Appvolumes and PowerBi

fabio1975 — Fri, 02 Dec 2022 09:21:47 GMT

ciao

I saw that in my answer post not all images are displayed...here a link with the same answer and images:

AppVolume DB – How to see User – Application association – BIOLNX (vmvirtual.blog)