How to get from the Sync-Rule-ID to the Sync Rule Resource ID

If you are looking at the XML export of the FIM synchronization config and you are trying to track down which sync rule is supplying a particular flow you just need to know which numbers lead you where.

For example:

<import-flows mv-attribute="accountName" type="ranked">
      <import-flow src-ma="{9686B319-E4BF-49C5-90C9-59054CCE3F92}"
                   cd-object-type="user" id="{210D4BB7-B886-4898-8361-7A232BBD65E8}">
        <sync-rule-mapping mapping-type="direct"
                           sync-rule-id="{B3E7157E-2EDA-E111-BCF5-005056000006}"
                           sync-rule-mapping-id="{52CF9D5C-33C8-4E1F-B56C-C77F7A9A1577}"
                           initial-flow-only="false" is-existence-test="false">
          <src-attribute>sAMAccountName</src-attribute>
        </sync-rule-mapping>
      </import-flow>
    </import-flows>

The key to finding the Sync rule is of course the Sync rule ID. However, this is not the resource ID that I can search for in the FIM Portal. Rather this is the metaverse ID.

From there I can open the list of connectors and for sync rules there should be one -- the FIM MA.

Then I can see the Distinguished Name of the Sync rule which is the Resource ID (aka ObjectID)

Then I can search in the FIM Portal by ResourceId and find the corresponding Synchronization rule:

Even better is to use the Join-ImportToExportAttributeFlow PowerShell commandlet originally created by Craig Martin http://fimpowershellmodule.codeplex.com/

Joe Zamora found and tweaked a minor bug in it: http://c--shark.blogspot.com/2013/03/one-small-addition-to-powershell-module.html be sure to use this fix.

Using the commandlet you can have a spreadsheet showing you the end to end attribute flow.

FIM Functions Updated, Bitwise Functions

In addition to the official reference for functions I thought I would update my examples from back in the ILM 2 Beta days

Function Name	BitAnd
Parameters	1) mask Type: Integer 2) flag Type: Integer
Description	BitAnd is a bitwise operation anding mask and flag. So if Flag is the UserAccountControl Attribute in AD and mask is -3 (the 64-bit two's complement of 2) Then the result is that the disable bit (bit 2) is turned off leaving all of the other bits unchanged. To figure out what mask to use to turn off a bit multiply that number by negative 1 and then subtract one. To turn off bit 2 and the userAccountControl with -3. To turn off bit 16 -- account is locked out and it with a -17.
Examples	BitAnd(-3, userAccountControl)  Turn off the disable bit Flow the result into userAccountControl in AD to enable a user.
	BitAnd(-3, 514) =512 if userAccountControl is 514 then the example gives us 512, which reactivates an account
	BitAnd(-3, 512) =512 if it is 512 then it remains unchanged. The account was already active
	BitAnd(-17, 528) =512 A locked out account (the bit in the 16's place was on) was unlocked
	BitAnd(-17, 512) =512 An account that was already unlocked was guaranteed to be unlocked
	Eq( BitAnd(2,userAccountControl),2) This is a test to see if an account in AD is currently disabled.  If so it will return a true otherwise it will return a false.

 

Function Name	BitOr
Parameters	1) mask Type: Integer 2) flag Type: Integer
Description	BitOr is a bitwise operation ORing mask and flag. So if Flag is the UserAccountControl Attribute in AD and mask is 2 Then the result is that the disable bit is turned on
Examples	BitOr(2, userAccountControl) Turn on the disable bit. Flow the result into userAccountControl in AD to disable a user.
	BitOr(2, 512) = 514 if userAccountControl is 512 then the example gives us 514.
	BitOr(2, 514) = 514 if it is 514 then it remains unchanged.

Hopefully this helps you in your codeless provisioning quest.

Remember there are limitations like the output of IIF can't feed into a function parameter expecting an Integer like the mask or the flag in BitAND or BitOR -- and no, I am not BitOr about it. Without casting and conversion functions that is an obstacle that can't be overcome using the FIM functions for that you may need to turn to custom workflows.

Insight Cloud SSO Solution and FIM Jumpstart offerings

I wrote an article for the Insight Newsletter about two of our new offerings.

Solving identity and access management for mid-sized business
By David Lundell, Sr. Manager, Identity and Security Practice
User productivity, IT budgets, and security and compliance all suffer from ineffective identity and access management. Insight has two new packages aimed at helping mid-sized businesses confront these challenges in the age of the cloud. Read more.

Secrets of the Metaverse Part 5

Other posts in the series:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?

First of all the FIM Product group does not support direct modification of the data in any of the FIM databases. Do so can leave your database in a state that is entirely unsupportable.

Second, the FIM Product group doesn't support direct queries against any of the FIM databases. However, it is possible to query the FIM Synchronization Service database without causing problems and without enormous gyrations. The FIM Service database has a very *interesting* data structure and is much more difficult to query.

So how do you safely query the FIM Synchronization Service database?

1) Use no lock hint

or

2) Set the transaction isolation level to read uncommitted.

Both of these tell SQL to not obtain locks on the records you are querying. This means that other threads such as the one that is synchronizing records don't get stuck waiting on your query. However this does also mean that some of the data you read could be in the middle of transaction, a transaction that could be rolled back. For example you query could catch it at the moment when a new MV object has just been projected from the HR MA but has not yet provisioned to AD. That transaction could succeed and commit or it could get rolled back and the MV project undone.

On to the how:

To use the no lock hint you must place it after every table and view name referenced in your query like this:

SELECT MetaverseObjectCount, ConnectorSpaceObjectCount, MetaDirectoryObjectCount = MetaverseObjectCount  + ConnectorSpaceObjectCount
FROM (select count(*) AS MetaverseObjectCount
    FROM MicrosoftIdentityIntegrationServer.dbo.[mms_metaverse] with (nolock)) as MVC
CROSS JOIN
(select  count(*) AS ConnectorSpaceObjectCount

FROM MicrosoftIdentityIntegrationServer.dbo.[mms_connectorspace] WITH (nolock) ) AS CSOC

To Set the transaction isolation level to read uncommitted you simply run that command and the keyword GO before your queries -- sometimes your reporting tools permit you to set that with a checkbox

set transaction isolation level read uncommitted
go

SELECT MetaverseObjectCount, ConnectorSpaceObjectCount, MetaDirectoryObjectCount = MetaverseObjectCount  + ConnectorSpaceObjectCount
FROM (select count(*) AS MetaverseObjectCount
    FROM MicrosoftIdentityIntegrationServer.dbo.[mms_metaverse] ) as MVC
CROSS JOIN
(select  count(*) AS ConnectorSpaceObjectCount

FROM MicrosoftIdentityIntegrationServer.dbo.[mms_connectorspace]  ) AS CSOC

One might ask: Can you do both? Yes you can and it will cause no harm. The one place you can use the set transaction isolation level command is in a view or stored procedure -- so inside of those objects you must use the WITH (nolock) hint

Secrets of the Metaverse Part 4

Parts 1-5:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?

Has access to the metaverse gotten faster with recent releases? Well I won't cover everything they have done but two really significant things:

1) Skinnier clustered index key for the mms_metaverse table:

2) Sequential numbering of the clustered index key

1) Skinnier clustered index key for the mms_metaverse table:

	Old	New
column used as the clustered Index key:	object_id	row_key
DataType:	uniqueIdentifier	big_int
Size:	16 bytes	8 bytes

The mms_metaverse table previously used the object_id column of type uniqueIdentifier (16 bytes) as the clustered index key. The product group added a new column called row_key of type big_int (8 bytes).

So how does this help?

Think of a database table like a book. The clustered index is the Table of Contents and the main text of the book, excluding the indexes in the back. There is only one order for the book to go in and that is the same with a table. In the book the key used in the table of contents is the page #. In a table we pick one or more columns to serve as the clustered index key.

The non-clustered indexes are like the indexes in the back of the book (a topical index, a place index, a person index) and they make include the clustered index key. So the topical index lists a subject and then the page(s) where it is found.

Clustered Index ~ Table of Contents

Clustered Index Key ~ page number

Non-Clustered Index ~ Topical Index at the back of the book

Non-Clustered Index Key ~ topic

Pretend we had to pad the page # so it looked like 00000000045?

Chapter 1 page 0000 0000 0000 0000 0000 0000 0001

Chapter 2 page 0000 0000 0000 0000 0000 0000 0093

Then the topical index would look like (the topical index includes the key from the table of contents -- the page number):

Beach 0000 0000 0000 0000 0000 0000 0006

Desert 0000 0000 0000 0000 0000 0000 0020

Suddenly this limits how many columns of the index I can print on a single page.

Both the table of contents and the index are much larger consuming more pages. It also takes more time to search through those pages for my topic.

By going to a skinnier clustered index key it is as though the product group changed the book to be like this:

Chapter 1 page 0001

Chapter 2 page 0093

and the index to this:

Beach 0006

Desert 0020

Suddenly the table of contents and the index take fewer pages and it is faster to leaf through them when searching. So it is with a database table reading the table and its indexes is now much faster.

2) Sequential numbering of the clustered index key

This new column has data populated automatically using the SQL Server identity feature (this feature allows new rows to be added with an incrementing counter). Previously new pages would be inserted in random order and sometimes that mean splitting pages.

So how does this help?

The use of an identity column to increment 1 by 1 as opposed to a randomly generated unique identifier is enormously helpful in writes and will lead to less fragmentation. Whereas prior to the change a new row could be inserted anywhere in the entire table, now it will be inserted at "the end."

Secrets of the Metaverse Part 3

Parts 1-5:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?

Many times people wonder how many attributes they can create in the Metaverse Designer tool.

The answer is confusing because ... it depends.

Per my calculations there is a hard limit for single-valued, non-reference attributes of 502. Now to show you my work (in school my math teachers always insisted that I show my work).

Remember from Part 2 that the Metaverse consists of 5 tables:

When you create a new single-valued, non-reference attribute using the Metaverse Designer tool FIM modifies not just data but the table structure of three of these tables: mms_metaverse, mms_metaverse_lineagedate, and mms_metaverse_lineageguid. FIM will add a column to each of these tables. The mms_metaverse table will get a column of a datatype  based on what has been selected as the attribute data type:

Metaverse DataType	SQL Data Type	Min Size	Max Size	Comments
String (Indexable)	nvarchar(448)	2	898
String (non-Indexable)	nvarchar(max)		2 GB	If the data is small (less than 4000 bytes or whatever limit is set) and doesn't cause the row to exceed the 8060 byte limit it will stored in the row, otherwise only pointers will be stored in the row and the data will be stored in its own set of pages (off-row).
Binary(indexable)	varbinary(900)	2	902
Binary(non-indexable)	varbinary(max)		2 GB	see the note on string non-indexable
Boolean	bit	1/8	1	If there are between 1-8 bit columns in the table 1 byte of storage is consumed, if 9-16 bits then 2 bytes, if 17-24 then 3 bytes and so on
Number	BigInt	8	8

In the mms_metaverse_lineagedate table FIM adds a new column of type DateTime which takes 8 bytes. In the mms_metaverse_lineageguid table FIM adds a column of type UniqueIdentifier which takes 16 bytes.

A row in SQL Server 2000 and beyond can only hold 8060 bytes. With SQL 2005 and beyond there is a feature called row overflow that allows for the variable length columns (like varchar) to overflow to other pages. However that doesn't apply to fixed length data types like BigInt, DateTime and UniqueIdentifier.

So the mms_metaverse_lineageguid has two columns (row_key a bigInt and object_id a unique identifier so 24 bytes) and then one column of type UniqueIdentifier for every single-valued non-reference attribute. 8060-24 = 8036 and 8036/16 = 502.25, which rounds down to 502.

So while there may be other limits that may depend on the datatype selected, this is one limit that cannot be escaped.

Secrets of the Metaverse Part 2

Parts 1-5:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?

Where and how is the Metaverse data stored?

Before I get into that I must caution you that modifying data directly will put you in a position that is unsupported by Microsoft. Even querying the data is something of a touchy issue (see Part 5).

The Metaverse consists of 5 tables in the FIM Synchronization Service Database:

Table	Comment
mms_metaverse	Every object in the metaverse has a row in this table. Single-Valued non-reference attributes are stored in this table
mms_metaverse_lineagedate	This table has a DateTime column of the same name of every attribute column in the mms_metaverse table (in other words -- single-valued non-reference attributes).
mms_metaverse_lineageguid	This table has a UniqueIdentifier column of the same name of every attribute column in the mms_metaverse table (in other words -- single-valued non-reference attributes).
mms_mv_link	Reference attributes (both single valued and multi-valued) are stored in this table in an Entity Attribute Value format. The references are kept as uniqueIdentifiers
mms_metaverse_multivalue	Non-Reference multi-valued attributes are stored in this table in an Entity Attribute Value format (with a column for each of the possible data types)