DSCPullServerAdmin.psm1

enum DSCDatabaseTable {
    Devices
    RegistrationData
    StatusReport
}

enum DSCPullServerConnectionType {
    SQL
    ESE
    MDB
}

class DSCPullServerConnection {
    hidden [DSCPullServerConnectionType] $_Type

    [uint16] $Index

    [bool] $Active

    DSCPullServerConnection ([DSCPullServerConnectionType]$Type) {
        $this._Type = $Type
        $this | Add-Member -MemberType ScriptProperty -Name Type -Value {
            return $this._Type
        } -SecondValue {
            Write-Warning 'This is a readonly property!'
        }
    }
}

class DSCPullServerESEConnection : DSCPullServerConnection {
    [string] $ESEFilePath
    hidden [object] $Instance
    hidden [object] $SessionId
    hidden [object] $DbId
    hidden [object] $TableId

    DSCPullServerESEConnection () : base([DSCPullServerConnectionType]::ESE) { }

    DSCPullServerESEConnection ([string]$Path) : base([DSCPullServerConnectionType]::ESE) {
        $resolvedPath = Resolve-Path $Path -ErrorAction SilentlyContinue
        if ($null -eq $resolvedPath) {
            throw "File $Path is invalid"
        } else {
            $this.ESEFilePath = $resolvedPath.ProviderPath
        }
    }
}

class DSCPullServerSQLConnection : DSCPullServerConnection {
    [string] $SQLServer

    [pscredential] $Credential

    [string] $Database

    DSCPullServerSQLConnection () : base([DSCPullServerConnectionType]::SQL) { }

    DSCPullServerSQLConnection ([string]$Server, [pscredential]$Credential, [string]$Database) : base([DSCPullServerConnectionType]::SQL) {
        $this.SQLServer = $Server
        $this.Credential = $Credential
        $this.Database = $Database
    }

    DSCPullServerSQLConnection ([string]$Server, [pscredential]$Credential) : base([DSCPullServerConnectionType]::SQL) {
        $this.SQLServer = $Server
        $this.Credential = $Credential
        $this.Database = 'DSC'
    }

    DSCPullServerSQLConnection ([string]$Server, [string]$Database) : base([DSCPullServerConnectionType]::SQL) {
        $this.SQLServer = $Server
        $this.Database = $Database
    }

    DSCPullServerSQLConnection ([string]$Server) : base([DSCPullServerConnectionType]::SQL) {
        $this.SQLServer = $Server
    }

    [string] ConnectionString () {
        if ($this.Credential -and $this.Database) {
            return 'Server={0};uid={1};pwd={2};Trusted_Connection=False;Database={3};' -f @(
                $this.SQLServer,
                $this.Credential.UserName,
                $this.Credential.GetNetworkCredential().Password,
                $this.Database
            )
        } elseif (($null -eq $this.Credential) -and $this.Database) {
            return 'Server={0};Integrated Security=True;Database={1};' -f @(
                $this.SQLServer,
                $this.Database
            )
        } elseif ($this.Credential -and -not $this.Database) {
            return 'Server={0};uid={1};pwd={2};Trusted_Connection=False;' -f @(
                $this.SQLServer,
                $this.Credential.UserName,
                $this.Credential.GetNetworkCredential().Password
            )
        } else {
            return 'Server={0};Integrated Security=True;' -f @(
                $this.SQLServer
            )
        }
    }
}

class DSCPullServerMDBConnection : DSCPullServerConnection {
    [string] $MDBFilePath

    DSCPullServerMDBConnection () : base([DSCPullServerConnectionType]::MDB) { }

    DSCPullServerMDBConnection ([string]$Path) : base([DSCPullServerConnectionType]::MDB) {
        $resolvedPath = Resolve-Path $Path -ErrorAction SilentlyContinue
        if ($null -eq $resolvedPath) {
            throw "File $Path is invalid"
        } else {
            $this.MDBFilePath = $resolvedPath.ProviderPath
        }
    }

    [string] ConnectionString () {
        return "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$($this.MDBFilePath);Persist Security Info=False"
    }
}

class DSCBaseClass {
    hidden [DSCDatabaseTable] $TableName

    DSCBaseClass ([DSCDatabaseTable] $tableName) {
        $this.TableName = $tableName
    }

    hidden [string] GetKey () {
        $key = switch ($this.GetType().Name) {
            DSCDevice {
                'TargetName'
            }
            DSCNodeStatusReport {
                'JobId'
            }
            DSCNodeRegistration {
                'AgentId'
            }
        }
        return $key
    }

    hidden [string] GetExcludedProperties () {
        if ($this.GetType().Name -eq 'DSCDevice') {
            return 'Status'
        } elseif ($this.GetType().Name -eq 'DSCNodeStatusReport') {
            return 'LastModifiedTime'
        } else {
            return $null
        }
    }

    hidden [string] GetInsert ([bool] $isMDB) {
        $excludeProperties = $this.GetExcludedProperties()

        $query = ('INSERT INTO {0} ({1}) VALUES ({2})' -f @(
            $this.TableName,
            (($this | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -notin $excludeProperties}).Name -join ','),
            (($this | Get-Member -MemberType Property).ForEach{
                if ($_.Name -in $excludeProperties) {
                    return
                } else {
                    if ($_.Name -eq 'ConfigurationNames') {
                        if ($this.ConfigurationNames.Count -ge 1) {
                            "'[`"{0}`"]'" -f ($this."$($_.Name)" -join '","')
                        } else {
                            "'[]'"
                        }
                    } elseif ($_.Name -eq 'IPAddress') {
                        "'{0}'" -f ($this."$($_.Name)" -join ';')
                    } elseif ($_.Name -in 'StatusData', 'Errors') {
                        "'[{0}]'" -f (($this."$($_.Name)" | ConvertTo-Json -Compress -Depth 100) | ConvertTo-Json -Compress)
                    } elseif ($_.Name -eq 'AdditionalData') {
                        "'{0}'" -f ($this."$($_.Name)" | ConvertTo-Json -Compress -Depth 100)
                    } elseif ($_.Definition.Split(' ')[0] -like '*datetime*' -and -not $null -eq $this."$($_.Name)") {
                        if ($this."$($_.Name)".ToString('yyyy-MM-dd HH:mm:ss') -eq '0001-01-01 00:00:00') {
                            'NULL'
                        } else {
                            "'{0}'" -f $this."$($_.Name)".ToString('yyyy-MM-dd HH:mm:ss')
                        }
                    } elseif ($_.Definition.Split(' ')[0] -like '*nullable*' -and $null -eq $this."$($_.Name)") {
                        'NULL'
                    } elseif ($_.Definition.Split(' ')[0] -like '*bool*' -and $isMDB) {
                        if ($this."$($_.Name)" -eq $false) {
                            "'{0}'" -f '0'
                        } else {
                            "'{0}'" -f '-1'
                        }
                    } else {
                        "'{0}'" -f $this."$($_.Name)"
                    }
                }
            } -join ',')
        ))
        return $query
    }

    hidden [string] GetUpdate ([bool] $isMDB) {
        $excludeProperties = $this.GetExcludedProperties()
        $key = $this.GetKey()
        $query = "UPDATE {0} Set {1} WHERE {2} = '{3}'" -f @(
            $this.TableName,
            (($this | Get-Member -MemberType Property).Where{
                $_.Name -ne $key
            }.foreach{
                if ($_.Name -in $excludeProperties) {
                    # skip
                } elseif ($_.Name -in 'StatusData', 'Errors') {
                    "$($_.Name) = '[{0}]'" -f (($this."$($_.Name)" | ConvertTo-Json -Compress -Depth 100) | ConvertTo-Json -Compress)
                } elseif ($_.Name -eq 'AdditionalData') {
                    "$($_.Name) = '[{0}]'" -f ($this."$($_.Name)" | ConvertTo-Json -Compress -Depth 100)
                } elseif ($_.Name -eq 'ConfigurationNames') {
                    if ($this.ConfigurationNames.Count -ge 1) {
                        "$($_.Name) = '[`"{0}`"]'" -f ($this."$($_.Name)" -join '","')
                    } else {
                        "$($_.Name) = '[]'"
                    }
                } elseif ($_.Name -eq 'IPAddress') {
                    "$($_.Name) = '{0}'" -f ($this."$($_.Name)" -join ';')
                } elseif ($_.Definition.Split(' ')[0] -like '*datetime*' -and -not $null -eq $this."$($_.Name)") {
                    if ($this."$($_.Name)".ToString('yyyy-MM-dd HH:mm:ss') -eq '0001-01-01 00:00:00') {
                        "$($_.Name) = NULL"
                    } else {
                        "$($_.Name) = '{0}'" -f $this."$($_.Name)".ToString('yyyy-MM-dd HH:mm:ss')
                    }
                } elseif ($_.Definition.Split(' ')[0] -like '*nullable*' -and $null -eq $this."$($_.Name)") {
                    "$($_.Name) = NULL"
                } elseif ($_.Definition.Split(' ')[0] -like '*bool*' -and $isMDB) {
                    if ($this."$($_.Name)" -eq $false) {
                        "$($_.Name) = '{0}'" -f '0'
                    } else {
                        "$($_.Name) = '{0}'" -f '-1'
                    }
                } else {
                    "$($_.Name) = '{0}'" -f $this."$($_.Name)"
                }
            } -join ','),
            $key,
            $this.$key
        )
        return $query
    }

    hidden [string] GetDelete () {
        $key = $this.GetKey()
        return ("DELETE FROM {0} WHERE {1} = '{2}'" -f $this.TableName, $key, $this.$key)
    }

    [string] GetSQLDelete () {
        return $this.GetDelete()
    }

    [string] GetMDBDelete () {
        return $this.GetDelete()
    }

    [string] GetSQLInsert () {
        return $this.GetInsert($false)
    }

    [string] GetMDBInsert () {
        return $this.GetInsert($true)
    }

    [string] GetSQLUpdate () {
        return $this.GetUpdate($false)
    }

    [string] GetMDBUpdate () {
        return $this.GetUpdate($true)
    }
}

class DSCDevice : DSCBaseClass {
    [string] $TargetName

    [guid] $ConfigurationID

    [string] $ServerCheckSum

    [string] $TargetCheckSum

    [bool] $NodeCompliant

    [nullable[datetime]] $LastComplianceTime

    [nullable[datetime]] $LastHeartbeatTime

    [bool] $Dirty

    [int32] $StatusCode

    hidden [string] $_status = "$($this | Add-Member -MemberType ScriptProperty -Name Status -Value {
        $this.GetStatus()
    })"


    DSCDevice () : base([DSCDatabaseTable]::Devices) { }

    DSCDevice ([System.Data.Common.DbDataRecord] $Input) : base([DSCDatabaseTable]::Devices) {
        for ($i = 0; $i -lt $Input.FieldCount; $i++) {
            $name = $Input.GetName($i)
            if (([DBNull]::Value).Equals($Input[$i])) {
                $this."$name" = $null
            } else {
                $this."$name" = $Input[$i]
            }
        }
    }

    [string] GetStatus () {
        $deviceStatusCodeMap = @{
            0 = 'Configuration was applied successfully'
            1 = 'Download Manager initialization failure'
            2 = 'Get configuration command failure'
            3 = 'Unexpected get configuration response from pull server'
            4 = 'Configuration checksum file read failure'
            5 = 'Configuration checksum validation failure'
            6 = 'Invalid configuration file'
            7 = 'Available modules check failure'
            8 = 'Invalid configuration Id In meta-configuration'
            9 = 'Invalid DownloadManager CustomData in meta-configuration'
            10 = 'Get module command failure'
            11 = 'Get Module Invalid Output'
            12 = 'Module checksum file not found'
            13 = 'Invalid module file'
            14 = 'Module checksum validation failure'
            15 = 'Module extraction failed'
            16 = 'Module validation failed'
            17 = 'Downloaded module is invalid'
            18 = 'Configuration file not found'
            19 = 'Multiple configuration files found'
            20 = 'Configuration checksum file not found'
            21 = 'Module not found'
            22 = 'Invalid module version format'
            23 = 'Invalid configuration Id format'
            24 = 'Get Action command failed'
            25 = 'Invalid checksum algorithm'
            26 = 'Get Lcm Update command failed'
            27 = 'Unexpected Get Lcm Update response from pull server'
            28 = 'Invalid Refresh Mode in meta-configuration'
            29 = 'Invalid Debug Mode in meta-configuration'
        }
        return $deviceStatusCodeMap[$this.StatusCode]
    }
}

class DSCNodeRegistration : DSCBaseClass {
    [Guid] $AgentId

    [string] $LCMVersion

    [string] $NodeName

    [IPAddress[]] $IPAddress

    [string[]] $ConfigurationNames

    DSCNodeRegistration () : base([DSCDatabaseTable]::RegistrationData) { }

    DSCNodeRegistration ([System.Data.Common.DbDataRecord] $Input) : base([DSCDatabaseTable]::RegistrationData) {
        for ($i = 0; $i -lt $Input.FieldCount; $i++) {
            $name = $Input.GetName($i)
            $data = $null
            switch ($name) {
                'ConfigurationNames' {
                    $data = ($Input[$i] | ConvertFrom-Json)
                }
                'IPAddress' {
                    $data = ($Input[$i] -split ',') -split ';' | ForEach-Object -Process {
                        if ($_ -ne [string]::Empty) {
                            $_
                        }
                    }
                }
                default {
                    $data = $Input[$i]
                }
            }
            $this."$name" = $data
        }
    }
}

class DSCNodeStatusReport : DSCBaseClass {
    [Guid] $JobId

    [Guid] $Id

    [string] $OperationType

    [string] $RefreshMode

    [string] $Status

    [string] $LCMVersion

    [string] $ReportFormatVersion

    [string] $ConfigurationVersion

    [string] $NodeName

    [IPAddress[]] $IPAddress

    [nullable[datetime]] $StartTime

    [nullable[datetime]] $EndTime

    [nullable[datetime]] $LastModifiedTime # Only applicable for ESENT, Not present in SQL

    [PSObject[]] $Errors

    [PSObject[]] $StatusData

    [bool] $RebootRequested

    [PSObject[]] $AdditionalData

    DSCNodeStatusReport () : base([DSCDatabaseTable]::StatusReport) { }

    DSCNodeStatusReport ([System.Data.Common.DbDataRecord] $Input) : base([DSCDatabaseTable]::StatusReport) {
        for ($i = 0; $i -lt $Input.FieldCount; $i++) {
            $name = $Input.GetName($i)
            $data = $null
            switch ($name) {
                { $_ -in 'StatusData', 'Errors'} {
                    $data = (($Input[$i] | ConvertFrom-Json) | ConvertFrom-Json)
                }
                'AdditionalData' {
                    $data = ($Input[$i] | ConvertFrom-Json)
                }
                'IPAddress' {
                    $data = ($Input[$i] -split ',') -split ';' | ForEach-Object -Process {
                        if ($_ -ne [string]::Empty) {
                            $_
                        }
                    }
                }
                default {
                    $data = $Input[$i]
                }
            }
            if ($false -eq [string]::IsNullOrEmpty($data)) {
                $this."$name" = $data
            }
        }
    }
}

function Assert-DSCPullServerDatabaseFilePath {
    param (
        [Parameter(Mandatory, ValueFromPipeline)]
        [System.IO.FileInfo] $File,

        [Parameter(Mandatory)]
        [ValidateSet('ESE', 'MDB')]
        [string] $Type
    )
    process {
        if ($Type -eq 'ESE' -and $File -notmatch '\.edb$') {
            throw 'The file specified in the path argument must be of type edb'
        }

        if ($Type -eq 'MDB' -and $File -notmatch '\.mdb$') {
            throw 'The file specified in the path argument must be of type mdb'
        }

        $true
    }
}

function Test-DefaultDSCPullServerConnection {
    [OutputType([bool])]
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [AllowNull()]
        [DSCPullServerConnection] $Connection
    )

    if ($null -eq $Connection) {
        Write-Warning 'No active connection was found'
        $false
    } else {
        $true
    }
}

function Set-DSCPullServerESERecord {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseShouldProcessForStateChangingFunctions', '')]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [Parameter(Mandatory)]
        [object] $InputObject,

        [Parameter(ValueFromPipeline)]
        [object] $PipeLineObject,

        [switch] $Insert
    )
    process {
        $columnDictionary = [Microsoft.Isam.Esent.Interop.Api]::GetColumnDictionary($Connection.SessionId, $Connection.TableId)

        [Microsoft.Isam.Esent.Interop.Api]::JetBeginTransaction($Connection.SessionId)
        if ($Insert) {
            [Microsoft.Isam.Esent.Interop.Api]::JetPrepareUpdate($Connection.SessionId, $Connection.TableId, [Microsoft.Isam.Esent.Interop.JET_prep]::Insert)
        } else {
            [Microsoft.Isam.Esent.Interop.Api]::JetPrepareUpdate($Connection.SessionId, $Connection.TableId, [Microsoft.Isam.Esent.Interop.JET_prep]::Replace)
        }

        try {
            $columnDictionary.Keys.ForEach{
                if ($InputObject.GetType().Name -eq 'DSCNodeStatusReport' -and $_ -eq 'JobId' -and -not $Insert) {
                    #primary key cannot be updated
                    return
                } elseif ($InputObject.GetType().Name -eq 'DSCNodeRegistration' -and $_ -eq 'AgentId' -and -not $Insert) {
                    #primary key cannot be updated
                    return
                } elseif ($InputObject.GetType().Name -eq 'DSCDevice' -and $_ -eq 'Targetname' -and -not $Insert) {
                    #primary key cannot be updated
                    return
                } elseif ($_ -in 'ConfigurationNames', 'Errors', 'StatusData') {
                    [Microsoft.Isam.Esent.Interop.Api]::SerializeObjectToColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        $InputObject.$_
                    )
                } elseif ($_ -eq 'IPAddress') {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        ($InputObject.$_ -join ';'),
                        [System.Text.Encoding]::Unicode
                    )
                } elseif ($_ -eq 'AdditionalData') {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        ($InputObject.$_ | ConvertTo-Json -Compress -Depth 100),
                        [System.Text.Encoding]::Unicode
                    )
                } elseif ($_ -in 'StartTime', 'EndTime', 'LastComplianceTime', 'LastHeartbeatTime', 'StatusCode', 'NodeCompliant') {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        $InputObject.$_
                    )
                } elseif ($_ -eq 'LastModifiedTime') {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        [datetime]::Now
                    )
                } elseif ($_ -in 'Dirty') {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        ([convert]::ToBoolean($InputObject.$_))
                    )
                } else {
                    [Microsoft.Isam.Esent.Interop.Api]::SetColumn(
                        $Connection.SessionId,
                        $Connection.TableId,
                        $columnDictionary[$_],
                        $InputObject.$_,
                        [System.Text.Encoding]::Unicode
                    )
                }
            }

            [Microsoft.Isam.Esent.Interop.Api]::JetUpdate($Connection.SessionId, $Connection.TableId)
            [Microsoft.Isam.Esent.Interop.Api]::JetCommitTransaction($Connection.SessionId, [Microsoft.Isam.Esent.Interop.CommitTransactionGrbit]::None)
        } catch {
            [Microsoft.Isam.Esent.Interop.Api]::JetRollback($Connection.SessionId, [Microsoft.Isam.Esent.Interop.RollbackTransactionGrbit]::None)
            Write-Error -ErrorRecord $_
        }
    }
}

function Remove-DSCPullServerESERecord {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseShouldProcessForStateChangingFunctions', '')]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [Parameter(ValueFromPipeline)]
        [object] $PipeLineObject
    )
    process {
        [Microsoft.Isam.Esent.Interop.Api]::JetDelete($Connection.SessionId, $Connection.TableId)
    }
}

function PreProc {
    param (
        [Parameter(Mandatory)]
        [string] $ParameterSetName,

        [DSCPullServerConnection] $Connection,

        [string] $SQLServer,

        [pscredential] $Credential,

        [string] $Database,

        [string] $ESEFilePath,

        [string] $MDBFilePath,

        [Parameter(ValueFromRemainingArguments)]
        $DroppedParams
    )
    $script:GetConnection = $null
    switch -Wildcard ($ParameterSetName) {
        *Connection {
            if (Test-DefaultDSCPullServerConnection $Connection) {
                return $Connection
            }
        }
        *SQL {
            $newSQLArgs = @{
                SQLServer = $SQLServer
                DontStore = $true
            }

            $PSBoundParameters.Keys | ForEach-Object -Process {
                if ($_ -in 'Credential', 'Database') {
                    [void] $newSQLArgs.Add($_, $PSBoundParameters[$_])
                }
            }
            New-DSCPullServerAdminConnection @newSQLArgs
        }
        *ESE {
            $newESEArgs = @{
                ESEFilePath = $ESEFilePath
                DontStore = $true
            }
            New-DSCPullServerAdminConnection @newESEArgs
        }
        *MDB {
            $newMDBArgs = @{
                MDBFilePath = $MDBFilePath
                DontStore = $true
            }
            New-DSCPullServerAdminConnection @newMDBArgs
        }
    }
}

function Open-DSCPullServerTable {
    param (
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [Parameter(Mandatory)]
        [string] $Table
    )
    [Microsoft.Isam.Esent.Interop.JET_TABLEID] $tableId = [Microsoft.Isam.Esent.Interop.JET_TABLEID]::Nil
    [void] [Microsoft.Isam.Esent.Interop.Api]::JetOpenTable(
        $Connection.SessionId,
        $Connection.DbId,
        $Table,
        $null,
        0,
        [Microsoft.Isam.Esent.Interop.OpenTableGrbit]::None,
        [ref]$tableId
    )
    $Connection.TableId = $tableId
}

function Mount-DSCPullServerESEDatabase {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [ValidateSet('None', 'ReadOnly', 'Exclusive')]
        [string] $Mode = 'None'
    )

    $instanceName = [guid]::NewGuid().guid
    $systemPath = (Split-Path -Path $Connection.ESEFilePath) + '\'

    [Microsoft.Isam.Esent.Interop.JET_INSTANCE] $jetInstance = [Microsoft.Isam.Esent.Interop.JET_INSTANCE]::Nil
    [Microsoft.Isam.Esent.Interop.JET_SESID] $sessionId = [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil
    [Microsoft.Isam.Esent.Interop.JET_DBID] $dbId = [Microsoft.Isam.Esent.Interop.JET_DBID]::Nil

    #parameter options:
    #https://msdn.microsoft.com/en-us/library/microsoft.isam.esent.interop.jet_param(v=exchg.10).aspx

    'NoInformationEvent', 'CircularLog' | ForEach-Object -Process {
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetSetSystemParameter(
            $jetInstance,
            [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil,
            [Microsoft.Isam.Esent.Interop.JET_param]$_,
            1,
            $null
        )
    }

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetSetSystemParameter(
        $jetInstance,
        [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil,
        [Microsoft.Isam.Esent.Interop.JET_param]::LogFileSize,
        128,
        $null
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetSetSystemParameter(
        $jetInstance,
        [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil,
        [Microsoft.Isam.Esent.Interop.JET_param]::CheckpointDepthMax,
        524288,
        $null
    )

    'PreferredVerPages', 'MaxVerPages' | ForEach-Object -Process {
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetSetSystemParameter(
            $jetInstance,
            [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil,
            [Microsoft.Isam.Esent.Interop.JET_param]$_,
            1024,
            $null
        )
    }

    'SystemPath', 'TempPath', 'LogFilePath' | ForEach-Object -Process {
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetSetSystemParameter(
            $jetInstance,
            [Microsoft.Isam.Esent.Interop.JET_SESID]::Nil,
            [Microsoft.Isam.Esent.Interop.JET_param]$_,
            $null,
            $systemPath
        )
    }

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetCreateInstance2(
        [ref]$jetInstance,
        $instanceName,
        $instanceName,
        [Microsoft.Isam.Esent.Interop.CreateInstanceGrbit]::None
    )


    [void] [Microsoft.Isam.Esent.Interop.Api]::JetInit2(
        [ref]$jetInstance,
        [Microsoft.Isam.Esent.Interop.InitGrbit]::None
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetBeginSession(
        $jetInstance,
        [ref]$sessionId,
        $null,
        $null
    )
    try {
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetAttachDatabase(
            $sessionId,
            $Connection.ESEFilePath,
            [Microsoft.Isam.Esent.Interop.AttachDatabaseGrbit]$Mode
        )
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetOpenDatabase(
            $sessionId,
            $Connection.ESEFilePath,
            $null,
            [ref]$dbId,
            [Microsoft.Isam.Esent.Interop.OpenDatabaseGrbit]$Mode
        )
        $Connection.Instance = $jetInstance
        $Connection.SessionId = $sessionId
        $Connection.DbId = $dbId
    } catch {
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetEndSession(
            $sessionId,
            [Microsoft.Isam.Esent.Interop.EndSessionGrbit]::None
        )
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetTerm($jetInstance)
        throw $_
    }
}

function Invoke-DSCPullServerSQLCommand {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerSQLConnection] $Connection,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string] $Script,

        [Parameter()]
        [ValidateSet('Get', 'Set')]
        [string] $CommandType = 'Get',

        [Parameter()]
        [uint16] $CommandTimeOut = 30,

        [Parameter(ValueFromRemainingArguments, DontShow)]
        $DroppedParams
    )
    begin {
        try {
            $sqlConnection = [System.Data.SqlClient.SqlConnection]::new($Connection.ConnectionString())
            $sqlConnection.Open()
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        }
    }
    process {
        try {
            $command = $sqlConnection.CreateCommand()
            $command.CommandText = $Script
            $command.CommandTimeout = $CommandTimeOut

            Write-Verbose ("Invoking command: {0}" -f $Script)

            if ($CommandType -eq 'Get') {
                $command.ExecuteReader()
            } else {
                [void] $command.ExecuteNonQuery()
            }
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        } finally {
            if ($false -eq $?) {
                $sqlConnection.Close()
                $sqlConnection.Dispose()
            }
        }
    }
    end {
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
}

function Invoke-DSCPullServerMDBCommand {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerMDBConnection] $Connection,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string] $Script,

        [Parameter()]
        [ValidateSet('Get', 'Set')]
        [string] $CommandType = 'Get',

        [Parameter(ValueFromRemainingArguments, DontShow)]
        $DroppedParams
    )
    begin {
        try {
            $mdbConnection = [System.Data.OleDb.OleDbConnection]::new($Connection.ConnectionString())
            $mdbConnection.Open()
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        }
    }
    process {
        try {
            $command = $mdbConnection.CreateCommand()
            $command.CommandText = $Script

            Write-Verbose ("Invoking command: {0}" -f $Script)

            if ($CommandType -eq 'Get') {
                $command.ExecuteReader()
            } else {
                [void] $command.ExecuteNonQuery()
            }
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        } finally {
            if ($false -eq $?) {
                $mdbConnection.Close()
                $mdbConnection.Dispose()
            }
        }
    }
    end {
        $mdbConnection.Close()
        $mdbConnection.Dispose()
    }
}

function Get-DSCPullServerSQLTable {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSAvoidUsingEmptyCatchBlock', '')]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseOutputTypeCorrectly', '')]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerSQLConnection] $Connection
    )
    try {
        $sqlConnection = [System.Data.SqlClient.SqlConnection]::new($Connection.ConnectionString())
        $sqlConnection.Open()
        $sqlConnection.GetSchema('Tables').TABLE_NAME
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
}

function Get-DSCPullServerMDBTable {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSAvoidUsingEmptyCatchBlock', '')]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseOutputTypeCorrectly', '')]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerMDBConnection] $Connection
    )
    try {
        $mdbConnection = [System.Data.OleDb.OleDbConnection]::new($Connection.ConnectionString())
        $mdbConnection.Open()
        $mdbConnection.GetSchema('Tables').TABLE_NAME
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        $mdbConnection.Close()
        $mdbConnection.Dispose()
    }
}

function Get-DSCPullServerESETable {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSAvoidUsingEmptyCatchBlock', '')]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseOutputTypeCorrectly', '')]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection
    )
    try {
        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode None
        [Microsoft.Isam.Esent.Interop.Api]::GetTableNames($Connection.SessionId, $Connection.DbId)
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        if ($null -ne $Connection.Instance) {
            Dismount-DSCPullServerESEDatabase -Connection $Connection
        }
    }
}

function Get-DSCPullServerESERecord {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessage('PSAvoidUsingEmptyCatchBlock', '')]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [Parameter(Mandatory)]
        [ValidateSet('Devices', 'RegistrationData', 'StatusReport')]
        [string] $Table,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string] $TargetName,

        [Parameter()]
        [guid] $ConfigurationID,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string] $NodeName,

        [Parameter()]
        [guid] $AgentId,

        [Parameter()]
        [datetime] $FromStartTime,

        [Parameter()]
        [datetime] $ToStartTime,

        [Parameter()]
        [guid] $JobId,

        [Parameter()]
        [ValidateSet('All', 'LocalConfigurationManager', 'Consistency', 'Initial')]
        [string] $OperationType = 'All',

        [Parameter()]
        [uint16] $Top
    )
    begin {
        $stringColumns = @(
            'TargetName',
            'ServerCheckSum',
            'TargetChecksum',
            'NodeName',
            'OperationType',
            'RefreshMode',
            'Status',
            'LCMVersion',
            'ReportFormatVersion',
            'ConfigurationVersion',
            'RebootRequested',
            'JobId',
            'Id',
            'ConfigurationID'
        )

        $boolColumns = @(
            'NodeCompliant',
            'Dirty'
        )

        $datetimeColumns = @(
            'LastComplianceTime',
            'LastHeartbeatTime',
            'StartTime',
            'EndTime',
            'LastModifiedTime'
        )

        $deserializeColumns = @(
            'Errors',
            'StatusData',
            'ConfigurationNames'
        )

        $convertFromJsonColumns = @(
            'AdditionalData'
        )

        $intColumns = @(
            'StatusCode'
        )

        try {
            Mount-DSCPullServerESEDatabase -Connection $Connection -Mode None
            Open-DSCPullServerTable -Connection $Connection -Table $Table
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        }
    }
    process {
        try {
            $recordCount = 0
            while ([Microsoft.Isam.Esent.Interop.Api]::TryMoveNext($Connection.SessionId, $Connection.TableId)) {
                if ($PSBoundParameters.ContainsKey('Top') -and $Top -eq $recordCount) {
                    break
                }

                switch ($Table) {
                    Devices {
                        $result = [DSCDevice]::new()
                    }
                    RegistrationData {
                        $result = [DSCNodeRegistration]::new()
                    }
                    StatusReport {
                        $result = [DSCNodeStatusReport]::new()
                    }
                }
                foreach ($column in ([Microsoft.Isam.Esent.Interop.Api]::GetTableColumns($Connection.SessionId, $Connection.TableId))) {
                    if ($column.Name -eq 'IPAddress') {
                        $ipAddress = ([Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                                $Connection.SessionId,
                                $Connection.TableId,
                                $column.Columnid,
                                [System.Text.Encoding]::Unicode
                            ) -split ';' -split ',')
                        $result."$($column.Name)" = $ipAddress.ForEach{
                            # potential for invalid ip address like empty string
                            try {
                                [void][ipaddress]::Parse($_)
                                $_
                            } catch {}
                        }
                    } elseif ($column.Name -in $stringColumns) {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid,
                            [System.Text.Encoding]::Unicode
                        )
                    } elseif ($column.Name -in $boolColumns) {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsBoolean(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid
                        )
                    } elseif ($column.Name -in $datetimeColumns) {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsDateTime(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid
                        )
                    } elseif ($column.Name -in $intColumns) {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsInt32(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid
                        )
                    } elseif ($column.Name -in $deserializeColumns) {
                        $data = [Microsoft.Isam.Esent.Interop.Api]::DeserializeObjectFromColumn(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid
                        )
                        if ($column.Name -eq 'StatusData') {
                            $result."$($column.Name)" = $data | ConvertFrom-Json -ErrorAction SilentlyContinue
                        } else {
                            $result."$($column.Name)" = $data
                        }
                    } elseif ($column.Name -in $convertFromJsonColumns) {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid,
                            [System.Text.Encoding]::Unicode
                        ) | ConvertFrom-Json -ErrorAction SilentlyContinue
                    } else {
                        $result."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                            $Connection.SessionId,
                            $Connection.TableId,
                            $column.Columnid,
                            [System.Text.Encoding]::Unicode
                        )
                    }
                }

                if ($Table -eq 'Devices') {
                    if ($PSBoundParameters.ContainsKey('TargetName') -and $result.TargetName -notlike $TargetName) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('ConfigurationID') -and $result.ConfigurationID -notlike $ConfigurationID) {
                        continue
                    }
                    $result
                } elseif ($Table -eq 'RegistrationData') {
                    if ($PSBoundParameters.ContainsKey('NodeName') -and $result.NodeName -notlike $NodeName) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('AgentId') -and $result.AgentId -ne $AgentId) {
                        continue
                    }
                    $result
                } elseif ($Table -eq 'StatusReport') {
                    if ($PSBoundParameters.ContainsKey('AgentId') -and $result.Id -ne $AgentId) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('NodeName') -and $result.NodeName -notlike $NodeName) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('FromStartTime') -and $result.StartTime -lt $FromStartTime) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('ToStartTime') -and $result.StartTime -gt $ToStartTime) {
                        continue
                    }
                    if ($PSBoundParameters.ContainsKey('JobId') -and $result.JobId -ne $JobId) {
                        continue
                    }
                    if ($OperationType -ne 'All' -and $result.OperationType -ne $OperationType) {
                        continue
                    }
                    [void] $recordCount++
                    $result
                }
            }
        } catch {
            Write-Error -ErrorRecord $_ -ErrorAction Stop
        } finally {
            Dismount-DSCPullServerESEDatabase -Connection $Connection
        }
    }
    end {
        if ($null -ne $Connection.Instance) {
            Dismount-DSCPullServerESEDatabase -Connection $Connection
        }
    }
}

function Dismount-DSCPullServerESEDatabase {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetCloseDatabase(
        $Connection.SessionId,
        $Connection.DbId,
        [Microsoft.Isam.Esent.Interop.CloseDatabaseGrbit]::None
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetDetachDatabase(
        $Connection.SessionId,
        $Connection.ESEFilePath
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetEndSession(
        $Connection.SessionId,
        [Microsoft.Isam.Esent.Interop.EndSessionGrbit]::None
    )

    [void] [Microsoft.Isam.Esent.Interop.Api]::JetTerm(
        $Connection.Instance
    )

    $Connection.Instance = $null
    $Connection.SessionId = $null
    $Connection.DbId = $null
    $Connection.TableId = $null
}

function Assert-DSCPullServerMDBPreReq {
    # check on type instead of PowerShell version as potentially the type will surface in a later version when .net core is updated.
    if ($null -eq ('System.Data.OleDb.OleDbConnection'-as [type])) {
        Write-Error -Message 'Type "System.Data.OleDb.OleDbConnection" is not available. To access MDB files, please use PowerShell 5.1' -ErrorAction Stop
    }

    # see if provider is available
    $oleDbEnum = [System.Data.OleDb.OleDbEnumerator]::new()
    if ($oleDbEnum.GetElements().SOURCES_NAME -notcontains 'Microsoft.ACE.OLEDB.16.0') {
        Write-Error -Message 'To access MDB files please install "Microsoft Access Database Engine 2016 Redistributable" from https://www.microsoft.com/en-us/download/details.aspx?id=54920' -ErrorAction Stop
    }
}

function Assert-DSCPullServerESEPreReq {
    # see if type is already available, don't load when already loaded
    if ($null -eq ('Microsoft.Isam.Esent.Interop.Api'-as [type])) {
        # try to load
        try {
            [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Isam.Esent.Interop')
        } catch {
            Write-Error -Message 'To access EDB files, please use PowerShell on Windows' -ErrorAction Stop
        }
    }
}

function Test-DSCPullServerDatabase {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerConnection] $Connection
    )
    $expectedTableNames = @(
        'Devices',
        'RegistrationData',
        'StatusReport'
    )

    $tableNames = switch ($Connection.Type) {
        SQL {
            Get-DSCPullServerSQLTable -Connection $connection
        }
        ESE {
            Get-DSCPullServerESETable -Connection $connection
        }
        MDB {
            Get-DSCPullServerMDBTable -Connection $connection
        }
    }
    Write-Verbose -Message "Database Tables: $($tableNames -join ', ' | Out-String)"

    $result = $true
    foreach ($table in $expectedTableNames) {
        if ($table -notin $tableNames) {
            $result = $false
        }
    }
    $result
}

function Test-DSCPullServerDatabaseExist {
    [OutputType([bool])]
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('Database')]
        [string] $Name,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerSQLConnection] $Connection,

        [Parameter(ValueFromRemainingArguments)]
        $DroppedParams
    )
    if ($PSCmdlet.ParameterSetName -eq 'SQL') {
        $testConnection = [DSCPullServerSQLConnection]::new($SQLServer)
        if ($PSBoundParameters.ContainsKey('Credential')) {
            $testConnection.Credential = $Credential
        }
    } else {
        $testConnection = [DSCPullServerSQLConnection]::new($Connection.SQLServer)
        if ($null -ne $Connection.Credential) {
            $testConnection.Credential = $Connection.Credential
        }
        $Name = $Connection.Database
    }

    $testDBQuery = "DECLARE @dbname nvarchar(128) SET @dbname = N'{0}' IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname))) SELECT CAST(1 AS bit) ELSE SELECT CAST(0 AS bit)" -f $Name
    $testResult = Invoke-DSCPullServerSQLCommand -Connection $testConnection -CommandType Get -Script $testDBQuery
    $testResult.GetBoolean(0)
}

<#
    .SYNOPSIS
    Copy data between 2 Database connections
 
    .DESCRIPTION
    This function allows for data to be copied over from
    a connection to another connection. This allows
    a user to migrate over data from an ESENT type Pull Server to
    a SQL type Pull Server, SQL to SQL type Pull Server, SQL to ESENT type
    Pull Server and ESENT to ESENT Type Pull Server without loosing data.
 
    .PARAMETER Connection1
    A specifically passed in Connection to migrate data out of.
 
    .PARAMETER Connection2
    A specifically passed in Connection to migrate data in to.
 
    .PARAMETER ObjectsToMigrate
    Define the object types to migrate. Defaults to Devices and RegistrationData.
 
    .PARAMETER Force
    When specified, existing records in the target database will be overwritten. When not specified
    existing data will not be overwritten and Warnings will be provided to inform
    the user.
 
    .EXAMPLE
    $eseConnection = New-DSCPullServerAdminConnection -ESEFilePath C:\EDB\Devices.edb
    $sqlConnection = New-DSCPullServerAdminConnection -SQLServer sqlserver\instance -Database dsc -Credential sa
 
    Copy-DSCPullServerAdminData -ObjectsToMigrate Devices, RegistrationData, StatusReports -Connection1 $eseConnection -Connection2 $sqlConnection -Force
#>

function Copy-DSCPullServerAdminData {
    [CmdletBinding(SupportsShouldProcess)]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerConnection] $Connection1,

        [Parameter(Mandatory)]
        [DSCPullServerConnection] $Connection2,

        [Parameter(Mandatory)]
        [ValidateSet('Devices', 'RegistrationData', 'StatusReports')]
        [string[]] $ObjectsToMigrate,

        [Parameter()]
        [switch] $Force
    )

    switch ($ObjectsToMigrate) {
        Devices {
            $devices = Get-DSCPullServerAdminDevice -Connection $Connection1
            foreach ($d in $devices) {
                $con2D = Get-DSCPullServerAdminDevice -Connection $Connection2 -TargetName $d.TargetName
                if ($null -eq $con2D) {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new device on $($Connection2.ESEFilePath)")) {
                                $d | New-DSCPullServerAdminDevice -Connection $Connection2 -Confirm:$false
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new device on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($d.GetSQLInsert())
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new device on $($Connection2.MDBFilePath)")) {
                                Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($d.GetMDBInsert())
                            }
                        }
                    }
                } else {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace existing device on $($Connection2.ESEFilePath)")) {
                                if ($Force) {
                                    $d | Set-DSCPullServerAdminDevice -Connection $Connection2 -Confirm:$false
                                } else {
                                    Write-Warning -Message "Unable to replace device $($d.TargetName) as Force switch was not set"
                                }
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace existing device on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($con2D.GetSQLDelete())
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($d.GetSQLInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace device $($d.TargetName) as Force switch was not set"
                                }
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace existing device on $($Connection2.MDBFilePath)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($con2D.GetMDBDelete())
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($d.GetMDBInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace device $($d.TargetName) as Force switch was not set"
                                }
                            }
                        }
                    }
                }
            }
        }
        RegistrationData {
            $registrations = Get-DSCPullServerAdminRegistration -Connection $Connection1
            foreach ($r in $registrations) {
                $con2Reg = Get-DSCPullServerAdminRegistration -Connection $Connection2 -AgentId $r.AgentId
                if ($null -eq $con2Reg) {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($r.AgentId, "Create new Registration on $($Connection2.ESEFilePath)")) {
                                $r | New-DSCPullServerAdminRegistration -Connection $Connection2 -Confirm:$false
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($r.AgentId, "Create new Registration on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($r.GetSQLInsert())
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new Registration on $($Connection2.MDBFilePath)")) {
                                Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($r.GetMDBInsert())
                            }
                        }
                    }
                } else {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($r.AgentId, "Replace existing Registration on $($Connection2.ESEFilePath)")) {
                                if ($Force) {
                                    $r | Set-DSCPullServerAdminRegistration -Connection $Connection2 -Confirm:$false
                                } else {
                                    Write-Warning -Message "Unable to replace Registration $($r.AgentId) as Force switch was not set"
                                }
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($r.AgentId, "Replace existing Registration on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($con2Reg.GetSQLDelete())
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($r.GetSQLInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace Registration $($r.AgentId) as Force switch was not set"
                                }
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace existing Registration on $($Connection2.MDBFilePath)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($con2Reg.GetMDBDelete())
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($r.GetMDBInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace Registration $($d.TargetName) as Force switch was not set"
                                }
                            }
                        }
                    }
                }
            }
        }
        StatusReports {
            $reports = Get-DSCPullServerAdminStatusReport -Connection $Connection1 -All
            foreach ($r in $reports) {
                $con2Rep = Get-DSCPullServerAdminStatusReport -Connection $Connection2 -JobId $r.JobId
                if ($null -eq $con2Rep) {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($r.JobId, "Create new StatusReport on $($Connection2.ESEFilePath)")) {
                                $r | New-DSCPullServerAdminStatusReport -Connection $Connection2 -Confirm:$false
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($r.JobId, "Create new StatusReport on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($r.GetSQLInsert())
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new StatusReport on $($Connection2.MDBFilePath)")) {
                                Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($r.GetMDBInsert())
                            }
                        }
                    }
                } else {
                    switch ($Connection2.Type) {
                        ESE {
                            if ($PSCmdlet.ShouldProcess($r.JobId, "Replace StatusReport Registration on $($Connection2.ESEFilePath)")) {
                                if ($Force) {
                                    $r | Set-DSCPullServerAdminStatusReport -Connection $Connection2 -Confirm:$false
                                } else {
                                    Write-Warning -Message "Unable to replace StatusReport $($r.JobId) as Force switch was not set"
                                }
                            }
                        }
                        SQL {
                            if ($PSCmdlet.ShouldProcess($r.JobId, "Replace StatusReport Registration on $($Connection2.SQLServer)\$($Connection2.Database)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($con2Rep.GetSQLDelete())
                                    Invoke-DSCPullServerSQLCommand -Connection $Connection2 -CommandType Set -Script ($r.GetSQLInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace StatusReport $($r.JobId) as Force switch was not set"
                                }
                            }
                        }
                        MDB {
                            if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace StatusReport Registration on $($Connection2.MDBFilePath)")) {
                                if ($Force) {
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($con2Rep.GetMDBDelete())
                                    Invoke-DSCPullServerMDBCommand -Connection $Connection2 -CommandType Set -Script ($r.GetMDBInsert())
                                } else {
                                    Write-Warning -Message "Unable to replace StatusReport $($d.TargetName) as Force switch was not set"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Overwrites device entry (LCMv1) properties in a Pull Server Database.
 
    .DESCRIPTION
    LCMv1 (WMF4 / PowerShell 4.0) pull clients send information
    to the Pull Server which stores their data in the devices table.
    This function will allow for manual overwrites of device properties
    in the devices table.
 
    .PARAMETER InputObject
    Pass in the device object to be modified from the database.
 
    .PARAMETER ConfigurationID
    Set the ConfigurationID property for the existing device.
 
    .PARAMETER TargetName
    Modify properties for the device with specified TargetName.
 
    .PARAMETER ServerCheckSum
    Set the ServerCheckSum property for the existing device.
 
    .PARAMETER TargetCheckSum
    Set the TargetCheckSum property for the existing device.
 
    .PARAMETER NodeCompliant
    Set the NodeCompliant property for the existing device.
 
    .PARAMETER LastComplianceTime
    Set the LastComplianceTime property for the existing device.
 
    .PARAMETER LastHeartbeatTime
    Set the LastHeartbeatTime property for the existing device.
 
    .PARAMETER Dirty
    Set the Dirty property for the existing device.
 
    .PARAMETER StatusCode
    Set the StatusCode property for the existing device.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Set-DSCPullServerAdminDevice -TargetName '192.168.0.1' -ConfigurationID '80ee20f9-78df-480d-8175-9dd6cb09607a'
 
    .EXAMPLE
    Get-DSCPullServerAdminDevice -TargetName '192.168.0.1' | Set-DSCPullServerAdminDevice -ConfigurationID '80ee20f9-78df-480d-8175-9dd6cb09607a'
#>

function Set-DSCPullServerAdminDevice {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_MDB')]
        [DSCDevice] $InputObject,

        [Parameter()]
        [guid] $ConfigurationID,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateNotNullOrEmpty()]
        [string] $TargetName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string] $ServerCheckSum,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string] $TargetCheckSum,

        [Parameter()]
        [bool] $NodeCompliant,

        [Parameter()]
        [nullable[datetime]] $LastComplianceTime,

        [Parameter()]
        [nullable[datetime]] $LastHeartbeatTime,

        [Parameter()]
        [bool] $Dirty,

        [Parameter()]
        [uint32] $StatusCode,

        [Parameter(ParameterSetName = 'InputObject_Connection')]
        [Parameter(ParameterSetName = 'Manual_Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_MDB')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection') -and $null -eq $script:GetConnection) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        } elseif ($null -ne $script:GetConnection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $script:GetConnection)
        } elseif ($null -ne $script:GetConnection) {
            $PSBoundParameters.Connection = $script:GetConnection
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        if (-not $PSBoundParameters.ContainsKey('InputObject')) {
            $existingDevice = Get-DSCPullServerAdminDevice -Connection $Connection -TargetName $TargetName
            if ($null -eq $existingDevice) {
                throw "A Device with TargetName '$TargetName' was not found"
            }
        } else {
            $existingDevice = $InputObject
        }

        $PSBoundParameters.Keys.Where{
            $_ -in ($existingDevice | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -ne 'Status'} ).Name
        }.ForEach{
            if ($null -ne $PSBoundParameters.$_) {
                $existingDevice.$_ = $PSBoundParameters.$_
            }
        }

        switch ($Connection.Type) {
            ESE {
                if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                    if ($PSCmdlet.MyInvocation.PipelinePosition -gt 1) {
                        Set-DSCPullServerESERecord -Connection $Connection -InputObject $existingDevice
                    } else {
                        Get-DSCPullServerAdminDevice -Connection $Connection -TargetName $existingDevice.TargetName |
                            Set-DSCPullServerESERecord -Connection $Connection -InputObject $existingDevice
                    }
                }
            }
            SQL {
                $tsqlScript = $existingDevice.GetSQLUpdate()

                if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                    Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
            MDB {
                $tsqlScript = $existingDevice.GetMDBUpdate()

                if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                    Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Set a connection that is stored in memory to be Active.
 
    .DESCRIPTION
    This function is used to set an existing connections for either SQL Databases
    or EDB files to be the Active connection.
 
    .PARAMETER Connection
    The connection object to be made active.
 
    .EXAMPLE
    $connection = Get-DSCPullServerAdminConnection -Index 4
    Set-DSCPullServerAdminConnectionActive -Connection $connection
#>

function Set-DSCPullServerAdminConnectionActive {
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseShouldProcessForStateChangingFunctions', '')]
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerConnection] $Connection
    )
    $currentActive = Get-DSCPullServerAdminConnection -OnlyShowActive
    if ($null -ne $currentActive) {
        $currentActive.Active = $false
    }
    $Connection.Active = $true
}

<#
    .SYNOPSIS
    Removes status report entries (LCMv2) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send reports
    to the Pull Server which stores their data in the StatusReport table.
    This function will remove status reports from the StatusReport table.
 
    .PARAMETER InputObject
    Pass in the status report object to be removed from the database.
 
    .PARAMETER JobId
    Define the JobId of the status report to be removed from the database.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Remove-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a'
 
    .EXAMPLE
    Get-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a' | Remove-DSCPullServerAdminStatusReport
#>

function Remove-DSCPullServerAdminStatusReport {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_MDB')]
        [DSCNodeStatusReport] $InputObject,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [guid] $JobId,

        [Parameter(ParameterSetName = 'InputObject_Connection')]
        [Parameter(ParameterSetName = 'Manual_Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_MDB')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection') -and $null -eq $script:GetConnection) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        } elseif ($null -ne $script:GetConnection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $script:GetConnection)
        } elseif ($null -ne $script:GetConnection) {
            $PSBoundParameters.Connection = $script:GetConnection
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        if (-not $PSBoundParameters.ContainsKey('InputObject')) {
            $existingReport = Get-DSCPullServerAdminStatusReport -Connection $Connection -JobId $JobId
        } else {
            $existingReport = $InputObject
        }

        if ($null -eq $existingReport) {
            Write-Warning -Message "A Report with JobId '$JobId' was not found"
        } else {
            switch ($Connection.Type) {
                ESE {
                    if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                        if ($PSCmdlet.MyInvocation.PipelinePosition -gt 1) {
                            Remove-DSCPullServerESERecord -Connection $Connection
                        } else {
                            Get-DSCPullServerAdminStatusReport -Connection $Connection -JobId $existingReport.JobId |
                                Remove-DSCPullServerESERecord -Connection $Connection
                        }
                    }
                }
                SQL {
                    $tsqlScript = $existingReport.GetSQLDelete()

                    if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                        Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
                MDB {
                    $tsqlScript = $existingReport.GetMDBDelete()

                    if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                        Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Removes node registration entries (LCMv2) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send information
    to the Pull Server which stores their data in the registrationdata table.
    This function will remove node registrations from the registrationdata table.
 
    .PARAMETER InputObject
    Pass in the registration object to be removed from the database.
 
    .PARAMETER AgentId
    Define the AgentId of the registration to be removed from the database.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Remove-DSCPullServerAdminRegistration -AgentId '80ee20f9-78df-480d-8175-9dd6cb09607a'
 
    .EXAMPLE
    Get-DSCPullServerAdminRegistration -TargetName '80ee20f9-78df-480d-8175-9dd6cb09607a' | Remove-DSCPullServerAdminRegistration
#>

function Remove-DSCPullServerAdminRegistration {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_MDB')]
        [DSCNodeRegistration] $InputObject,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [guid] $AgentId,

        [Parameter(ParameterSetName = 'InputObject_Connection')]
        [Parameter(ParameterSetName = 'Manual_Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_MDB')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection') -and $null -eq $script:GetConnection) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        } elseif ($null -ne $script:GetConnection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $script:GetConnection)
        } elseif ($null -ne $script:GetConnection) {
            $PSBoundParameters.Connection = $script:GetConnection
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        if (-not $PSBoundParameters.ContainsKey('InputObject')) {
            $existingRegistration = Get-DSCPullServerAdminRegistration -Connection $Connection -AgentId $AgentId
        } else {
            $existingRegistration = $InputObject
        }

        if ($null -eq $existingRegistration) {
            Write-Warning -Message "A NodeRegistration with AgentId '$AgentId' was not found"
        } else {
            switch ($Connection.Type) {
                ESE {
                    if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                        if ($PSCmdlet.MyInvocation.PipelinePosition -gt 1) {
                            Remove-DSCPullServerESERecord -Connection $Connection
                        } else {
                            Get-DSCPullServerAdminRegistration -Connection $Connection -AgentId $existingRegistration.AgentId |
                                Remove-DSCPullServerESERecord -Connection $Connection
                        }
                    }
                }
                SQL {
                    $tsqlScript = $existingRegistration.GetSQLDelete()

                    if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                        Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
                MDB {
                    $tsqlScript = $existingRegistration.GetMDBDelete()

                    if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                        Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Removes device entries (LCMv1) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv1 (WMF4 / PowerShell 4.0) pull clients send information
    to the Pull Server which stores their data in the devices table.
    This function will remove devices from the devices table.
 
    .PARAMETER InputObject
    Pass in the device object to be removed from the database.
 
    .PARAMETER TargetName
    Define the TargetName of the device to be removed from the database.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Remove-DSCPullServerAdminDevice -TargetName '192.168.0.1'
 
    .EXAMPLE
    Get-DSCPullServerAdminDevice -TargetName '192.168.0.1' | Remove-DSCPullServerAdminDevice
#>

function Remove-DSCPullServerAdminDevice {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_MDB')]
        [DSCDevice] $InputObject,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [string] $TargetName,

        [Parameter(ParameterSetName = 'InputObject_Connection')]
        [Parameter(ParameterSetName = 'Manual_Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_MDB')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection') -and $null -eq $script:GetConnection) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        } elseif ($null -ne $script:GetConnection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $script:GetConnection)
        } elseif ($null -ne $script:GetConnection) {
            $PSBoundParameters.Connection = $script:GetConnection
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        if (-not $PSBoundParameters.ContainsKey('InputObject')) {
            $existingDevice = Get-DSCPullServerAdminDevice -Connection $Connection -TargetName $TargetName
        } else {
            $existingDevice = $InputObject
        }

        if ($null -eq $existingDevice) {
            Write-Warning -Message "A Device with TargetName '$TargetName' was not found"
        } else {
            switch ($Connection.Type) {
                ESE {
                    if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                        if ($PSCmdlet.MyInvocation.PipelinePosition -gt 1) {
                            Remove-DSCPullServerESERecord -Connection $Connection
                        } else {
                            Get-DSCPullServerAdminDevice -Connection $Connection -TargetName $existingDevice.TargetName |
                                Remove-DSCPullServerESERecord -Connection $Connection
                        }
                    }
                }
                SQL {
                    $tsqlScript = $existingDevice.GetSQLDelete()

                    if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                        Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
                MDB {
                    $tsqlScript = $existingDevice.GetSQLDelete()

                    if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                        Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                    }
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Removes stored ESE and SQL connections from memory.
 
    .DESCRIPTION
    Connection objects created by New-DSCPullServerAdminConnection
    are stored in memory. This allows for multiple connections to
    exist simultaneously in the same session. When a connection can
    be disposed, this function allows you to remove it.
 
    .PARAMETER Connection
    The connection object to be removed from memory.
 
    .EXAMPLE
    Get-DSCPullServerAdminConnection -Index 4 | Remove-DSCPullServerAdminConnection
#>

function Remove-DSCPullServerAdminConnection {
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseShouldProcessForStateChangingFunctions', '')]
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [DSCPullServerConnection] $Connection
    )
    if ($Connection.Active) {
        Write-Warning -Message 'Removing Current Active Connection, please select or add a new one'
    }
    if ($Connection.Type -eq [DSCPullServerConnectionType]::ESE -and $null -ne $Connection.SessionId) {
        Dismount-DSCPullServerESEDatabase -Connection $Connection
    }

    for ($i = 0; $i -lt $script:DSCPullServerConnections.Count; $i++) {
        if ($script:DSCPullServerConnections[$i].Equals($Connection)) {
            $script:DSCPullServerConnections.RemoveAt($i)
        }
    }
}

<#
    .SYNOPSIS
    Creates status report entries (LCMv2) in a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send reports
    to the Pull Server which stores their data in the StatusReport table.
    This function will allow for manual creation of status reports
    in the StatusReport table and allows for multiple properties to be set.
 
    .PARAMETER JobId
    Set the JobId property for the new device.
 
    .PARAMETER Id
    Set the Id property for the new device.
 
    .PARAMETER OperationType
    Set the OperationType property for the new device.
 
    .PARAMETER RefreshMode
    Set the RefreshMode property for the new device.
 
    .PARAMETER Status
    Set the Status property for the new device.
 
    .PARAMETER LCMVersion
    Set the LCMVersion property for the new device.
 
    .PARAMETER ReportFormatVersion
    Set the ReportFormatVersion property for the new device.
 
    .PARAMETER ConfigurationVersion
    Set the ConfigurationVersion property for the new device.
 
    .PARAMETER NodeName
    Set the NodeName property for the new device.
 
    .PARAMETER IPAddress
    Set the IPAddress property for the new device.
 
    .PARAMETER StartTime
    Set the StartTime property for the new device.
 
    .PARAMETER EndTime
    Set the EndTime property for the new device.
 
    .PARAMETER LastModifiedTime
    Set the LastModifiedTime property for the new device.
 
    .PARAMETER Errors
    Set the Errors property for the new device.
 
    .PARAMETER StatusData
    Set the StatusData property for the new device.
 
    .PARAMETER RebootRequested
    Set the RebootRequested property for the new device.
 
    .PARAMETER AdditionalData
    Set the AdditionalData property for the new device.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    New-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a' -NodeName 'lcmclient01'
#>

function New-DSCPullServerAdminStatusReport {
    [CmdletBinding(
        DefaultParameterSetName = 'Connection',
        ConfirmImpact = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [guid] $JobId,

        [Parameter(ValueFromPipelineByPropertyName)]
        [Guid] $Id = [guid]::NewGuid(),

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $OperationType,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $RefreshMode,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $Status,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $LCMVersion,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $ReportFormatVersion,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $ConfigurationVersion,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string] $NodeName,

        [Parameter(ValueFromPipelineByPropertyName)]
        [IPAddress[]] $IPAddress,

        [Parameter(ValueFromPipelineByPropertyName)]
        [nullable[datetime]] $StartTime,

        [Parameter(ValueFromPipelineByPropertyName)]
        [nullable[datetime]] $EndTime,

        [Parameter(ValueFromPipelineByPropertyName)]
        [nullable[datetime]] $LastModifiedTime,

        [Parameter(ValueFromPipelineByPropertyName)]
        [PSObject[]] $Errors,

        [Parameter(ValueFromPipelineByPropertyName)]
        [PSObject[]] $StatusData,

        [Parameter(ValueFromPipelineByPropertyName)]
        [bool] $RebootRequested,

        [Parameter(ValueFromPipelineByPropertyName)]
        [PSObject[]] $AdditionalData,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        $report = [DSCNodeStatusReport]::new()
        $PSBoundParameters.Keys.Where{
            $_ -in ($report | Get-Member -MemberType Property).Name
        }.ForEach{
            $report.$_ = $PSBoundParameters.$_
        }

        $existingReport = Get-DSCPullServerAdminStatusReport -Connection $Connection -JobId $report.JobId
        if ($null -ne $existingReport) {
            throw "A Report with JobId '$JobId' already exists."
        }

        switch ($Connection.Type) {
            ESE {
                if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                    try {
                        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode None
                        Open-DSCPullServerTable -Connection $Connection -Table 'StatusReport'
                        Set-DSCPullServerESERecord -Connection $Connection -InputObject $report -Insert
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Stop
                    } finally {
                        Dismount-DSCPullServerESEDatabase -Connection $Connection
                    }
                }
            }
            SQL {
                $tsqlScript = $report.GetSQLInsert()

                if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                    Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
            MDB {
                $tsqlScript = $report.GetMDBInsert()

                if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                    Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Creates a DSC Pull Server SQL Database.
 
    .DESCRIPTION
    Normally, the DSC Pull Server database is created when the first
    interaction with the Pull Server takes place. This function allows
    for prestaging the database.
 
    .PARAMETER SQLServer
    Define the SQL Instance where the database should be created.
 
    .PARAMETER Credential
    Define the Credentials to be used with the SQL Server connection.
 
    .PARAMETER Name
    Define the Database name to create.
 
    .EXAMPLE
    New-DSCPullServerAdminSQLDatabase -SQLServer sqlserver\instance -Name dscdb
#>

function New-DSCPullServerAdminSQLDatabase {
    [CmdletBinding(
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param(
        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [Alias('Database')]
        [string] $Name
    )

    $connection = [DSCPullServerSQLConnection]::new($SQLServer)
    if ($PSBoundParameters.ContainsKey('Credential')) {
        $connection.Credential = $Credential
    }

    $dbExists = Test-DSCPullServerDatabaseExist @PSBoundParameters -ErrorAction Stop
    if ($dbExists) {
        Write-Warning -Message "Database $Name on $SQLServer already exists"
    } else {
        $createDbScript = "CREATE DATABASE {0}"-f $Name
        if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$Name", $createDbScript)) {
            Invoke-DSCPullServerSQLCommand -Connection $connection -CommandType Set -Script $createDbScript -CommandTimeOut 600
        }
        $connection.Database = $Name
        @(
            "CREATE TABLE [dbo].[Devices] ([TargetName] VARCHAR (255) NOT NULL,[ConfigurationID] VARCHAR (255) NOT NULL,[ServerCheckSum] VARCHAR (255) NOT NULL,[TargetCheckSum] VARCHAR (255) NOT NULL,[NodeCompliant] BIT DEFAULT ((0)) NOT NULL,[LastComplianceTime] DATETIME NULL,[LastHeartbeatTime] DATETIME NULL,[Dirty] BIT DEFAULT ((1)) NULL,[StatusCode] INT DEFAULT ((-1)) NULL);",
            "CREATE TABLE [dbo].[RegistrationData] ([AgentId] VARCHAR (MAX) NOT NULL,[LCMVersion] VARCHAR (255) NULL,[NodeName] VARCHAR (255) NULL,[IPAddress] VARCHAR (255) NULL,[ConfigurationNames] VARCHAR (MAX) NULL);",
            "CREATE TABLE [dbo].[StatusReport] ([JobId] VARCHAR (255) NOT NULL,[Id] VARCHAR (255) NOT NULL,[OperationType] VARCHAR (255) NULL,[RefreshMode] VARCHAR (255) NULL,[Status] VARCHAR (255) NULL,[LCMVersion] VARCHAR (255) NULL,[ReportFormatVersion] VARCHAR (255) NULL,[ConfigurationVersion] VARCHAR (255) NULL,[NodeName] VARCHAR (255) NULL,[IPAddress] VARCHAR (255) NULL,[StartTime] DATETIME DEFAULT (getdate()) NULL,[EndTime] DATETIME DEFAULT (getdate()) NULL,[Errors] VARCHAR (MAX) NULL,[StatusData] VARCHAR (MAX) NULL,[RebootRequested] VARCHAR (255) NULL,[AdditionalData]VARCHAR (MAX) NULL);"
        ) | ForEach-Object -Process {
            if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$Name", $_)) {
                Invoke-DSCPullServerSQLCommand -Connection $connection -CommandType Set -Script $_ -CommandTimeOut 300
            }
        }
    }
}

<#
    .SYNOPSIS
    Creates node registration entries (LCMv2) in a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send information
    to the Pull Server which stores their data in the registrationdata table.
    This function will allow for manual creation of registrations in the
    registrationdata table and allows for multiple properties to be set.
 
    .PARAMETER AgentId
    Set the AgentId property for the new device.
 
    .PARAMETER LCMVersion
    Set the LCMVersion property for the new device.
 
    .PARAMETER NodeName
    Set the NodeName property for the new device.
 
    .PARAMETER IPAddress
    Set the IPAddress property for the new device.
 
    .PARAMETER ConfigurationNames
    Set the ConfigurationNames property for the new device.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    New-DSCPullServerAdminRegistration -AgentId '80ee20f9-78df-480d-8175-9dd6cb09607a' -NodeName 'lcmclient01'
#>

function New-DSCPullServerAdminRegistration {
    [CmdletBinding(
        DefaultParameterSetName = 'Connection',
        ConfirmImpact = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [guid] $AgentId,

        [Parameter(ValueFromPipelineByPropertyName)]
        [ValidateSet('2.0')]
        [string] $LCMVersion = '2.0',

        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [string] $NodeName,

        [Parameter(ValueFromPipelineByPropertyName)]
        [IPAddress[]] $IPAddress,

        [Parameter(ValueFromPipelineByPropertyName)]
        [string[]] $ConfigurationNames,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
        if (-not $PSBoundParameters.ContainsKey('LCMVersion')) {
            $PSBoundParameters.Add('LCMVersion', $LCMVersion)
        }
    }
    process {
        $nodeRegistration = [DSCNodeRegistration]::new()
        $PSBoundParameters.Keys.Where{
            $_ -in ($nodeRegistration | Get-Member -MemberType Property).Name
        }.ForEach{
            $nodeRegistration.$_ = $PSBoundParameters.$_
        }

        $existingRegistration = Get-DSCPullServerAdminRegistration -Connection $Connection -AgentId $nodeRegistration.AgentId
        if ($null -ne $existingRegistration) {
            throw "A NodeRegistration with AgentId '$AgentId' already exists."
        }

        switch ($Connection.Type) {
            ESE {
                if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                    try {
                        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode None
                        Open-DSCPullServerTable -Connection $Connection -Table 'RegistrationData'
                        Set-DSCPullServerESERecord -Connection $Connection -InputObject $nodeRegistration -Insert
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Stop
                    } finally {
                        Dismount-DSCPullServerESEDatabase -Connection $Connection
                    }
                }
            }
            SQL {
                $tsqlScript = $nodeRegistration.GetSQLInsert()

                if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                    Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
            MDB {
                $tsqlScript = $nodeRegistration.GetMDBInsert()

                if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                    Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Create device entries (LCMv1) in a Pull Server Database.
 
    .DESCRIPTION
    LCMv1 (WMF4 / PowerShell 4.0) pull clients send information
    to the Pull Server which stores their data in the devices table.
    This function will allow for manual creation of devices in the
    devices table and allows for multiple properties to be set.
 
    .PARAMETER ConfigurationID
    Set the ConfigurationID property for the new device.
 
    .PARAMETER TargetName
    Set the TargetName property for the new device.
 
    .PARAMETER ServerCheckSum
    Set the ServerCheckSum property for the new device.
 
    .PARAMETER TargetCheckSum
    Set the TargetCheckSum property for the new device.
 
    .PARAMETER NodeCompliant
    Set the NodeCompliant property for the new device.
 
    .PARAMETER LastComplianceTime
    Set the LastComplianceTime property for the new device.
 
    .PARAMETER LastHeartbeatTime
    Set the LastHeartbeatTime property for the new device.
 
    .PARAMETER Dirty
    Set the Dirty property for the new device.
 
    .PARAMETER StatusCode
    Set the StatusCode property for the new device.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    New-DSCPullServerAdminDevice -ConfigurationID '80ee20f9-78df-480d-8175-9dd6cb09607a' -TargetName '192.168.0.1'
#>

function New-DSCPullServerAdminDevice {
    [CmdletBinding(
        DefaultParameterSetName = 'Connection',
        ConfirmImpact = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [guid] $ConfigurationID,

        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [string] $TargetName,

        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [string] $ServerCheckSum,

        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [string] $TargetCheckSum,

        [Parameter(ValueFromPipelineByPropertyName)]
        [bool] $NodeCompliant,

        [Parameter(ValueFromPipelineByPropertyName)]
        [nullable[datetime]] $LastComplianceTime,

        [Parameter(ValueFromPipelineByPropertyName)]
        [nullable[datetime]] $LastHeartbeatTime,

        [Parameter(ValueFromPipelineByPropertyName)]
        [bool] $Dirty,

        [Parameter(ValueFromPipelineByPropertyName)]
        [uint32] $StatusCode,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [string] $Database
    )
    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        $device = [DSCDevice]::new()
        $PSBoundParameters.Keys.Where{
            $_ -in ($device | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -ne 'Status'} ).Name
        }.ForEach{
            $device.$_ = $PSBoundParameters.$_
        }

        $existingDevice = Get-DSCPullServerAdminDevice -Connection $Connection -TargetName $device.TargetName
        if ($null -ne $existingDevice) {
            throw "A Device with TargetName '$TargetName' already exists."
        }

        switch ($Connection.Type) {
            ESE {
                if ($PSCmdlet.ShouldProcess($Connection.ESEFilePath)) {
                    try {
                        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode None
                        Open-DSCPullServerTable -Connection $Connection -Table 'Devices'
                        Set-DSCPullServerESERecord -Connection $Connection -InputObject $device -Insert
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Stop
                    } finally {
                        Dismount-DSCPullServerESEDatabase -Connection $Connection
                    }
                }
            }
            SQL {
                $tsqlScript = $device.GetSQLInsert()

                if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                    Invoke-DSCPullServerSQLCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
            MDB {
                $tsqlScript = $device.GetMDBInsert()

                if ($PSCmdlet.ShouldProcess($Connection.MDBFilePath, $tsqlScript)) {
                    Invoke-DSCPullServerMDBCommand -Connection $Connection -CommandType Set -Script $tsqlScript
                }
            }
        }
    }
}

<#
    .SYNOPSIS
    Create a new connection with either a SQL Database or EDB file.
 
    .DESCRIPTION
    This function is used to create new connections for either SQL Databases
    or EDB files that are re-used for multiple tasks. More than one connection can
    be created in a PowerShell session. By default, connections are stored in memory
    and are visible via the Get-DSCPullServerAdminConnection function.
    Connections can be passed to other functions via parameter binding.
    The default connection is used by default for all other functions. The default
    connection can be modified with the Set-DSCPullServerAdminConnectionActive
    function.
 
    .PARAMETER ESEFilePath
    Specifies the path to the EDB file to be used for the connection.
 
    .PARAMETER MDBFilePath
    Specifies the path to the MDB file to be used for the connection.
 
    .PARAMETER SQLServer
    Specifies the SQL Instance to connect to for the connection.
 
    .PARAMETER Credential
    Specifies optional Credentials to use when connecting to the SQL Instance.
 
    .PARAMETER Database
    Specifies the Database name to use for the SQL connection.
 
    .PARAMETER DontStore
    When specified, the connection will not be stored in memory.
 
    .EXAMPLE
    New-DSCPullServerAdminConnection -ESEFilePath C:\Users\EDB\Devices.edb
 
    .EXAMPLE
    $sqlCredential = Get-Credential
    New-DSCPullServerAdminConnection -SQLServer sqlserver\instance -Database dscpulldb -Credential $sqlCredential
#>

function New-DSCPullServerAdminConnection {
    [Diagnostics.CodeAnalysis.SuppressMessage('PSUseShouldProcessForStateChangingFunctions', '')]
    [OutputType([DSCPullServerSQLConnection])]
    [OutputType([DSCPullServerESEConnection])]
    [CmdletBinding(DefaultParameterSetName = 'SQL')]
    param (
        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [string] $Database,

        [switch] $DontStore
    )

    if ($PSCmdlet.ParameterSetName -eq 'MDB') {
        Assert-DSCPullServerMDBPreReq
    }

    if ($PSCmdlet.ParameterSetName -eq 'ESE') {
        Assert-DSCPullServerESEPreReq
    }

    $currentConnections = Get-DSCPullServerAdminConnection
    $lastIndex = $currentConnections |
        Sort-Object -Property Index -Descending |
        Select-Object -First 1 -ExpandProperty Index

    if ($PSCmdlet.ParameterSetName -eq 'SQL') {
        if ($PSBoundParameters.ContainsKey('Credential') -and $PSBoundParameters.ContainsKey('Database')) {
            $connection = [DSCPullServerSQLConnection]::New($SQLServer, $Credential, $Database)
        } elseif ($PSBoundParameters.ContainsKey('Database')) {
            $connection = [DSCPullServerSQLConnection]::New($SQLServer, $Database)
        } elseif ($PSBoundParameters.ContainsKey('Credential')) {
            $connection = [DSCPullServerSQLConnection]::New($SQLServer, $Credential)
        } else {
            $connection = [DSCPullServerSQLConnection]::New($SQLServer)
        }
        if (-not (Test-DSCPullServerDatabaseExist -Connection $connection)) {
            Write-Error -Message "Could not find database with name $($connection.Database) at $($connection.SQLServer)" -ErrorAction Stop
        }
    } elseif ($PSCmdlet.ParameterSetName -eq 'MDB') {
        $connection = [DSCPullServerMDBConnection]::New($MDBFilePath)
    } else {
        $connection = [DSCPullServerESEConnection]::New($ESEFilePath)
    }

    if (-not (Test-DSCPullServerDatabase -Connection $connection)) {
        Write-Error -Message "Invalid PullServer Database" -ErrorAction Stop
    }

    if (-not $DontStore) {
        if ($null -eq $currentConnections) {
            $connection.Index = 0
            $connection.Active = $true
        } else {
            $connection.Index = $lastIndex + 1
            $connection.Active = $false
        }
        if($null -eq $script:DSCPullServerConnections) {
            $script:DSCPullServerConnections = [System.Collections.ArrayList]::new()
        }
        [void] $script:DSCPullServerConnections.Add($connection)
    }
    $connection
}

<#
    .SYNOPSIS
    Get status report entries (LCMv2) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send reports
    to the Pull Server which stores their data in the StatusReport table.
    This function will return status reports from the StatusReport table
    and allows for multiple types of filtering.
 
    .PARAMETER AgentId
    Return the reports with the specific AgentId.
 
    .PARAMETER NodeName
    Return the reports with the specific NodeName.
    Wildcards are supported for SQL and ESE connections but not for MDB connection.
 
    .PARAMETER JobId
    Return the reports with the specific JobId (Key).
 
    .PARAMETER FromStartTime
    Return the reports which start from the specific FromStartTime.
 
    .PARAMETER ToStartTime
    Return the reports which start no later than the specific ToStartTime.
 
    .PARAMETER All
    Return all reports that correspond to specified filters (overwrites Top parameter).
    SQL Only.
 
    .PARAMETER Top
    Return number of reports that correspond to specified filters.
    SQL Only.
 
    .PARAMETER OperationType
    Return the reports which have the specified OperationType.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Get-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a'
#>

function Get-DSCPullServerAdminStatusReport {
    [OutputType([DSCNodeStatusReport])]
    [CmdletBinding(DefaultParameterSetName = 'Connection')]
    param (
        [Parameter()]
        [guid] $AgentId,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [SupportsWildcards()]
        [Alias('Name')]
        [string] $NodeName,

        [Parameter()]
        [guid] $JobId,

        [Parameter()]
        [datetime] $FromStartTime,

        [Parameter()]
        [datetime] $ToStartTime,

        [Parameter()]
        [switch] $All,

        [Parameter()]
        [uint16] $Top = 5,

        [Parameter()]
        [ValidateSet('All', 'LocalConfigurationManager', 'Consistency', 'Initial')]
        [string] $OperationType = 'All',

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [string] $Database
    )

    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        } else {
            $script:GetConnection = $Connection
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                    OperationType = $OperationType
                    Table = 'StatusReport'
                }
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    $eseParams.Add('AgentId', $AgentId)
                }
                if ($PSBoundParameters.ContainsKey('NodeName')) {
                    $eseParams.Add('NodeName', $NodeName)
                }
                if ($PSBoundParameters.ContainsKey('FromStartTime')) {
                    $eseParams.Add('FromStartTime', $FromStartTime)
                }
                if ($PSBoundParameters.ContainsKey('ToStartTime')) {
                    $eseParams.Add('ToStartTime', $ToStartTime)
                }
                if ($PSBoundParameters.ContainsKey('JobId')) {
                    $eseParams.Add('JobId', $JobId)
                }
                if (-not ($PSBoundParameters.ContainsKey('All')) -and ($PSBoundParameters.ContainsKey('Top'))) {
                    $eseParams.Add('Top', $Top)
                }

                Get-DSCPullServerESERecord @eseParams
            }
            SQL {
                if ($PSBoundParameters.ContainsKey('All')) {
                    $tsqlScript = 'SELECT * FROM StatusReport'
                } else {
                    $tsqlScript = 'SELECT TOP({0}) * FROM StatusReport' -f $Top
                }
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    [void] $filters.Add(("Id = '{0}'" -f $AgentId))
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    [void] $filters.Add(("NodeName like '{0}'" -f $NodeName.Replace('*', '%').Replace('?', '_')))
                }
                if ($PSBoundParameters.ContainsKey("FromStartTime")) {
                    [void] $filters.Add(("StartTime >= '{0}'" -f (Get-Date $FromStartTime -f s)))
                }
                if ($PSBoundParameters.ContainsKey("ToStartTime")) {
                    [void] $filters.Add(("StartTime <= '{0}'" -f (Get-Date $ToStartTime -f s)))
                }
                if ($PSBoundParameters.ContainsKey("JobId")) {
                    [void] $filters.Add(("JobId = '{0}'" -f $JobId))
                }

                if ($OperationType -ne 'All') {
                    [void] $filters.Add("OperationType = '{0}'" -f $OperationType)
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerSQLCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCNodeStatusReport]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
            MDB {
                if ($PSBoundParameters.ContainsKey('All')) {
                    $tsqlScript = 'SELECT * FROM StatusReport'
                } else {
                    $tsqlScript = 'SELECT TOP {0} * FROM StatusReport' -f $Top
                }
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    [void] $filters.Add(("Id = '{0}'" -f $AgentId))
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    if ([System.Management.Automation.WildcardPattern]::ContainsWildcardCharacters($NodeName)) {
                        Write-Error -Message "MDB connection does not support wildcards for NodeName" -ErrorAction Stop
                    } else {
                        [void] $filters.Add(("NodeName = '{0}'" -f $NodeName))
                    }
                }
                if ($PSBoundParameters.ContainsKey("FromStartTime")) {
                    [void] $filters.Add(("StartTime >= '{0}'" -f (Get-Date $FromStartTime -f s)))
                }
                if ($PSBoundParameters.ContainsKey("ToStartTime")) {
                    [void] $filters.Add(("StartTime <= '{0}'" -f (Get-Date $ToStartTime -f s)))
                }
                if ($PSBoundParameters.ContainsKey("JobId")) {
                    [void] $filters.Add(("JobId = '{0}'" -f $JobId))
                }

                if ($OperationType -ne 'All') {
                    [void] $filters.Add("OperationType = '{0}'" -f $OperationType)
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerMDBCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCNodeStatusReport]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
        }
    }
    end {
        $script:GetConnection = $null
    }
}

<#
    .SYNOPSIS
    Get node registration entries (LCMv2) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send information
    to the Pull Server which stores their data in the registrationdata table.
    This function will return node registrations from the registrationdata table
    and allows for multiple types of filtering.
 
    .PARAMETER AgentId
    Return the registation with the specific AgentId (Key).
 
    .PARAMETER NodeName
    Return the registation with the specific NodeName (Non-key, could be more than 1 result).
    Wildcards are supported for SQL and ESE connections but not for MDB connection.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Get-DSCPullServerAdminRegistration -AgentId '80ee20f9-78df-480d-8175-9dd6cb09607a'
#>

function Get-DSCPullServerAdminRegistration {
    [OutputType([DSCNodeRegistration])]
    [CmdletBinding(DefaultParameterSetName = 'Connection')]
    param (
        [Parameter()]
        [guid] $AgentId,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [SupportsWildcards()]
        [Alias('Name')]
        [string] $NodeName,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [string] $Database
    )

    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        } else {
            $script:GetConnection = $Connection
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                    Table = 'RegistrationData'
                }
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    $eseParams.Add('AgentId', $AgentId)
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    $eseParams.Add('NodeName', $NodeName)
                }

                Get-DSCPullServerESERecord @eseParams
            }
            SQL {
                $tsqlScript = 'SELECT * FROM RegistrationData'
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    [void] $filters.Add(("AgentId = '{0}'" -f $AgentId))
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    [void] $filters.Add(("NodeName like '{0}'" -f $NodeName.Replace('*', '%').Replace('?', '_')))
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerSQLCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCNodeRegistration]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
            MDB {
                $tsqlScript = 'SELECT * FROM RegistrationData'
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    [void] $filters.Add(("AgentId = '{0}'" -f $AgentId))
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    if ([System.Management.Automation.WildcardPattern]::ContainsWildcardCharacters($NodeName)) {
                        Write-Error -Message "MDB connection does not support wildcards for NodeName" -ErrorAction Stop
                    } else {
                        [void] $filters.Add(("NodeName = '{0}'" -f $NodeName))
                    }
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerMDBCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCNodeRegistration]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
        }
    }
    end {
        $script:GetConnection = $null
    }
}

<#
    .SYNOPSIS
    Get device entries (LCMv1) from a Pull Server Database.
 
    .DESCRIPTION
    LCMv1 (WMF4 / PowerShell 4.0) pull clients send information
    to the Pull Server which stores their data in the devices table.
    This function will return devices from the devices table and allows
    for multiple types of filtering.
 
    .PARAMETER TargetName
    Return the device with the specific TargetName.
    Wildcards are supported for SQL and ESE connections but not for MDB connection.
 
    .PARAMETER ConfigurationID
    Return all devices with the same ConfigurationID.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Get-DSCPullServerAdminDevice -TargetName '192.168.0.1'
 
    .EXAMPLE
    Get-DSCPullServerAdminDevice
#>

function Get-DSCPullServerAdminDevice {
    [OutputType([DSCDevice])]
    [CmdletBinding(DefaultParameterSetName = 'Connection')]
    param (
        [Parameter()]
        [SupportsWildcards()]
        [ValidateNotNullOrEmpty()]
        [String] $TargetName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [guid] $ConfigurationID,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'SQL')]
        [ValidateNotNullOrEmpty()]
        [string] $Database
    )

    begin {
        if ($null -ne $Connection -and -not $PSBoundParameters.ContainsKey('Connection')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        } else {
            $script:GetConnection = $Connection
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                    Table = 'Devices'
                }
                if ($PSBoundParameters.ContainsKey('TargetName')) {
                    $eseParams.Add('TargetName', $TargetName)
                }
                if ($PSBoundParameters.ContainsKey('ConfigurationID')) {
                    $eseParams.Add('ConfigurationID', $ConfigurationID)
                }

                Get-DSCPullServerESERecord @eseParams
            }
            SQL {
                $tsqlScript = 'SELECT * FROM Devices'
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey("TargetName")) {
                    [void] $filters.Add(("TargetName like '{0}'" -f $TargetName.Replace('*', '%').Replace('?', '_')))
                }
                if ($PSBoundParameters.ContainsKey("ConfigurationID")) {
                    [void] $filters.Add(("ConfigurationID = '{0}'" -f $ConfigurationID))
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerSQLCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCDevice]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
            MDB {
                $tsqlScript = 'SELECT * FROM Devices'
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey("TargetName")) {
                    if ([System.Management.Automation.WildcardPattern]::ContainsWildcardCharacters($TargetName)) {
                        Write-Error -Message "MDB connection does not support wildcards for TargetName" -ErrorAction Stop
                    } else {
                        [void] $filters.Add(("TargetName = '{0}'" -f $TargetName))
                    }
                }
                if ($PSBoundParameters.ContainsKey("ConfigurationID")) {
                    [void] $filters.Add(("ConfigurationID = '{0}'" -f $ConfigurationID))
                }

                if ($filters.Count -ge 1) {
                    $tsqlScript += " WHERE {0}" -f ($filters -join ' AND ')
                }

                Invoke-DSCPullServerMDBCommand -Connection $Connection -Script $tsqlScript | ForEach-Object {
                    try {
                        [DSCDevice]::New($_)
                    } catch {
                        Write-Error -ErrorRecord $_ -ErrorAction Continue
                    }
                }
            }
        }
    }
    end {
        $script:GetConnection = $null
    }
}

<#
    .SYNOPSIS
    Get stored ESE and SQL connections from memory.
 
    .DESCRIPTION
    Connection objects created by New-DSCPullServerAdminConnection
    are stored in memory. This allows for multiple connections to
    exist simultaneously in the same session. This function will
    return the existing connections and allows for multiple types of
    filtering.
 
    .PARAMETER Type
    Filter output on Connection type.
 
    .PARAMETER OnlyShowActive
    Only return the current Active connection.
 
    .PARAMETER Index
    Return a specific Connection based on it's index number.
 
    .EXAMPLE
    Get-DSCPullServerAdminConnection -OnlyShowActive
 
    .EXAMPLE
    Get-DSCPullServerAdminConnection
#>

function Get-DSCPullServerAdminConnection {
    [OutputType([DSCPullServerSQLConnection])]
    [OutputType([DSCPullServerESEConnection])]
    [OutputType([DSCPullServerMDBConnection])]
    [CmdletBinding()]
    param (
        [Parameter()]
        [DSCPullServerConnectionType] $Type,

        [switch] $OnlyShowActive,

        [Parameter()]
        [uint16] $Index
    )
    if ($PSBoundParameters.ContainsKey('Type')) {
        $result = $script:DSCPullServerConnections | Where-Object -FilterScript {
            $_.Type -eq $Type
        }
    } else {
        $result = $script:DSCPullServerConnections
    }

    if ($PSBoundParameters.ContainsKey('Index')) {
        $result = $result | Where-Object -FilterScript {
            $_.Index -eq $Index
        }
    }

    if ($OnlyShowActive) {
        $result | Where-Object -FilterScript {
            $_.Active
        }
    } else {
        $result
    }
}

<#
    .SYNOPSIS
    Overwrites node registration entries (LCMv2) in a Pull Server Database.
 
    .DESCRIPTION
    LCMv2 (WMF5+ / PowerShell 5+) pull clients send information
    to the Pull Server which stores their data in the registrationdata table.
    This function will allow for manual overwrites of registrations properties
    in the registrationdata table.
 
    .PARAMETER InputObject
    Pass in the registration object to be modified from the database.
 
    .PARAMETER AgentId
    Modify properties for the registration with specified AgentId.
 
    .PARAMETER LCMVersion
    Set the LCMVersion property for the existing device.
 
    .PARAMETER NodeName
    Set the NodeName property for the existing device.
 
    .PARAMETER IPAddress
    Set the IPAddress property for the existing device.
 
    .PARAMETER ConfigurationNames
    Set the ConfigurationNames property for the existing device.
 
    .PARAMETER Connection
    Accepts a specific Connection to be passed to target a specific database.
    When not specified, the currently Active Connection from memory will be used
    unless one off the parameters for ad-hoc connections (ESEFilePath, SQLServer)
    is used in which case, an ad-hoc connection is created.
 
    .PARAMETER ESEFilePath
    Define the EDB file path to use an ad-hoc ESE connection.
 
    .PARAMETER MDBFilePath
    Define the MDB file path to use an ad-hoc MDB connection.
 
    .PARAMETER SQLServer
    Define the SQL Instance to use in an ad-hoc SQL connection.
 
    .PARAMETER Credential
    Define the Credentials to use with an ad-hoc SQL connection.
 
    .PARAMETER Database
    Define the database to use with an ad-hoc SQL connection.
 
    .EXAMPLE
    Set-DSCPullServerAdminRegistration -AgentId '80ee20f9-78df-480d-8175-9dd6cb09607a' -ConfigurationNames 'WebServer'
 
    .EXAMPLE
    Get-DSCPullServerAdminRegistration -AgentId '80ee20f9-78df-480d-8175-9dd6cb09607a' | Set-DSCPullServerAdminRegistration -ConfigurationNames 'WebServer'
#>

function Set-DSCPullServerAdminRegistration {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'High',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_MDB')]
        [DSCNodeRegistration] $InputObject,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [guid] $AgentId,

        [Parameter()]
        [ValidateSet('2.0')]
        [string] $LCMVersion = '2.0',

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string] $NodeName,

        [Parameter()]
        [IPAddress[]] $IPAddress,

        [Parameter()]
        [string[]] $ConfigurationNames,

        [Parameter(ParameterSetName = 'InputObject_Connection')]
        [Parameter(ParameterSetName = 'Manual_Connection')]
        [DSCPullServerConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive),

        [Parameter(Mandatory, ParameterSetName = 'InputObject_ESE')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_ESE')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'ESE'})]
        [System.IO.FileInfo] $ESEFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_MDB')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_MDB')]
        [ValidateScript({$_ | Assert-DSCPullServerDatabaseFilePath -Type 'MDB'})]
        [System.IO.FileInfo] $MDBFilePath,

        [Parameter(Mandatory, ParameterSetName = 'InputObject_SQL')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [ValidateNotNullOrEmpty()]
        [Alias('SQLInstance')]
        [string] $SQLServer,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName = 'Manual_SQL')]
        [pscredential] $Credential,

        [Parameter(ParameterSetName = 'InputObject_SQL')]
        [Parameter(ParameterSetName