FIM metaverse SQL query: expected rules list

 

set transaction isolation level read uncommitted

 

select

     mvpers.displayName as [User Display Name]

     ,mvpers.accountName as [User Account Name]

    ,mvere.displayName as [ERE Display Name]

    ,mvere.object_type

    ,link.*

from dbo.mms_mv_link link

join dbo.mms_metaverse mvpers

on link.object_id = mvpers.object_id

full outer join dbo.mms_metaverse mvere

on reference_id = mvere.object_id

where attribute_name = 'expectedRulesList'

 

Outbound System Scoping Filter: "Contains" doesn't work on multi-valued attributes (and why no "Is Present"?)

I was excited to finally have a scenario in which I could use the new Outbound System Scoping Filter, but sadly its limitations put the kibosh on that idea.  I was surprised to discover (empirically, of course) that you cannot use a "contains" operator on a multi-valued attribute.  What's more, there is no "is present" operator at all.  Anyone else frustrated by FIM's lack of consistency? </rant>

Anyway, here are the tests that I ran, in case you're interested:

Test	Scoping filter	Result
Single-valued attribute equals	employeeID EQUAL 100000	OK
Multi-valued attribute contains string	Roles CONTAINS MyRoleName	FAIL (Not Applied)
Attempt at multi-valued attribute contains string	Roles STARTSWITH MyRoleName	FAIL (Not Applied)
Another attempt at multi-valued attribute contains string	Roles EQUAL MyRoleName	FAIL (Not Applied)
Desperate attempt at multi-valued attribute contains string	Roles STARTSWITH %MyRoleName%	FAIL (Not Applied)
Attempt at "Is Present"	employeeID NOTEQUAL NO SUCH EMPID	FAIL (Applied when employeeID is not present)
Another attempt at "Is Present"	employeeID STARTSWITH %	FAIL (Not Applied)
Desperate attempt at "Is Present"	employeeID GREATERTHAN 0	SUCCESS 100000: Applied A100000: Applied <empty>: Not Applied

And here's the explanation from Andreas on the forum:

"That is not supposed to work. The scoping filter is only intended for string values. It isn't documented because I didn't think of documenting it."

Pasted from <http://social.technet.microsoft.com/Forums/office/en-US/b2a6f056-313d-4c28-946a-ff86c314a7f6/outbound-system-scoping-filter-not-working-with-multivalued-attribute>

FIM metaverse SQL query - employeeID contributing MA

set transaction isolation level read uncommitted

SELECT TOP 1000

    --lg.[object_id]

    mv.object_type

      --,lg.[displayName]

      --,lg.[employeeID]

      --,lg.[HR_USERID]

      --,lg.[HR_Joined]

    ,mv.employeeID as [employeeID]

    ,ma_empid.ma_name as [employeeID MA]

    ,mv.displayName as displayName

    ,ma_disp.ma_name as [displayName MA]

    ,mv.HR_USERID as HR_USERID

    ,ma_uid.ma_name as [HR_USERID MA]

    ,mv.HR_Joined as HR_Joined

    ,ma_hrj.ma_name as [HR_Joined MA]

  FROM [FIMSynchronizationService].[dbo].[mms_metaverse_lineageguid] lg

  join [FIMSynchronizationService].dbo.mms_metaverse mv

  on lg.object_id = mv.object_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_empid

  on cr_empid.lineage_id = lg.employeeID

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_empid

  on ma_empid.ma_id = cr_empid.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_disp

  on cr_disp.lineage_id = lg.displayName

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_disp

  on ma_disp.ma_id = cr_disp.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_uid

  on cr_uid.lineage_id = lg.HR_USERID

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_uid

  on ma_uid.ma_id = cr_uid.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_hrj

  on cr_hrj.lineage_id = lg.HR_Joined

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_hrj

  on ma_hrj.ma_id = cr_hrj.ma_id

  where object_type = 'person'

  and (

        ma_empid.ma_name = 'FIM MA'

        or mv.employeeID is null

        --or mv.HR_Joined is null

    )