Monday, June 13, 2016

FIM Powershell Module: Remove/unset/clear a single-valued reference attribute

In the latest version of the FIM Powershell Module (2016-05-18), in order to remove/unset/clear a single-valued reference attribute, you're supposed to do this:

New-FimImportChange -Operation 'Replace' -AttributeName "Manager"

Note that you just don't supply the -AttributeValue paramter.  However, in my script, I don't want to perform the extra step of checking whether my value is present; so I'd like to do this:

New-FimImportChange -Operation 'Replace' -AttributeName "Manager" -AttributeValue "$newManager"

In order to do that, I had to make a small change to New-FimImportChange in FimPowerShellModule.psm1:

### Process the AttributeValue Parameter
if (!$AttributeValue)
    # Allow the caller to pass an empty AttributeValue to unset it, but DO NOT set the AttributeValue on the ImportChange object.
elseif ($AttributeValue -is [String])
    $importChange.AttributeValue = $AttributeValue
elseif ($AttributeValue -is [DateTime])

Wednesday, March 23, 2016

MIM metaverse SQL query - manager contributing MA

This is a sequel (no pun intended) to my old post, FIM metaverse SQL query - employeeID contributing MA.  Since 'manager' is a reference attribute, you need a slightly different query than for scalar attributes.

set transaction isolation level read uncommitted

       ,ma_mgr.ma_name as [manager MA]
FROM [FIMSynchronizationService].[dbo].[mms_mv_link] l
join [FIMSynchronizationService].dbo.mms_metaverse mv
on l.object_id = mv.object_id

left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_mgr
on cr_mgr.lineage_id = l.lineage_id
left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_mgr
on ma_mgr.ma_id = cr_mgr.ma_id

where object_type = 'person'
and l.attribute_name = 'manager'

Wednesday, March 2, 2016


I was looking for a way to replace all GUIDs in a Visual Studio solution, so I took the answer to this StackOverflow question (Replacing all GUIDs in a file with new GUIDs from the command line) and extended it so that the script keeps track of GUIDs that are referenced across multiple files. An example is shown in the header below.

    Replace all GUIDs in specified files under a root folder, carefully keeping track
    of how GUIDs are referenced in different files (e.g. Visual Studio solution).
    Loosely based on GuidSwap.ps1:
    Version:        1.0
    Author:         Joe Zamora (
    Creation Date:  2016-03-01
    Purpose/Change: Initial script development
    .\ReplaceGuids.ps1 "C:\Code\IDMware" -FileNamePatterns @("*.sln","*.csproj","*.cs") -Verbose -WhatIf

# Add common parameters to the script.
$global:WhatIf = $WhatIf.IsPresent

# Change directory to the location of this script.
$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
cd $dir
$ScriptName = $MyInvocation.MyCommand.Name

    Write-Host @"
Usage: $ScriptName  -RootFolder <rootfolder> [Options]

    -LogFolder <logfolder>                      Defaults to location of script.
    -FileNamePatterns @(*.ext1, *.ext2, ...)    Defaults to all files (*).
    -WhatIf                                     Test run without replacements.
    -Verbose                                    Standard Powershell flags.

if ($LogFolder -and !(Test-Path "$LogFolder" -PathType Container))
      Write-Host "No such folder: `"$LogFolder`""

    This code snippet gets all the files in $Path that contain the specified pattern.
    Based on this sample:
function Enumerate-FilesContainingPattern {
    $Path=(throw 'Path cannot be empty.')
    ,$Pattern=(throw 'Pattern cannot be empty.')
    $PathArray = @()
    if (!$FileNamePatterns) {
        $FileNamePatterns = @("*")

    ForEach ($FileNamePattern in $FileNamePatterns) {
        Get-ChildItem $Path -Recurse -Filter $FileNamePattern |
        Where-Object { $_.Attributes -ne "Directory"} |
        ForEach-Object {
            If (Get-Content $_.FullName | Select-String -Pattern $Pattern) {
                $PathArray += $_.FullName
} <# function Enumerate-FilesContainingPattern #>

# Timestamps and performance.
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()
$startTime = Get-Date
Write-Verbose @"

--- SCRIPT BEGIN $ScriptName $startTime ---


# Begin by finding all files under the root folder that contain a GUID pattern.
$GuidRegexPattern = "[a-fA-F0-9]{8}-([a-fA-F0-9]{4}-){3}[a-fA-F0-9]{12}"
$FileList = Enumerate-FilesContainingPattern $RootFolder $GuidRegexPattern $FileNamePatterns
$LogFilePrefix = "{0}-{1}" -f $ScriptName, $startTime.ToString("yyyy-MM-dd_HH-mm-ss")
$FileListLogFile = Join-Path $LogFolder "$LogFilePrefix-FileList.txt"
$FileList | ForEach-Object {$_ | Out-File $FileListLogFile -Append}
Write-Host "File list log file:`r`n$FileListLogFile"
cat $FileListLogFile | %{Write-Verbose $_}

# Next, do a read-only loop over the files and build a mapping table of old to new GUIDs.
$guidMap = @{}
foreach ($filePath in $FileList)
    $text = [string]::join([environment]::newline, (get-content -path $filePath))
    Foreach ($match in [regex]::matches($text, $GuidRegexPattern)) {
        $oldGuid = $match.Value.ToUpper()
        if (!$guidMap.ContainsKey($oldGuid)) {
            $newGuid = [System.Guid]::newguid().ToString().ToUpper()
            $guidMap[$oldGuid] = $newGuid

$GuidMapLogFile = Join-Path $LogFolder "$LogFilePrefix-GuidMap.csv"
"OldGuid,NewGuid" | Out-File $GuidMapLogFile
$guidMap.Keys | % { "$_,$($guidMap[$_])" | Out-File $GuidMapLogFile -Append }
Write-Host "GUID map log file:`r`n$GuidMapLogFile"
cat $GuidMapLogFile | %{Write-Verbose $_}

# Finally, do the search-and-replace.
foreach ($filePath in $FileList) {
    Write-Verbose "Processing $filePath"
    $newText = New-Object System.Text.StringBuilder
    cat $filePath | % {
        $original = $_
        $new = $_
        $isMatch = $false
        $matches = [regex]::Matches($new, $GuidRegexPattern)
        foreach ($match in $matches) {
            $isMatch = $true
            $new = $new -ireplace $match.Value, $guidMap[$match.Value.ToString().ToUpper()]
        $newText.AppendLine($new) | Out-Null
        if ($isMatch) {
            $msg = "Old: $original`r`nNew: $new"
            if ($global:WhatIf) {
                Write-Host "What if:`r`n$msg"
            } else {
                Write-Verbose "`r`n$msg"
    if (!$global:WhatIf) {
        $newText.ToString() | Set-Content $filePath

# Timestamps and performance.
$endTime = Get-Date
Write-Verbose @"

--- SCRIPT END $ScriptName $endTime ---

Total elapsed: $($stopWatch.Elapsed)

Thursday, February 11, 2016

MIM 2016 lab on Windows 7

Rebecca Croft's excellent walkthrough on setting up a FIM 2010 R2 lab on Windows 7 is still helpful for installing MIM 2016 on Windows 7.  Here are a few notes/differences from my experience.
Thanks again to Rebecca and best of luck to you readers!

CShark is now IDMware

Heads up; I've renamed my blog from 'CShark' to 'IDMware'.  Seems more appropriate, considering the amount of code I've written for identity projects over the years.  And I hope to publish more of that code to the community.  Depends on my work schedule, of course.  :)


Tuesday, January 26, 2016

Lessons learned - FIM_TemporalEventsJob

Here are some lessons learned while troubleshooting the titular SQL Agent job.  If you think to yourself, "Self, why would I ever need to care about this?" then you're either (a) lucky or (b) blissfully ignorant.  Don't worry though, you can blame FIM for your ignorance.  ;)  This is one of those features of FIM that won't actually tell you that something's wrong until you stumble upon the fact that, for example, your managers stopped getting notifications when contractor accounts expired.

So, where do you start troubleshooting such a problem?  Well, the keyword "expiration" may trigger thoughts of whether you remembered to renew your car's registration, but in FIMLand it should make you think, "temporal set."  And how are temporal events triggered?  Well, one way is when a datetime attribute on a user is updated and it falls into the scope of a temporal set.  But think about the other way that temporal events are triggered: the user's datetime attribute has been the same for several weeks, and now it's been long enough that the user now satisfies some expiration criteria.  If you've never had to consider how that happens, then I envy you!  But if you really want to know, or if you've read this far and feel like you can't turn back now, then I'll tell you that those events are triggered by a SQL Agent job that's installed with the FIM Service product.

If you run SQL Server Management Studio (SSMS) and connect to the database engine on the FIM SQL server, you can expand the SQL Server Agent -> Jobs branch, right click on FIM_TemporalEventsJob and select View History.  If you see all green checkmarks, then congratulations on keeping your set criteria simple enough for FIM to handle!  However, if you see red icons like below, then congratulations on giving FIM a challenge!  (But unfortunately you're going to have to fix it.)

Lesson 1: FIM_TemporalEventsJob SQL Agent job continues after error in step 1, but fails if error in step 2

The steps of the FIM_TemporalEventsJob SQL Agent job are shown below.  The first lesson I learned in this troubleshooting exercise is that, if the job encounters an error in step 1, the job still continues on to the next step.  However, if an error is encountered in step 2, the job is stopped and the last two steps aren't executed.  (And in case you're not SQL literate, the steps below all execute SQL stored procedures.)

Step 1
EXECUTE [fim].[TriggerTemporalEvents] @extendedOutput = 0
Step 2
EXECUTE [fim].[MaintainSets]
Step 3
EXECUTE [fim].[MaintainGroups]
Step 4
EXECUTE [fim].[OptimizeSetMembershipConditionsUsingPartitions]

Step 1 sample error

Here's an example of an error that you might see in step 1 of the job history.  If you've got a keen eye, you may ask yourself, "What the heck is a SetKey???"  Don't worry, keep reading... 

Executed as user: DOMAIN\SVC-SQLAgent. Reraised Error 50000, Level 16, State 1, Procedure TriggerTemporalEvents, Line 667, Message: <_x0040_failedTable SetKey="6062948" ErrorMsg="Reraised Error 50000, Level 16, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 8623, Level 16, State 1, Procedure ?, Line 2, Message: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."/> [SQLSTATE 42000] (Error 50000).  The step failed.

Step 2 sample error

Here's a similar error that you might see in step 2 of the job history.  This is essentially the same error, but it occurred in a different stored procedure.

Executed as user: DOMAIN\SVC-SQLAgent. Reraised Error 50000, Level 16, State 1, Procedure MaintainSets, Line 556, Message: <Failures><_x0040_failedSetCorrections SetKey="6062948" ErrorMessage="Reraised Error 8623, Level 16, State 1, Procedure ?, Line 2, Message: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."/></Failures> [SQLSTATE 42000] (Error 50000).  The step failed.

Lesson 2: Step 2 (fim.MaintainSets) will continue after individual errors

Okay, we've learned that the job will continue after errors in step 1 but not step 2.  But what about the substeps within each step?  I.e., if an error occurs while evaluating one set, will it stop or continue to evaluate the rest of the sets?  The answer is that it will evaluate *all* sets, even if one set causes an error.

To prove that to myself, I added some print statements to the fim.MaintainSets stored procedure and ran it manually...

Sample of adding print statements to fim.MaintainSets procedure

SUCCESS: @setKey                         = 2732
SUCCESS: @setKey                         = 2733
SUCCESS: @setKey                         = 2734
SUCCESS: @setKey                         = 6062663
SUCCESS: @setKey                         = 6062802
SUCCESS: @setKey                         = 6062814
FAIL: @setKey                         = 6062948
SUCCESS: @setKey                         = 6063066
SUCCESS: @setKey                         = 7185483
SUCCESS: @setKey                         = 7185525
SUCCESS: @setKey                         = 10711150
SUCCESS: @setKey                         = 11024517
SUCCESS: @setKey                         = 11596415

Lesson 3: Find problematic set

Okay, so how do you find which set is causing the problem?  In the error message above we saw a "SetKey," but what the heck do you do with that?  That's a great question, because all identifiers in the FIM Service are GUIDs.  Well, the FIM product group certainly did you no favors here.  In order to find the problematic set, you have to run a SQL query against the FIMService database:

FROM [fim].[Set] s
join fim.[Objects] o on s.SetKey = o.ObjectKey
        ValueString as DisplayName
    FROM fim.ObjectValueString ovs
    join fim.AttributeInternal ai on ai.[Key] = ovs.AttributeKey
    WHERE Name = 'DisplayName'
    and ovs.ObjectKey = o.ObjectKey
) x
where s.SetKey = '6062948'

Et voila!  Here's your problematic set!

Contractor Extended Date 56 days without login

"Great!  Thanks Joe!  ...  Wait, how do I fix it???"  Well, this is out of scope of this blog post, but here are a few web resources to help you.  Good luck!