More FIM Powershell Module updates

More updates to the FIM Powershell Module for end-to-end flow rules. In case you're wondering, yes, I've asked the Codeplex project team to add me as a developer. :)

1. Fixed a bug with OSR-Expression export flows.
2. Added the following info to the output:
    a. Allow nulls
    b. Initial flow only
    c. Is existence test

Reference version:
FIM PowerShell Modules v2.1
http://fimpowershellmodule.codeplex.com/releases/view/98374

<#
  .SYNOPSIS 
  Gets the Export Attribute Flow Rules from Sync Server Configuration
  .DESCRIPTION
  Reads the server configuration from the XML files, and outputs the Export Attribute Flow rules as PSObjects
  .OUTPUTS
  PSObjects containing the synchronization server export attribute flow rules
  
  .EXAMPLE
  Get-ExportAttributeFlow -ServerConfigurationFolder "E:\sd\IAM\ITAuthorize\Source\Configuration\FimSync\ServerConfiguration"
#>
Function Get-ExportAttributeFlow
{
  Param
  (        
        [parameter(Mandatory=$false)]
            [String]
            [ValidateScript({Test-Path $_})]
            $ServerConfigurationFolder
  )
  End
  {        
           ### This is where the rules will be aggregated before we output them
            $rules = @()
            
            ### Export attribute flow rules are contained in the ma-data nodes of the MA*.XML files
            $maFiles = @(get-item (Join-Path $ServerConfigurationFolder "MA-*.xml"))
            
            
            foreach ($maFile in $maFiles)
            {
                  ### Get the MA Name and MA ID
                  $maName = (select-xml $maFile -XPath "//ma-data/name").Node.InnerText
               
                foreach($exportFlowSet in (Select-Xml -path $maFile -XPath "//export-flow-set" | select -ExpandProperty Node))
                {
                    $mvObjectType = $exportFlowSet.'mv-object-type'
                    $cdObjectType = $exportFlowSet.'cd-object-type'
                    
                    foreach($exportFlow in $exportFlowSet.'export-flow')
                    {
                        $cdAttribute = $exportFlow.'cd-attribute'
                        [bool]$allowNulls = $false
                              if ([bool]::TryParse($exportFlow.'suppress-deletions', [ref]$allowNulls))
                              {
                                    $allowNulls = -not $allowNulls
                              }
                        [string]$initialFlowOnly = $null
                        [string]$isExistenceTest = $null
                        
                        if ($exportFlow.'direct-mapping' -ne $null)
                        {
                        ###
                        ### Handle src-attribute that are intrinsic (<src-attribute intrinsic="true">object-id</src-attribute>)
                        ###
                        if ($exportFlow.'direct-mapping'.'src-attribute'.intrinsic)
                        {
                            $srcAttribute = "<{0}>" -F $exportFlow.'direct-mapping'.'src-attribute'.'#text'
                        }
                        else
                        {
                                $srcAttribute = $exportFlow.'direct-mapping'.'src-attribute'
                        }
                            
                            $rule = New-Object PSObject
                            $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'DIRECT'
                            $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName                
                            $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                            $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute
                                    $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $null
                                    $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls.ToString()
                                    $rule | Add-Member -MemberType noteproperty -name 'InitialFlowOnly' -value $initialFlowOnly
                                    $rule | Add-Member -MemberType noteproperty -name 'IsExistenceTest' -value $isExistenceTest
                            
                            $rules += $rule
                        }
                        elseif ($exportFlow.'scripted-mapping' -ne $null)
                        {                
                            $scriptContext = $exportFlow.'scripted-mapping'.'script-context'                           
                                    $srcAttributes = @()
                                    
                        ###
                        ### Handle src-attribute that are intrinsic (<src-attribute intrinsic="true">object-id</src-attribute>)
                        ###
                        $exportFlow.'scripted-mapping'.'src-attribute' | ForEach-Object {
                            if ($_.intrinsic)
                            {
                                $srcAttributes += "<{0}>" -F $_.'#text'
                            }
                            elseif ($_) # Do not add empty values.
                            {
                                    $srcAttributes += $_
                            }
                        }
                        # (Commented) Leave as collection.
                        #if ($srcAttributes.Count -eq 1)
                        #{
                        #    $srcAttributes = $srcAttributes -as [String]
                        #}
                                
                            $rule = New-Object PSObject
                            $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'SCRIPTED'
                            $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                                    $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttributes
                            $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute    
                            $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $scriptContext
                                    $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls.ToString()
                                    $rule | Add-Member -MemberType noteproperty -name 'InitialFlowOnly' -value $initialFlowOnly
                                    $rule | Add-Member -MemberType noteproperty -name 'IsExistenceTest' -value $isExistenceTest
                                            
                            $rules += $rule                        
                        }
                              elseif ($exportFlow.'sync-rule-mapping' -ne $null)
                              {
                                    $srcAttribute = $exportFlow.'sync-rule-mapping'.'src-attribute'
                        $initialFlowOnly = $exportFlow.'sync-rule-mapping'.'initial-flow-only'
                        $isExistenceTest = $exportFlow.'sync-rule-mapping'.'is-existence-test'
                                    if($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'direct')
                                    {
                                          $rule = New-Object PSObject
                                          $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Direct'
                                          $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                                          $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                                          $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                                                
                                          $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $null
                                          $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls.ToString()
                                    $rule | Add-Member -MemberType noteproperty -name 'InitialFlowOnly' -value $initialFlowOnly
                                    $rule | Add-Member -MemberType noteproperty -name 'IsExistenceTest' -value $isExistenceTest
                                                                  
                                          $rules += $rule             
                                    }
                                    elseif ($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'expression')
                                    {
                                          $scriptContext = $exportFlow.'sync-rule-mapping'.'sync-rule-value'.'export-flow'.InnerXml
                                          $cdAttribute = $exportFlow.'sync-rule-mapping'.'sync-rule-value'.'export-flow'.dest
                                          $rule = New-Object PSObject
                                          $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Expression'
                                          $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                                          $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                                          $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                                                
                                          $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $scriptContext
                                          $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls.ToString()
                                    $rule | Add-Member -MemberType noteproperty -name 'InitialFlowOnly' -value $initialFlowOnly
                                    $rule | Add-Member -MemberType noteproperty -name 'IsExistenceTest' -value $isExistenceTest
                                                                  
                                          $rules += $rule             
                                    }
                                    elseif ($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'constant')
                                    {                           
                                        $srcAttributes = @()
                                        $scriptContext = $exportFlow.'sync-rule-mapping'.'sync-rule-value'
                                          $rule = New-Object PSObject
                                          $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Constant'
                                          $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                                          $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttributes
                                          $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                                          $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                                                
                                          $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value "'$scriptContext'"
                                          $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls.ToString()
                                    $rule | Add-Member -MemberType noteproperty -name 'InitialFlowOnly' -value $initialFlowOnly
                                    $rule | Add-Member -MemberType noteproperty -name 'IsExistenceTest' -value $isExistenceTest
                                                                  
                                          $rules += $rule             
                                    }
                                    else
                                    {
                                          throw "Unsupported Export Flow type '$($exportFlow.'sync-rule-mapping'.'mapping-type')'"
                                    }
                             
                              }
                    }
                }
            }
            
            Write-Output $rules
  }#End
}
<#
  .SYNOPSIS 
  Gets the Joined Rules where the IAF rules are joined to the EAF rules based on the MV Attributes and Object Types
  .DESCRIPTION
  Reads the server configuration from the XML files, and outputs the Joined IAF and EAF Rules as PSObjects
  .OUTPUTS
  PSObjects containing the synchronization server attribute flow rules
  
  .EXAMPLE
  Join-ImportToExportAttributeFlow -ServerConfigurationFolder "E:\sd\IAM\ITAuthorize\Source\Configuration\FimSync\ServerConfiguration"
  
#>
Function Join-ImportToExportAttributeFlow
{
    [CmdletBinding()]
     Param
     (        
        [parameter(Mandatory=$false)]
            [String]
            [ValidateScript({Test-Path $_})]
            $ServerConfigurationFolder
     ) 
      End
      {
            ### Get the Import Attribute Flow Rules
            $IAF = Get-ImportAttributeFlow-ServerConfigurationFolder $ServerConfigurationFolder
            
            ### Get the Export Attribute Flow Rules
            $EAF = Get-ExportAttributeFlow-ServerConfigurationFolder $ServerConfigurationFolder
        
#        foreach ($eafTest in $EAF)
#        {
##            Write-Verbose "$(' ' + $eafTest.'MVAttribute' + ' ') $($eafTest.'MVAttribute'.GetType())"
##            Write-Verbose "$($eafTest.'MVAttribute' -join ',') $($eafTest.'MVAttribute'.GetType())"
#            if (($eafTest.'RuleType') -eq 'OSR-Expression')
#            {
#                Write-Host $eafTest
##                Write-Verbose "$eafTest $($eafTest.'MVAttribute'.GetType()) $($eafTest.'MVAttribute'.count) $($eafTest.'MVAttribute' -join ',')"
#            }
#        }
            ### This is where the rules will be aggregated before we output them
            $e2eFlowRules = @()
            foreach ($iafRule in $IAF)
            {
                ### Look for a corresponding EAF rule    
                $eafMatches = @($EAF | where {$_.'MVAttribute' -contains $iafRule.'MVAttribute' -and $_.'MVObjectType' -eq $iafRule.'MVObjectType'})
                  ### There may be multiple EAF rule for each IAF rules
                if ($eafMatches.count -gt 0)
                {
                    foreach($eafRule in $eafMatches)
                    {                        
                        $e2eFlowRuleProperties = @{            
                            'IAFRuleType'          = $iafRule.'RuleType'
                            'IAFSourceMA'          = $iafRule.'SourceMA'
                            'IAFCDObjectType'      = $iafRule.'CDObjectType'
                            'IAFCDAttribute'       = $iafRule.'CDAttribute'
                            'IAFScriptContext'     = $iafRule.'ScriptContext'
                            'IAFPrecedenceType'    = $iafRule.'PrecedenceType'
                            'IAFPrecedenceRank'    = $iafRule.'PrecedenceRank'
                            'MVObjectType'         = $iafRule.'MVObjectType'
                            'MVAttribute'          = $iafRule.'MVAttribute'
                            'EAFMVAttribute'       = $eafRule.'MVAttribute'
                            'EAFCDAttribute'       = $eafRule.'CDAttribute'
                            'EAFTargetMA'          = $eafRule.'MAName'
                            'EAFCDObjectType'      = $eafRule.'CDObjectType'
                            'EAFRuleType'          = $eafRule.'RuleType'
                            'EAFScriptContext'     = $eafRule.'ScriptContext'
                            'EAFAllowNulls'        = $eafRule.'AllowNulls'
                            'EAFInitialFlowOnly'   = $eafRule.'InitialFlowOnly'
                            'EAFIsExistenceTest'   = $eafRule.'IsExistenceTest'
                        }
                       
                    $e2eFlow = New-Object PSObject -Property $e2eFlowRuleProperties
                        $e2eFlowRules += $e2eFlow
                    }
                }
                  ### It is possible there are NO EAF rules for an IAF rule
                  ### here we stuff $null into the EAF side to make our output easy to consume for Out-GridView and Compare-Object
                  ### otherwise jagged objects seem to confuse things
                  ###
                  ### In this case the rule may be useless
                  ### Or the use of that MV attribute may not be visible here because some rules extension calls it (need to check the source code to confirm)
                else
                {
                   $e2eFlowRuleProperties = @{            
                            'IAFRuleType'          = $iafRule.'RuleType'
                            'IAFSourceMA'          = $iafRule.'SourceMA'
                            'IAFCDObjectType'      = $iafRule.'CDObjectType'
                            'IAFCDAttribute'       = $iafRule.'CDAttribute'
                            'IAFScriptContext'     = $iafRule.'ScriptContext'
                            'IAFPrecedenceType'    = $iafRule.'PrecedenceType'
                            'IAFPrecedenceRank'    = $iafRule.'PrecedenceRank'
                            'MVObjectType'         = $iafRule.'MVObjectType'
                            'MVAttribute'          = $iafRule.'MVAttribute'
                            'EAFMVAttribute'       = $null
                            'EAFCDAttribute'       = $null
                            'EAFTargetMA'          = $null
                            'EAFCDObjectType'      = $null
                            'EAFRuleType'          = $null
                            'EAFScriptContext'     = $null
                            'EAFAllowNulls'        = $null
                            'EAFInitialFlowOnly'   = $null
                            'EAFIsExistenceTest'   = $null
                        }
                        
                $e2eFlow = New-Object PSObject -Property $e2eFlowRuleProperties
                  $e2eFlowRules += $e2eFlow
                }
            }
        ### There's one more case in which the MV attribute is blank for an EAF.
        foreach ($eafRule in @($EAF | where {$_.'MVAttribute'.count -eq 0}))
        {
            Write-Verbose $eafRule
            $e2eFlowRuleProperties = @{            
                'IAFRuleType'          = $null
                'IAFSourceMA'          = $null
                'IAFCDObjectType'      = $null
                'IAFCDAttribute'       = $null
                'IAFScriptContext'     = $null
                'IAFPrecedenceType'    = $null
                'IAFPrecedenceRank'    = $null
                'MVObjectType'         = $null
                'MVAttribute'          = $null
                'EAFMVAttribute'       = $eafRule.'MVAttribute'
                'EAFCDAttribute'       = $eafRule.'CDAttribute'
                'EAFTargetMA'          = $eafRule.'MAName'
                'EAFCDObjectType'      = $eafRule.'CDObjectType'
                'EAFRuleType'          = $eafRule.'RuleType'
                'EAFScriptContext'     = $eafRule.'ScriptContext'
                'EAFAllowNulls'        = $eafRule.'AllowNulls'
                'EAFInitialFlowOnly'   = $eafRule.'InitialFlowOnly'
                'EAFIsExistenceTest'   = $eafRule.'IsExistenceTest'
            }
                        
            $e2eFlow = New-Object PSObject -Property $e2eFlowRuleProperties
            $e2eFlowRules += $e2eFlow
        }
            $e2eFlowRules | select `
            'IAFSourceMA',`
            'IAFCDObjectType',`
            'IAFCDAttribute',`
            'IAFRuleType',`
            'IAFScriptContext',`
            'IAFPrecedenceType',`
            'IAFPrecedenceRank',`
            'MVObjectType',`
            'MVAttribute',`
            'EAFMVAttribute',`
            'EAFTargetMA',`
            'EAFCDObjectType',`
            'EAFCDAttribute',`
            'EAFRuleType',`
            'EAFScriptContext',`
        'EAFAllowNulls',`
        'EAFInitialFlowOnly',`
        'EAFIsExistenceTest'`
      }
}

Convert "file time" varchar in hex format to datetime in T-SQL

I had a hard time finding an all-in-one conversion from a "file time" string to a datetime in T-SQL, so I pieced one together from several online hints.

To give you a little bit of background, I'm working with data that I dumped from an AD connector space using csexport, and attributes like accountExpires end up in a varchar column looking like this: 0x1ce2ea686b85800.  I needed a way, in T-SQL, to convert these to a datetime value.  I would have thought this would be something that's been done countless times before, but I couldn't quite find an all-in-one solution.

One quick note about this function.  It was enough for me to just convert to a date (year & day), as I mention in the comment, so please drop me a note if you add the timestamp to the conversion!

USE [FIMUtil]
GO

/****** Object: UserDefinedFunction [dbo].[FileTimeToDate]    Script Date:06/07/2013 15:19:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileTimeToDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FileTimeToDate]
GO

USE [FIMUtil]
GO

/****** Object: UserDefinedFunction [dbo].[FileTimeToDate]    Script Date:06/07/2013 15:19:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--=============================================
--Author:             Joe Zamora
-- Create date: 2013-06-07
-- Description: Convert a datetime value stored in hex format in a varchar type
-- to a datetime type. Currently only converts to the date (year & day) without
-- timestamp.
-- E.g. @FileTimeHex ='0x1CE2EA686B85800' or '1CE2EA686B85800'
--=============================================
CREATE FUNCTION [dbo].[FileTimeToDate] 
(
       -- Add the parameters for the function here
       @FileTimeHex varchar(50)
)
RETURNS datetime
AS
BEGIN
       -- Declare the return variable here
       DECLARE @Result datetime

       -- Add the T-SQL statements to compute the return value here
----DEBUG/TEST----
--declare @FileTimeHex varchar(50)
--set @FileTimeHex ='0x1CE2EA686B85800'
------------------
declare @LastLogin bigint
       ,@hexString varchar(50)
       ,@binValue varbinary(50)
-- Strip the leading '0x'
select @hexString =
       case when @FileTimeHex like '0x%' then substring(@FileTimeHex,3,18)
       else @FileTimeHex
       end
-- I guess we have to use a common length???
set @hexString = right('000000000000000000'+@hexString,16)
----DEBUG/TEST----
--select @hexString
--select convert(varbinary(50),@hexString,2)
------------------
set @binValue = convert(varbinary(50),@hexString,2)
----DEBUG/TEST----
--select @binValue
------------------
-- Bounds checking
set @LastLogin = null
select @LastLogin = 
       case when @binValue not in (0x0,0x7FFFFFFFFFFFFFFF) then @binValue
       end
----DEBUG/TEST----
--select @LastLogin
------------------
-- This formula taken from:
-- http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/3a6368c6-2580-4639-9604-a04cab08020d/
set @LastLogin = @LastLogin / (60 * 10000000)
set @LastLogin = @LastLogin / 1440
--set @LastLogin =@LastLogin -299
set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))

SELECT @Result = dateadd(day,@lastlogin,'1/1/1900')
----DEBUG/TEST----
--select @result
------------------

       -- Return the result of the function
       RETURN @Result

END

GO