I have a stored procedure that runs daily to product metrics for the 18 organizations currently hosted in my Directory Server (version 11),  I am trying to add a new measure of the number of   allocated and assigned application licenses for each organization.  I know I can look this up in the directory server but my data table feeds a PowerBI Reports that gives me a quick view of assigned and allocated resources by organization with the ability to review past dates.

For example the number of quick fields applications allocated and assigned.

Having trouble finding the right joins to get me this info from the LFDS database schema.

For example here is one sub query used for license assignment:

,'Assigned Full User' = (Select Count(*) 
                                                    From [LFDS-P01].[dbo].[directory_objects] DO
                                                    Inner Join [LFDS-P01].[dbo].[user_licenses] on DO.[sid]=[user_licenses].[sid]
                                                    WHERE [user_licenses].[type]= '9bba0d89-9a13-455f-ada9-83cf071d46b9'
                                                    AND DO.container_id = [directory_objects].[id])