I just started working with FIM 2010 R2 in a development environment. Before you read any further, please take this into consideration. All of this has come from a development environment. I would never do this in a production environment. I would probably go as far as to say that my level of fear and caution for “Hey, what does this button do?” type of scenarios is probably a little too low.
With that understanding, let’s get to the point. I was setting up a typical scenario with a SQL MA and and Active Directory MA. The SQL Database has approximately 50,000 entries that I needed to pull into the metaverse. I like to learn by doing. Yeah, I read a little bit before I started, but clearly not enough. I was still trying to figure out if I was going to use classic rules or if I was going to need to use the FIM portal to create Synchronization rules. After several imports, exports, and syncs, and then messing with Sync rules, I finally decided that I actually did need to use the FIM portal and Synchronization rules. I wanted to start with a completely clean slate. So I went in and deleted everything, including MA’s.
The problem came up when I deleted the FIM service Management Agent connector space. No matter what I did, every time I did an import, it would pull in about 150,000 Expected Rule Entries, or ERE’s.
Poking around in the FIM Script Box, I found some PowerShell code that could delete these for me. Basically, it does a search for all the orphaned ERE’s using the FIM web service under the hood of the “Export-FIMConfig” PowerShell cmdlet. Well this is great if you are looking at a couple 100 objects. But with 150,000 objects, I was looking at more like days.
So I decided to poking around a bit more. I figured that at the end of the day, these objects have to be somewhere in the database. If you are getting worried now, don’t. I promise it gets better. Looking at the FIMService database, I found one table that was particularly interesting. There is a FIM.objects table. Sweet!
Here’s where it got a little nuts. Looking in the connector space, I found the object ID of ONE of my orphaned ERE’s. It had an objectID of something like “B8A72DEB-6A7F-482F-81A6-8DD66D91D6EA.”
So I ran a quick query like the following
SELECT * FROM Fim.Objects
WHERE ObjectID = 'B8A72DEB-6A7F-482F-81A6-8DD66D91D6EA'
Here I found out that it had an ObjectType of 11. Looking at the table, it was referencing the ObjectTypeInternal table.
So the next thing I did was run a similar query and found “Astonishingly!” that there were about 150,000 ERE objects with ObjectType = 11.
Using this query, I was able to find all the ObjectKey’s of all the objects I needed to delete.
SELECT * FROM Fim.Objects
WHERE ObjectTypeKey = 11
Now I needed a way to delete these suckers. Looking over the database, I was wondering if there might be some stored procedure I might be able to use.
Low and Behold, sitting there, was something called debug.DeleteObject
Nevermind the “Debug” prefix on this stored procedure. Using a little Excel Magic, I created a huge long SQL command (What can I say, I am not a SQL guy) that looked something like this. I basically took the ObjectKey column from the last query, dumped it in excel, and then used some string manipulation to generate my SQL script.
Here’s what eventually what went into SQL Query Analyzer – actual code was 150,000 calls.
Running this only took about a little over 2 hours. Way faster than using the Import-FimConfig and Export-FIMConfig. Using the web service abstraction layer is great and all, but sometimes you just need to bypass as much as possible.
So the next time you are working in a development environment and feel like taking a chance on blowing off your toe, or maybe your whole foot, this just might help!