Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scripts to save synchronization statistics (Pending Exports) to SQL
<#
.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
<#
.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)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.