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

SELECT TOP 1000
       mv.object_type
       ,mv.accountName
       ,mv.domain
       ,l.attribute_name
       ,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

ReplaceGuids.ps1

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.

<#
    .Synopsis
    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:
    http://stackoverflow.com/questions/2201740/replacing-all-guids-in-a-file-with-new-guids-from-the-command-line
   
    .NOTES
    Version:        1.0
    Author:         Joe Zamora (blog.idmware.com)
    Creation Date:  2016-03-01
    Purpose/Change: Initial script development
 
    .EXAMPLE
    .\ReplaceGuids.ps1 "C:\Code\IDMware" -FileNamePatterns @("*.sln","*.csproj","*.cs") -Verbose -WhatIf
#>

# Add common parameters to the script.
[CmdletBinding()]
param(
    $RootFolder
    ,$LogFolder='.'
    ,[String[]]$FileNamePatterns
    ,[switch]$WhatIf
)
$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

If(!($RootFolder))
{
    Write-Host @"
Usage: $ScriptName  -RootFolder <rootfolder> [Options]

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

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

<#
    .Synopsis
    This code snippet gets all the files in $Path that contain the specified pattern.
    Based on this sample:
    http://www.adminarsenal.com/admin-arsenal-blog/powershell-searching-through-files-for-matching-strings/
#>
function Enumerate-FilesContainingPattern {
[CmdletBinding()]
param(
    $Path=(throw 'Path cannot be empty.')
    ,$Pattern=(throw 'Pattern cannot be empty.')
    ,[String[]]$FileNamePatterns=$null
)
    $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
            }
        }
    }
    $PathArray
} <# 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)
"@