Monday, November 9, 2015

FIM Custom Expressions inside Custom Expressions?

Recently, I needed to take Longitude and Latitude data that was given to me in the following format and break it into its individual components and then flow it out to AD.
Let's suppose the data looks like this:
"Point -10.1223 45.945"
I could just use the Left and Right functions to get out the Longitude and Latitude.

The problem was it could also look like this depending on the level of precision:
"Point -10.1223453333 45.945111113" 

So using the Left and Right functions were right out.

But I could use the Word function
Word(GeoData,2," ") gives me -10.1223 for the first row and -10.1223453333 for the 2nd row
Word(GeoData,3," ") gives me 45.945 for the first row and for the 2nd: 45.945111113

Suppose the data was slightly different a comma instead of space in between.
"Point -10.1223,45.945"
"Point -10.1223453333,45.945111113"

Now I can add another delimiter in addition to the space character I can put in a comma:
Word(GeoData,2," ,") gives me -10.1223 for the first row and -10.1223453333 for the second row
Word(GeoData,3," ,") gives me 45.945 for the first row and for the second: 45.945111113

Let's add another twist (which gets to how the data was really presented) and put the data inside parenthesis like so:
"Point (-10.1223 45.945)"
"Point (-10.1223453333 45.945111113)" 

But when I tried to do 
Word(GeoData,3," (")
I got an error "the function Word is not correctly formatted"
Then I had a brain storm! What about putting a CustomExpression inside a CustomExpression?
Bummer: "The function named CustomExpression could not be located" so no nesting of Custom Expressions!

So for giggles I decided to not start it with a customExpression

That worked!

So the lesson is that sometimes trying to use some of the special characters, like a parenthesis inside of a literal string confuses the CustomExpression parser but can work when put inside the parameter of the function call (not inside the Custom Expression)

I did indeed confirm that the parenthesis was the problem as it worked with other characters just not a literal parenthesis when doing a whole custom expression.

Wednesday, August 5, 2015

How many attributes can you have in the Metaverse?

Back in 2013 I published 5 posts about the Secrets of the Metaverse:

The third post was about how many attributes you can have in the Metaverse in which I said that the mms_metaverse_lineageguid table limits us to 502 single valued non-reference attributes in the Metaverse. This is still correct but a client told me of a scenario they encountered where the lineageguid table prevented them from getting to over 450 attributes and they encouraged me to blog about how they solved it.

The issue can occur when you delete attributes from the Metaverse and then try to add more. If you exceed 502 single valued non-reference attributes that have ever existed in your Metaverse you will encounter this error unless you take some very specific actions at the database level. WARNING: these actions should be done under the direction of Microsoft Support so that your installation can remain in a supported state.

The client had deleted a number of unused attributes prior to adding the many attributes they needed and then hit a brick wall getting the following error in their application event log:
 0x8023042e (the table cannot be created because the row size limit was exceeded.):SQL: ALTER TABLE [dbo].[mms_metaverse_lineageguid] ADD [theirAttribute] [uniqueidentifier] NULL 0x80230629 (the specified metaverse schema has too many attributes).

First: Why does this happen?
As you can see from the error message when you add or delete a single valued non-reference attribute to the Metaverse the Synchronization Service runs an ALTER TABLE statement to add or delete a column and as famed SQL MVP and author Kalen Delaney states running "ALTER TABLE will not reclaim space." Her article is about altering the length of column but "Database Whisperer" Michael J Swart provides an example of removing columns and shows that ALTER TABLE just makes a meta data level change. So even though the column is not used anymore it is still taking up space in the table until the Clustered Index is rebuilt.

You can see how close you are getting by using the following query (I used Michael's and Kalen's queries as starting points):

USE FIMSynchronization
SELECT AS [Column Name], column_id, leaf_offset, pc.is_dropped
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p
        ON p.partition_id = pc.partition_id
   LEFT JOIN sys.columns c
         ON column_id = partition_column_id
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('mms_metaverse_lineageguid')
ORDER BY pc.leaf_offset

Column Name Column_ID Leaf_Offset Is_Dropped
------------------ -------------   ---------        --------------
title                  99              1564           0
type                  100              1580           0
uid                  101              1596           0
NULL          NULL      1612           1
NULL          NULL      1628           1
testAttribute3  105              1644           0

In this example I have added three attributes and then deleted two of them (the first two I added). As you can see this leaves behind some open space in the mms_metaverse_LineageGuidtable and means that I would hit the limit sooner than I would expect.

If the biggest Leaf_Offset is 8044 then you are out of space to add more single value non-reference attributes (8044 byte offset+16 bytes =8060 bytes limit for a SQL row).

Normally, rebuilding the clustered index will reclaim the space for you. So you think no problem since you have followed my advice in FIM Best Practices Volume 1 and you use Ola Hallengren's scripts to automate index maintenance. However, the script will only rebuild if the index is more than 30% fragmented otherwise it will just reorganize it (which doesn't reclaim the space). So you could rebuild the clustered index by hand. Oops! The mms_metaverse_LineageGuid table doesn't have a clustered index -- so you have to add one. But then to return the database schema to its supported state you need to drop the clustered index. You can the clustered index on the object_ID column as this will be unique and not null. Then drop it.

 ONCE AGAIN: ONLY DO THIS UNDER THE DIRECTION OF MICROSOFT SUPPORT if you want to stay supported (and with all Syncs halted).

CREATE CLUSTERED INDEX [CX_mms_metaverse_lineageguid_object_id] ON dbo.mms_metaverse_lineageguid ( object_id)


DROP INDEX [CX_mms_metaverse_lineageguid_object_id] ON dbo.mms_metaverse_lineageguid

The creating or dropping of a clustered index forces the rebuilding of any non-clustered indexes. Unfortunately that means that this happens twice. But that can not be avoided. For a big table that can take a while (tens of minutes for metaverse with hundreds of thousands of rows). Of course the pure DBA would prefer to create an appropriate Cluster Index and leave it, rather than drop it, but that would put me out of support by the MIM product group.

When I rerun the query I get:

Column Name Column_ID Leaf_Offset Is_Dropped
------------------ -------------   ---------        --------------
title                  99              1564           0
type                  100              1580           0
uid                  101              1596           0
testAttribute3  105              1612           0

You can see that testAttribute3 is now offset at 1612 and the other columns are gone. Now that space is available to use for the lineageguids of attributes. 

So that is how you reclaim the space so you can add more attributes.

Tuesday, August 4, 2015

MIM 2016 is now available

MIM 2016 is now available

MIM -- Microsoft Identity Manager 2016 builds on and replaces Microsoft's Forefront Identity Manager 2010 R2.

On Microsoft's site they include an introductory (2 min) video about Hybrid Identity but don't mistake that for the MIM UI.

So has anything been removed?

No. While the list of deprecated features are still deprecated none of them have been removed from this new version.

So what's new?

The first thing to call your attention is the focus on Hybrid (Cloud + On Premise) Identity. MIM can still manage on premise but is now even better equipped to work with Microsoft's Identity Management pieces in the cloud.

PAM -- Privileged Account Management. You establish a secure Forest (a bastion forest) with MIM deployed there and you request temporary membership in a role that then puts you in a group in the bastion forest that grants you elevated privileges in your main forest(s). Microsoft's take on this is different than others such as CyberArk which vault your privileged passwords and change them frequently.

SSPR -- Self Service Password Reset can now use Azure MFA (Phone Factor)

Self Service Unlock -- Use the SSPR mechanisms to authenticate and then unlock your account without resetting the password. Useful when you change you password but forget to update the cached password on your phone.

Certificate Management -- Win 8.1 client, no need to join to the domain (if using ADFS), Virtual Smart card, claims, events for troubleshooting.

Thursday, July 2, 2015

Still an MVP but now DS MVP

I have been awarded the Microsoft Most Valuable Professional for a 9th time. I started off as an MIIS MVP (even though ILM had been released 4 months previous). Then I became an ILM MVP in 2008, then in 2010 it was FIM MVP (or was that 2011). Now with FIM changing to MIM and in an effort to reduce the administrative paperwork the Microsoft MVP team has every time MMS/MIIS/ILM/FIM/MIM changes names all FIM MVPs have become DS (Directory Services) MVPs. ;) Actually, they decided that there was enough overlap and dependency that it made sense to combine them. So now I am a Directory Services MVP

Thursday, May 28, 2015

Big Data needs Identity in order to Act

At the 2015 Identity Summit Scott McNeely declared "Big data without Identity is not actionable"

Let's discuss.

Pulling from Information Week and IBM the Top 6 use cases of Big Data are:
1. Big Data Exploration
2. 360 degree view of customer
3. Information Security and Intelligence
4. Operation Analysis of data from Internet of Things
5. Data warehouse Augmentation/Optimization
6. Big Data Efficiency play (break down silos)

Big Data use caseHow Identity makes Big Data actionable
360 degree view of customer How can you understand the customer from 360 degrees if you don't know who the customer is? The trick here is linking their social media info to their account info -- talk about resolving ambiguous joins!
Information Security and Intelligence If you plan to leverage social media to improve your fraud detection once again you must link that data to your customer's account. Without that you have nothing to detect let alone act on.
Operation Analysis of data from Internet of Things With more devices connected to the Internet you can get a whole lot more data from these devices -- picture the Nest Learning thermostat and the Power Meter data flowing in and being analyzed together to tell customers how to lower their usage or warn them to lower usage to stave off a blackout. I can almost hear a HAL like voice saying "Dave you lowered your thermostat -- I can't let you do that."
Data warehouse Augmentation/Optimization While Data warehousing is about aggregating data to look for trends you usually capture data at the transactional level and want to tie it to the customer hence all of those grocery store loyalty cards.
Big Data Efficiency play (break down silos) Information weekly's example about "A large global financial institution … wanted to move from next-day to same-day balance reporting for its corporate banking customers" again illustrates how identity is the thread that allows you to make meaning of the disparate data.
Big Data Exploration Since this use case is really about exploring your data to do one of the others it makes more sense to look at how Identity fits in to the other use cases
Conclusion: Scott is right. Without Identity there is no way to act on most of the big data results.

Thursday, April 30, 2015

FIM Sync Flow with ScreenShots and Code snippets

Years ago Brad Turner and I created a Flow Chart of FIM data flow with Screenshots and Code snippets. Some of the code examples are funny and it still says ILM rather than FIM. It also doesn't include filter based out bound filter-based sync rules that came with R2. Bearing those things in mind it still provides a good bit of value. Someday I will update it with the latest -- until then enjoy.

FIM Hotfix for PCNS to support 2012 R2 DC's

With the latest hotfix MSFT now supports running PCNS on Windows Server 2012 R2. FIM still should not be installed on Windows Server 2012 R2 (2012 yes, 2008 R2 yes, 2008 yes). Only PCNS can be installed on Windows Server 2012 R2. The hotfix article has a slight error indicating that it is ok to install FIM Sync Service on 2012 R2 if you have installed the hotfix PCNS on 2012 R2 -- not true (the article should get corrected soon). Be warned this update may break ECMA 1 and ECMA 2.0 based MA's. That is they may not run returning "stopped-extension-dll-load" There are workarounds published in the article.