Wednesday, October 30, 2019

MIM Portal Groups whose displayedOwner isn't among the Owners

In the MIM Portal it will create issues if you have a group whose displayedOwner isn't among the objects in the multivalued reference attribute Owner. Querying this through XPath is just about impossible so here is the SQL query to do it.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO​
USE FIMService​
GO​

SELECT DOwn.*
FROM (​
SELECT groupObjID = G.[objectID]
           , GroupDisplayName = GAOVS.ValueString
           , userDisplayName= UAOVS.ValueString
           , UserObjID =  U.[objectid]​
FROM [fim].[ObjectValueReference] GOVR​
JOIN [fim].[ObjectValueString] GAOVS​
        ON GOVR.ObjectKey = GAOVS.ObjectKey​
JOIN [fim].[Objects] G​
          ON G.ObjectKey = GOVR.ObjectKey​
JOIN [fim].[ObjectValueString] UAOVS​
           ON GOVR.ValueReference = UAOVS.ObjectKey​
JOIN [fim].[Objects] U​
          ON U.ObjectKey = GOVR.ValueReference​
WHERE GOVR.[AttributeKey] =65 -- DisplayedOwner​
     AND UAOVS.[AttributeKey] = 1 
    AND GAOVS.[AttributeKey] = 1 -- DisplayedName​
) DOwn​ --DisplayedOwners
LEFT JOIN​
(​
SELECT groupObjID = G.[objectID]
              , GroupDisplayName  = GAOVS.ValueString
             , userDisplayName = UAOVS.ValueString
             , UserObjID =  U.[objectid]​
FROM [fim].[ObjectValueReference] GOVR​
JOIN [fim].[ObjectValueString] GAOVS​
        ON GOVR.ObjectKey = GAOVS.ObjectKey​
JOIN [fim].[Objects] G​
         ON G.ObjectKey = GOVR.ObjectKey​
JOIN [fim].[ObjectValueString] UAOVS​
          ON GOVR.ValueReference = UAOVS.ObjectKey​
JOIN [fim].[Objects] U​
          ON U.ObjectKey = GOVR.ValueReference​
WHERE   GOVR.[AttributeKey] =138 -- Owner​
        AND UAOVS.[AttributeKey] = 1 
        AND GAOVS.[AttributeKey] = 1 --DisplayedName​
) Own​ -- Owners
On Down.gObjID = Own.gObjID 
    AND Down.UObjID = Own.UObjID​
WHERE Own.UObjID IS NULL​
order by DOwn.GacctName