Querying ERE's from the fimservice database

I occasionally review the EREs floating about the system - the expected rule entries used to put objects in scope of traditional OSR sync rules - either to try and identity and clean-up orphans or just provide some stats.

There are two easy ways listed below to get this from the fimservice database - the first is the quick/dirty way, the second is the proper but very slow way.

This information is also available in the sync engine, but as they originate from the fim service it seems better to query from there.

/* ERE count from the MIM database, just found some object types and keys that looked correct. SQL takes seconds, PowerShell takes approximately forever with the FIMAutomation snap-in */

SELECT OVS.ValueString, Count(OVS.ValueString) 
FROM Fim.Objects OBJ
inner join fim.ObjectValueString OVS on OBJ.ObjectKey = OVS.ObjectKey
WHERE OBJ.ObjectTypeKey = 11
and OVS.AttributeKey = 66
group by OVS.ValueString

The slow (but supported) method, using the FIMAutomation snap-in

# Add the FIMAutomation snap-in
Add-pssnapin fimautomation

# The URI of your fim service endpoint
$uri = "http://fim01:5725/ResourceManagementService"

# Construct an output file based on today's date
$outputFile = "c:\temp\EREs_$([DateTime]::Now.ToString("yyyyMMdd")).csv" 

# All ERE's
$filter = "/ExpectedRuleEntry"

# Filter by a specific starts-with wildcard
#$filter = "/ExpectedRuleEntry[starts-with(DisplayName, 'AD: ')]"

# Filter by a specific sync rule name
#$filter = "/ExpectedRuleEntry[DisplayName = 'AD: CORP Inbound/Outbound User']"

# Export based on the filter
$objects = Export-FIMConfig -uri $URI -onlyBaseResources -customConfig $filter

# Group by displayname for a count per-sync rule
$objects.ResourceManagementObject.ResourceManagementAttributes | where {$_.attributename -eq 'DisplayName'} | select value | group-object -prop value | select count,name | ft -wrap -auto

# Export some details to CSV
$results = foreach ($object in $objects) {
  write-output "out" | select @{N='DisplayName';E={($object.ResourceManagementObject.ResourceManagementAttributes | where {$_.attributename -eq 'displayName'}).Value}},
                          @{N='CreatedTime';E={($object.ResourceManagementObject.ResourceManagementAttributes | where {$_.attributename -eq 'CreatedTime'}).Value}},
                          @{N='ObjectID';E={($object.ResourceManagementObject.ResourceManagementAttributes | where {$_.attributename -eq 'ObjectID'}).Value}} 
}
$results | export-csv -path $outputFile 

Wayne's World of IT (WWoIT).