Every now and then a FIM administrator runs into having to do some cleanup in the MetaVerse in FIM. It could be things like deleting objects or “nulling” attributes.
In this post I will show you how you can use a CleanUp MA to assist you in this task. The example I use is to delete all group objects that are missing the accountName attribute.
There are several ways of creating the CleanUp MA. My approach is to use a SQL MA that uses a SQL view of the MetaVerse to filter the objects you would like to “Clean Up”.
WARNING! Before you continue make sure all schedules are turned off and that you make a new backup of the FIMSynchronizationService database.
First let’s create the SQL view. The table you use in the view is the mms_metaverse and the “required” attributes you should use is the object_id and object_type. Object_id is the one you use to join and object_type is for defining the object_type column in the SQL MA. You then add columns and filters as needed to define the objects in the MV that you need to work with. In this example I take group objects where accountName is missing.
Then create a simple SQL MA using the view and create a join rule using the object_id attribute.
Configure run profiles for Full Import and Full Synchronization
Run Full Import and Full Sync to join the objects in the CleanUp MA to the objects in the MV.
In Metaverse Designer configure a new object deletion setting to delete group objects if they are disconnected from the CleanUp MA.
If you would like to propagate the deletion to another MA (or MA’s) you can temporary define the deprovisioning setting on that MA to delete objects when disconnected. In my example these groups has an error in the FIM Service MA since they lack the required accountName. These pending provisions will be deleted once the MV object is deleted.
To fire off the deletion just delete the connector space of the CleanUp MA. This will now disconnect and delete the MV object and, in my case, the pending errors in the FIM Service MA.
When done, remember to reset the object deletion rule and deprovisioning settings you changed to its prior setting.
The CleanUp MA can be kept for future needs. Just change the view as needed and do a Refresh Schema on the CleanUp MA to use it for other purposes when needed.
Nice idea Kent – my strong recommendation would be to add the ” with (nolock)” directive in your SQL view definition, so line 2 becomes “FROM dbo.mms_metaverse WITH (nolock)”. Safety first when querying the FIM metaverse directly like this.
Correct! Commonly this is used when doing “manual” runs. But better safe than sorry.
Thanks for the instructions Kent. Just checking is the object type “person” and in the Join Rule what is object_id mapped to in the metaverse?
Since the SQL view gets the object_id from the MetaVerse we just join back to the object_id in the MetaVerse. Making sure we have the same object in our cleanu view as we connect to in MV.
I’ve successfully removed 5,001 objects from the metaverse (FIM objects without accountName) using this MA.
This has speeded up the FIMMA Export run no end on my test FIM 2010 R2 server!
Thanks for this very useful post.
Thanks Kent,
After months of testing and development, my MV Db had got a little unruly.
Using this process I was able to clean up the MV and greatly reduce Export run times and errors!
Cheers!
Mark W
(Brisbane, Australia)
Pingback: Borrado de objetos fantasma del metaverso | Enterprise IT Blog
Pingback: Borrado de objetos fantasma del metaverso - Enterprise IT at Plain Concepts
Thanks a lot !! This is an amazing and safe way to get the MV cleared. !!!
Hi Kent – thanks for this article. I needed to clear a couple of dozen mail attribute values from selected metaverse objects without clearing the connector spaces of production MAs if I could avoid it – and ran into some multivalue and reference attributes to deal with. I thought I would post the SQL here to give readers visibility of this approach, noting that I am only reading the first of any set of multivalue attributes to achieve the desired result:
CREATE VIEW dbo.vwCleanUpMA AS
Select MV.object_id
,MV.object_type
,MV.sourceForest
,MV.sourceDN
,MV.sourceAnchor
–user
,MV.msExchUserCulture
,MV.msExchAssistantName
,MV.msExchHideFromAddressLists
,MV.msExchMasterAccountSid
,MV.targetAddress
,MV.msExchResourceDisplay
,MV.msExchRecipientTypeDetails
,MV.msExchRecipientDisplayType
,MV.msExchResourceCapacity
,(
SELECT TOP 1 string_value_indexable
FROM dbo.mms_metaverse_multivalue MMV with (nolock)
WHERE MMV.object_id = MV.object_id
AND MMV.attribute_name = ‘msExchResourceMetaData’
) AS msExchResourceMetaData
,(
SELECT TOP 1 string_value_indexable
FROM dbo.mms_metaverse_multivalue MMV with (nolock)
WHERE MMV.object_id = MV.object_id
AND MMV.attribute_name = ‘msExchResourceSearchProperties’
) AS msExchResourceSearchProperties
,MV.legacyExchangeDN
,MV.msRtcSipApplicationOptions
,MV.msRtcSipDeploymentLocator
,MV.msRtcSipLine
,MV.msRtcSipOptionFlags
,MV.msRtcSipOwnerUrn
,MV.msRtcSipPrimaryUserAddress
,MV.msRtcSipUserEnabled
,MV.msExchRequireAuthToSendTo
–group
,MV.msExchMailboxGuid
,(
SELECT TOP 1 binary_value_indexable
FROM dbo.mms_metaverse_multivalue MMV with (nolock)
WHERE /*MMV.object_id = MV.object_id
AND*/ MMV.attribute_name = ‘msExchMasterAccountHistory’
) AS msExchMasterAccountHistory
–contact
,(
SELECT TOP 1 reference_id
FROM dbo.mms_mv_link MVL with (nolock)
WHERE MVL.object_id = MV.object_id
AND MVL.attribute_name = ‘manager’
) AS manager
from dbo.mms_metaverse MV with (nolock)
where sourceForest in (‘NA’,’NAU’)
Thx Bob! Making queries against MV data to get MV and Reference data is not an easy task in the beginning. Takes a while before one understands the DB structure in Sync. I’v done similar against the FIMService DB, and that one is even more complex to make queries against.
Wow – 3 replies to the same post, Kent :).
Fast forward to November 2018 and I’m in the same boat again, but this time armed with my favourite Lithnet PS library … so I thought I’d share a non-SQL variant on the above approach … namely using PS to create a CSV file to do the same thing.
In my case I have the person Metaverse attribute “jobTitle” which is contaminated with data previously imported from an AD MA but for which there is no longer an IAF – and I need to remove these values, leaving only legitimate values contributed by the MA “”.
Here is my script to generate the CSV:
Import-Module LithnetMiisAutomation
$maHT = @{}
@(Get-ManagementAgent) | % {
$maHT.Add($_.Name, $_.ID)
}
$q1 = New-MVQuery -Attribute “jobTitle” -Operator IsPresent
$mvObjects = @(Get-MVObject -ObjectType “person” -Queries @($q1))
$mvSubset = $mvObjects.Where({$_.Attributes.jobTitle.Values.Where({$_.MAID -ne $maHT.(“”)})})
$mvCleanup = @()
$mvSubset | % {
$pso = New-Object PSObject
$pso | Add-Member -MemberType NoteProperty -Name “ID” -Value “{$($_.ID)}”.ToUpper()
$pso | Add-Member -MemberType NoteProperty -Name “jobTitle” -Value “to be deleted”
$mvCleanup += $pso
}
$mvCleanup | Export-Csv “C:\Temp\MVCleanup.csv” -Delimiter ‘,’ -NoTypeInformation
Creating the temporary MA is very similar to the SQL one … and instead of altering the object deletion rule, I simply overwrite whatever MV value there is already, and then it gets recalled by default when I delete the temporary MA and CS.
Looks like the MA name got removed from the above script when I replaced some identifying text with another name between < and >: symbols :(.
Hi Kent, Thanks for this, the CleanUp MA worked beautifully for me :), but I would like to schedule this as a weekly task to ensure the MV stays clean. Is there a way to delete the CS using Powershell?
Cheers Jeff.
Hi Jeff, As far as I know there is no current way of clearing a connector space using PowerShell. Check out https://github.com/lithnet/miis-powershell/wiki for scripting options in Synchronization Service.
IMHO… What you should focus on is not to run this “fix” but instead change your MIM configuration to avoid objects ending up in a state where you need to use the CleanUp MA. /Kent
Thanks Kent..good advice
Cheers Jeff
Pingback: Borrado de objetos fantasma del metaverso – Cloud Solutions