Scripts to save synchronization statistics (Pending Exports) to SQL

CreateSyncStatisticDB.ps1

<#
    .SYNOPSIS
        Script that creates SQL DB to store Pending Exports in MIM MAs into SQL for Statistical analysis.
#>

PARAM([string]$SQLServer = "dbFIMSync", [string]$DBName = "FIMSyncStatistics",[string]$ScheduleAccount="AD\svcMIMAdmin")

#region Modules
Import-Module LithnetMIISAutomation
Import-Module SqlServer
#endregion Module

#Let's do it in three steps first create the Database and then the Tables and finally Permissions
$Query = @"
USE [master]
GO
CREATE DATABASE [$DBName]
GO
"@
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query

#Create Exports Table
$Query = @"
USE [$DBName]
GO
CREATE TABLE [Exports](
	[ID] [bigint] NOT NULL IDENTITY(1,1),
    [LastExportDeltaTime] [datetime] NOT NULL,
    [MAID] [uniqueidentifier] NOT NULL,
	[MAName] [varchar](50) NOT NULL,
	[ObjectType] [varchar](50) NOT NULL,
    [ExportDeltaOperation] [varchar](50) NULL,
	[UnappliedExportDelta] [xml] NULL
)
GO
"@
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query

#Give permission
$Query = @"
USE [$DBName]
CREATE USER [$ScheduleAccount] FOR LOGIN [$ScheduleAccount] WITH DEFAULT_SCHEMA=[dbo] 
GO
ALTER ROLE [db_datareader] ADD MEMBER [$ScheduleAccount]
ALTER ROLE [db_datawriter] ADD MEMBER [$ScheduleAccount]
GO
"@
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query

SaveExportStatistic.ps1

<#
    .SYNOPSIS
        Script to manually add some pending exports to SyncStatistics DB.
        Used as an example on what to put in Schedule script to automate it.
        Example uses the SqlServer PS Module to save to SQL. 
        It will likely be better performance to use the built in SQL client in Windows.
        
        $conn = new-object "System.Data.SqlClient.SqlConnection" $connstring
        $conn.Open()
        $cmd = new-object "System.Data.SqlClient.SqlCommand"
        etc...

		Data is saved as [xml] in SQL and the ConvertTo-Xml commandlet have a Depth parameter that controls the level of detail you get about each exported object.
		For example Depth = 1 will only show that the member attribute changed on group, but not the values.
#>
PARAM([string]$SQLServer = "dbFIMSync", [string]$DBName = "FIMSyncStatistics",[string]$MAName,[int]$Depth = 1)

#region Modules
Import-Module LithnetMIISAutomation
Import-Module SqlServer
#endregion Modules

#region Functions
function InsertQuery{
	PARAM([string]$DBName,$ExportObject,$Depth = 1)
	
	$LastExportDeltaTime = $ExportObject.LastExportDeltaTime
	$MAID = $ExportObject.MAID
	$MAName = $ExportObject.MAName
	$ObjectType = $ExportObject.ObjectType
	$ExportDeltaOperation = $ExportObject.ExportDeltaOperation
	$UnappliedExportDelta = ($ExportObject.UnappliedExportDelta | ConvertTo-Xml -Depth $Depth).InnerXml
	
	$Query = @"
	USE [$DBName]
	GO
	INSERT INTO [dbo].[Exports]
           ([LastExportDeltaTime]
           ,[MAID]
           ,[MAName]
           ,[ObjectType]
           ,[ExportDeltaOperation]
           ,[UnappliedExportDelta])
     VALUES
           ('$LastExportDeltaTime',
           '$MAID',
           '$MAName',
           '$ObjectType',
           '$ExportDeltaOperation',
           '$UnappliedExportDelta')
"@
	
	return $Query
}
#endregion Functions

$PendingExports = Get-PendingExports -MA $MAName

if($PendingExports.Count -gt 0){#We don't want empty rows in the table
	foreach($Object in $PendingExports)
	{
		Invoke-Sqlcmd -ServerInstance $SQLServer -Query (InsertQuery -DBName $DBName -ExportObject $Object -Depth $Depth)
	}
}