Monday, July 22, 2013

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'

 

Sunday, July 14, 2013

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."

Tuesday, July 9, 2013

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
    )