DSCPullServerAdmin

0.1.4

dscpullserveradmin.psm1

enum DSCPullServerConnectionType {
    SQL
    ESE
}

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

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

    DSCPullServerESEConnection ([string]$Path) : base([DSCPullServerConnectionType]::ESE) {
        $this.ESEFilePath = (Resolve-Path $Path).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 DSCDevice {
    [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 () {}

    DSCDevice ([System.Data.Common.DbDataRecord] $Input) {
        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]
    }

    [string] GetSQLUpdate () {
        $query = "UPDATE Devices Set {0} WHERE TargetName = '{1}'" -f @(
            (($this | Get-Member -MemberType Property).Where{
                $_.Name -notin 'TargetName', 'Status'
            }.foreach{
                if ($_.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')
                    }
                } else {
                    "$($_.Name) = '{0}'" -f $this."$($_.Name)"
                }
            } -join ','),
            $this.TargetName
        )
        return $query
    }

    [string] GetSQLInsert () {
        $query = ("INSERT INTO Devices ({0}) VALUES ({1})" -f @(
            (($this | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -ne 'Status'}).Name -join ','),
            (($this | Get-Member -MemberType Property).ForEach{
                if ($_.Name -eq 'Status') {
                    return
                } else {
                    if ($_.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')
                        }
                    } else {
                        "'{0}'" -f $this."$($_.Name)"
                    }
                }
            } -join ',')
        ))
        return $query
    }

    [string] GetSQLDelete () {
        return ("DELETE FROM Devices WHERE TargetName = '{0}'" -f $this.TargetName)
    }
}

class DSCNodeRegistration {
    [Guid] $AgentId

    [string] $LCMVersion

    [string] $NodeName

    [IPAddress[]] $IPAddress

    [string[]] $ConfigurationNames

    DSCNodeRegistration () {}

    DSCNodeRegistration ([System.Data.Common.DbDataRecord] $Input) {
        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
        }
    }

    [string] GetSQLUpdate () {
        $query = "UPDATE RegistrationData Set {0} WHERE AgentId = '{1}'" -f @(
            (($this | Get-Member -MemberType Property).Where{
                $_.Name -ne 'AgentId'
            }.foreach{
                if ($_.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 ';')
                } else {
                    "$($_.Name) = '{0}'" -f $this."$($_.Name)"
                }
            } -join ','),
            $this.AgentId
        )
        return $query
    }

    [string] GetSQLInsert () {
        $query = ("INSERT INTO RegistrationData ({0}) VALUES ({1})" -f @(
            (($this | Get-Member -MemberType Property).Name -join ','),
            (($this | Get-Member -MemberType Property).ForEach{
                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 ';')
                } else {
                    "'{0}'" -f $this."$($_.Name)"
                }
            } -join ',')
        ))
        return $query
    }

    [string] GetSQLDelete () {
        return ("DELETE FROM RegistrationData WHERE AgentId = '{0}'" -f $this.AgentId)
    }
}

class DSCNodeStatusReport {
    [Guid] $JobId

    [Guid] $Id

    [string] $OperationType

    [string] $RefreshMode

    [string] $Status

    [string] $LCMVersion

    [string] $ReportFormatVersion

    [string] $ConfigurationVersion

    [string] $NodeName

    [IPAddress[]] $IPAddress

    [datetime] $StartTime

    [datetime] $EndTime

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

    [PSObject[]] $Errors

    [PSObject[]] $StatusData

    [bool] $RebootRequested

    [PSObject[]] $AdditionalData

    DSCNodeStatusReport () {}

    DSCNodeStatusReport ([System.Data.Common.DbDataRecord] $Input) {
        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
            }
        }
    }

    [string] GetSQLUpdate () {
        $query = "UPDATE StatusReport Set {0} WHERE JobId = '{1}'" -f @(
            (($this | Get-Member -MemberType Property).Where{
                $_.Name -ne 'JobId'
            }.foreach{
                if ($_.Name -eq 'LastModifiedTime') {
                    # skip as missing in SQL table, only present in EDB
                } elseif ($_.Name -eq 'IPAddress') {
                    "$($_.Name) = '{0}'" -f ($this."$($_.Name)" -join ';')
                } 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)
                } else {
                    if ($_.Definition.Split(' ')[0] -eq 'datetime') {
                        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')
                        }
                    } else {
                        "$($_.Name) = '{0}'" -f $this."$($_.Name)"
                    }
                }
            } -join ','),
            $this.JobId
        )
        return $query
    }

    [string] GetSQLInsert () {
        $query = ("INSERT INTO StatusReport ({0}) VALUES ({1})" -f @(
            (($this | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -ne 'LastModifiedTime'}).Name -join ','),
            (($this | Get-Member -MemberType Property).ForEach{
                if ($_.Name -eq 'LastModifiedTime') {
                    # skip as missing in SQL table, only present in EDB
                } 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)
                } else {
                    if ($_.Definition.Split(' ')[0] -eq 'datetime') {
                        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')
                        }
                    } else {
                        "'{0}'" -f $this."$($_.Name)"
                    }
                }
            } -join ',')
        ))
        return $query
    }

    [string] GetSQLDelete () {
        return ("DELETE FROM StatusReport WHERE JobId = '{0}'" -f $this.JobId)
    }
}

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
}

function Get-DSCPullServerESEDevice {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

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

        [Parameter()]
        [guid] $ConfigurationID
    )
    $table = 'Devices'
    [Microsoft.Isam.Esent.Interop.JET_TABLEID] $tableId = [Microsoft.Isam.Esent.Interop.JET_TABLEID]::Nil
    try {
        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode ReadOnly
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetOpenTable(
            $Connection.SessionId,
            $Connection.DbId,
            $Table,
            $null,
            0,
            [Microsoft.Isam.Esent.Interop.OpenTableGrbit]::None,
            [ref]$tableId
        )
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    }

    try {
        [Microsoft.Isam.Esent.Interop.Api]::MoveBeforeFirst($Connection.SessionId, $tableId)

        $stringColumns = @(
            'TargetName',
            'ServerCheckSum',
            'TargetChecksum'
        )

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

        $datetimeColumns = @(
            'LastComplianceTime',
            'LastHeartbeatTime'
        )

        while ([Microsoft.Isam.Esent.Interop.Api]::TryMoveNext($Connection.SessionId, $tableId)) {
            foreach ($column in ([Microsoft.Isam.Esent.Interop.Api]::GetTableColumns($Connection.SessionId, $tableId))) {
                $device = [DSCDevice]::new()
                if ($column.Name -in $stringColumns) {
                    $device."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    )
                } elseif ($column.Name -in $boolColumns) {
                    $row = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsBoolean(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                    if ($row.HasValue) {
                        $device."$($column.Name)" = $row.Value
                    }
                } elseif ($column.Name -eq 'ConfigurationID') {
                    $device."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsGuid(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                } elseif ($column.Name -in $datetimeColumns) {
                    $row = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsDateTime(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                    if ($row.HasValue) {
                        $device."$($column.Name)" = $row.Value
                    }
                } elseif ($column.Name -eq 'StatusCode') {
                    $row = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsInt32(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                    if ($row.HasValue) {
                        $device.StatusCode = $row.Value
                    }
                } else {
                    $device."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    )
                }
            }

            if ($PSBoundParameters.ContainsKey('TargetName') -and $device.TargetName -notlike $TargetName) {
                continue
            }
            if ($PSBoundParameters.ContainsKey('ConfigurationID') -and $device.ConfigurationID -notlike $ConfigurationID) {
                continue
            }

            $device
        }
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        Dismount-DSCPullServerESEDatabase -Connection $Connection
    }
}

function Get-DSCPullServerESERegistration {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $Connection,

        [Parameter()]
        [guid] $AgentId,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [Alias('Name')]
        [string] $NodeName
    )
    $table = 'RegistrationData'
    [Microsoft.Isam.Esent.Interop.JET_TABLEID] $tableId = [Microsoft.Isam.Esent.Interop.JET_TABLEID]::Nil
    try {
        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode ReadOnly
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetOpenTable(
            $Connection.SessionId,
            $Connection.DbId,
            $Table,
            $null,
            0,
            [Microsoft.Isam.Esent.Interop.OpenTableGrbit]::None,
            [ref]$tableId
        )
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    }

    try {
        [Microsoft.Isam.Esent.Interop.Api]::MoveBeforeFirst($Connection.SessionId, $tableId)
        while ([Microsoft.Isam.Esent.Interop.Api]::TryMoveNext($Connection.SessionId, $tableId)) {
            $nodeRegistration = [DSCNodeRegistration]::new()
            foreach ($column in ([Microsoft.Isam.Esent.Interop.Api]::GetTableColumns($Connection.SessionId, $tableId))) {
                if ($column.Name -eq 'IPAddress') {
                    $nodeRegistration.IPAddress = ([Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    ) -split ';' -split ',')
                } elseif ($column.Name -eq 'ConfigurationNames') {
                    $nodeRegistration.ConfigurationNames = [Microsoft.Isam.Esent.Interop.Api]::DeserializeObjectFromColumn(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                } else {
                    $nodeRegistration."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                }
            }

            if ($PSBoundParameters.ContainsKey('NodeName') -and $nodeRegistration.NodeName -notlike $NodeName) {
                continue
            }

            if ($PSBoundParameters.ContainsKey('AgentId') -and $nodeRegistration.AgentId -ne $AgentId) {
                continue
            }

            $nodeRegistration
        }
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        Dismount-DSCPullServerESEDatabase -Connection $Connection
    }
}

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

        [Parameter()]
        [Alias('Id')]
        [guid] $AgentId,

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

        [Parameter()]
        [datetime] $FromStartTime,

        [Parameter()]
        [datetime] $ToStartTime,

        [Parameter()]
        [guid] $JobId
    )
    $table = 'StatusReport'
    [Microsoft.Isam.Esent.Interop.JET_TABLEID] $tableId = [Microsoft.Isam.Esent.Interop.JET_TABLEID]::Nil
    try {
        Mount-DSCPullServerESEDatabase -Connection $Connection -Mode ReadOnly
        [void] [Microsoft.Isam.Esent.Interop.Api]::JetOpenTable(
            $Connection.SessionId,
            $Connection.DbId,
            $Table,
            $null,
            0,
            [Microsoft.Isam.Esent.Interop.OpenTableGrbit]::None,
            [ref]$tableId
        )
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    }

    try {
        [Microsoft.Isam.Esent.Interop.Api]::MoveBeforeFirst($Connection.SessionId, $tableId)

        $stringColumns = @(
            'NodeName',
            'OperationType',
            'RefreshMode',
            'Status',
            'LCMVersion',
            'ReportFormatVersion',
            'ConfigurationVersion',
            'RebootRequested'
        )

        $guidColumns = @(
            'JobId',
            'Id'
        )

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

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

        while ([Microsoft.Isam.Esent.Interop.Api]::TryMoveNext($Connection.SessionId, $tableId)) {
            $statusReport = [DSCNodeStatusReport]::new()
            foreach ($column in ([Microsoft.Isam.Esent.Interop.Api]::GetTableColumns($Connection.SessionId, $tableId))) {
                if ($column.Name -in $datetimeColumns) {
                    $statusReport."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsDateTime(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                } elseif ($column.Name -eq 'IPAddress') {
                    $ipAddress = ([Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    ) -split ';' -split ',')
                    $statusReport.IPAddress = $ipAddress.ForEach{
                        # potential for invalid ip address like empty string
                        try {
                            [void][ipaddress]::Parse($_)
                            $_
                        } catch {}
                    }
                } elseif ($column.Name -in $stringColumns) {
                    $statusReport."$($column.Name)" = ([Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    ) -split ';' -split ',')
                } elseif ($column.Name -in $guidColumns) {
                    $statusReport."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsGuid(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    )
                } elseif ($column.Name -in $deserializeColumns) {
                    $statusReport."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::DeserializeObjectFromColumn(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid
                    ) | ConvertFrom-Json
                } elseif ($column.Name -eq 'AdditionalData') {
                    $statusReport."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    ) | ConvertFrom-Json
                } else {
                    $statusReport."$($column.Name)" = [Microsoft.Isam.Esent.Interop.Api]::RetrieveColumnAsString(
                        $Connection.SessionId,
                        $tableId,
                        $column.Columnid,
                        [System.Text.Encoding]::Unicode
                    )
                }
            }

            if ($PSBoundParameters.ContainsKey('AgentId') -and $statusReport.Id -ne $AgentId) {
                continue
            }

            if ($PSBoundParameters.ContainsKey('NodeName') -and $statusReport.NodeName -notlike $NodeName) {
                continue
            }

            if ($PSBoundParameters.ContainsKey('FromStartTime') -and $statusReport.FromStartTime -ge $FromStartTime) {
                continue
            }

            if ($PSBoundParameters.ContainsKey('ToStartTime') -and $statusReport.AgentId -le $ToStartTime) {
                continue
            }

            if ($PSBoundParameters.ContainsKey('JobId') -and $statusReport.JobId -ne $JobId) {
                continue
            }

            $statusReport
        }
    } catch {
        Write-Error -ErrorRecord $_ -ErrorAction Stop
    } finally {
        Dismount-DSCPullServerESEDatabase -Connection $Connection
    }
}

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 {
        $sqlConnection = [System.Data.SqlClient.SqlConnection]::new($Connection.ConnectionString())
        try {
            $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 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 PreProc {
    param (
        [Parameter(Mandatory)]
        [string] $ParameterSetName,

        [DSCPullServerConnection] $Connection,

        [string] $SQLServer,

        [pscredential] $Credential,

        [string] $Database,

        [string] $ESEFilePath,

        [Parameter(ValueFromRemainingArguments)]
        $DroppedParams
    )

    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
        }
    }
}

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 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 from EDB to SQL.
 
    .DESCRIPTION
    This function allows for data to be copied over from
    an ESE (edb) connection to a SQL connection. This allows
    a user to migrate over from an ESENT type Pull Server to
    a SQL type Pull Server without loosing data.
 
    .PARAMETER ESEConnection
    A specifically passed in ESE connection to migrate data out of.
 
    .PARAMETER SQLConnection
    A specifically passed in SQL 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 SQL 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-DSCPullServerAdminSQLDatabase -SQLServer sqlserver\instance -Name dsc -Credential sa
 
    Copy-DSCPullServerAdminDataESEToSQL -ObjectsToMigrate Devices, RegistrationData, StatusReports -Force
#>
function Copy-DSCPullServerAdminDataESEToSQL {
    [CmdletBinding(SupportsShouldProcess)]
    param(
        [Parameter(Mandatory)]
        [DSCPullServerESEConnection] $ESEConnection,

        [Parameter(Mandatory)]
        [DSCPullServerSQLConnection] $SQLConnection,

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

        [Parameter()]
        [switch] $Force
    )

    switch ($ObjectsToMigrate) {
        Devices {
            $devices = Get-DSCPullServerAdminDevice -Connection $ESEConnection
            foreach ($d in $devices) {
                $sqlD = Get-DSCPullServerAdminDevice -Connection $SQLConnection -TargetName $d.TargetName
                if ($null -eq $sqlD) {
                    if ($PSCmdlet.ShouldProcess($d.TargetName, "Create new device on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($d.GetSQLInsert())
                    }
                } else {
                    if ($PSCmdlet.ShouldProcess($d.TargetName, "Replace existing device on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        if ($Force) {
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($sqlD.GetSQLDelete())
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($d.GetSQLInsert())
                        } else {
                            Write-Warning -Message "Unable to replace device $($d.TargetName) as Force switch was not set"
                        }
                    }
                }
            }
        }
        RegistrationData {
            $registrations = Get-DSCPullServerAdminRegistration -Connection $ESEConnection
            foreach ($r in $registrations) {
                $sqlReg = Get-DSCPullServerAdminRegistration -Connection $SQLConnection -AgentId $r.AgentId
                if ($null -eq $sqlReg) {
                    if ($PSCmdlet.ShouldProcess($r.AgentId, "Create new Registration on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($r.GetSQLInsert())
                    }
                } else {
                    if ($PSCmdlet.ShouldProcess($r.AgentId, "Replace existing Registration on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        if ($Force) {
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($sqlReg.GetSQLDelete())
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($r.GetSQLInsert())
                        } else {
                            Write-Warning -Message "Unable to replace Registration $($r.AgentId) as Force switch was not set"
                        }
                    }
                }
            }
        }
        StatusReports {
            $reports = Get-DSCPullServerAdminStatusReport -Connection $ESEConnection
            foreach ($r in $reports) {
                $sqlRep = Get-DSCPullServerAdminStatusReport -Connection $SQLConnection -JobId $r.JobId -AgentId $r.Id
                if ($null -eq $sqlRep) {
                    if ($PSCmdlet.ShouldProcess($r.JobId, "Create new StatusReport on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($r.GetSQLInsert())
                    }
                } else {
                    if ($PSCmdlet.ShouldProcess($r.JobId, "Replace StatusReport Registration on $($SQLConnection.SQLServer)\$($SQLConnection.Database)")) {
                        if ($Force) {
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($sqlRep.GetSQLDelete())
                            Invoke-DSCPullServerSQLCommand -Connection $SQLConnection -CommandType Set -Script ($r.GetSQLInsert())
                        } else {
                            Write-Warning -Message "Unable to replace StatusReport $($r.JobId) 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 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 = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [DSCDevice] $InputObject,

        [Parameter()]
        [guid] $ConfigurationID,

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

        [Parameter()]
        [string] $ServerCheckSum,

        [Parameter()]
        [string] $TargetCheckSum,

        [Parameter()]
        [bool] $NodeCompliant,

        [Parameter()]
        [datetime] $LastComplianceTime,

        [Parameter()]
        [datetime] $LastHeartbeatTime,

        [Parameter()]
        [bool] $Dirty,

        [Parameter()]
        [uint32] $StatusCode,

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

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $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) {
            throw "A Device with TargetName '$TargetName' was not found"
        } else {
            $PSBoundParameters.Keys.Where{
                $_ -in ($existingDevice | Get-Member -MemberType Property | Where-Object -FilterScript {$_.Name -ne 'Status'} ).Name
            }.ForEach{
                if ($null -ne $PSBoundParameters.$_) {
                    $existingDevice.$_ = $PSBoundParameters.$_
                }
            }
            $tsqlScript = $existingDevice.GetSQLUpdate()

            if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                Invoke-DSCPullServerSQLCommand -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 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')]
        [DSCNodeStatusReport] $InputObject,

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

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

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $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 {
            $tsqlScript = $existingReport.GetSQLDelete()
            if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                Invoke-DSCPullServerSQLCommand -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 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')]
        [DSCNodeRegistration] $InputObject,

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

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

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $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 {
            $tsqlScript = $existingRegistration.GetSQLDelete()
            if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                Invoke-DSCPullServerSQLCommand -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 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')]
        [DSCDevice] $InputObject,

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

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

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $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 {
            $tsqlScript = $existingDevice.GetSQLDelete()
            if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
                Invoke-DSCPullServerSQLCommand -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'
    }
    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 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()]
        [Guid] $Id = [guid]::NewGuid(),

        [Parameter()]
        [string] $OperationType,

        [Parameter()]
        [string] $RefreshMode,

        [Parameter()]
        [string] $Status,

        [Parameter()]
        [string] $LCMVersion,

        [Parameter()]
        [string] $ReportFormatVersion,

        [Parameter()]
        [string] $ConfigurationVersion,

        [Parameter()]
        [string] $NodeName,

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

        [Parameter()]
        [datetime] $StartTime,

        [Parameter()]
        [datetime] $EndTime,

        [Parameter()]
        [datetime] $LastModifiedTime,

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

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

        [Parameter()]
        [bool] $RebootRequested,

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

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerSQLConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive -Type SQL),

        [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."
        } else {
            $tsqlScript = $report.GetSQLInsert()
        }

        if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
            Invoke-DSCPullServerSQLCommand -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 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)]
        [guid] $AgentId,

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

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

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

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

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerSQLConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive -Type SQL),

        [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."
        } else {
            $tsqlScript = $nodeRegistration.GetSQLInsert()
        }

        if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
            Invoke-DSCPullServerSQLCommand -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 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)]
        [guid] $ConfigurationID,

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

        [Parameter()]
        [string] $ServerCheckSum,

        [Parameter()]
        [string] $TargetCheckSum,

        [Parameter()]
        [bool] $NodeCompliant,

        [Parameter()]
        [datetime] $LastComplianceTime,

        [Parameter()]
        [datetime] $LastHeartbeatTime,

        [Parameter()]
        [bool] $Dirty,

        [Parameter()]
        [uint32] $StatusCode,

        [Parameter(ParameterSetName = 'Connection')]
        [DSCPullServerSQLConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive -Type SQL),

        [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."
        } else {
            $tsqlScript = $device.GetSQLInsert()
        }

        if ($PSCmdlet.ShouldProcess("$($Connection.SQLServer)\$($Connection.Database)", $tsqlScript)) {
            Invoke-DSCPullServerSQLCommand -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 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')]
        [ValidateNotNullOrEmpty()]
        [string] $ESEFilePath,

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

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

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

        [switch] $DontStore
    )

    $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
        }
    } else {
        $connection = [DSCPullServerESEConnection]::New($ESEFilePath)
    }

    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.
 
    .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 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 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()]
        [Alias('Name')]
        [string] $NodeName,

        [Parameter()]
        [guid] $JobId,

        [Parameter()]
        [datetime] $FromStartTime,

        [Parameter()]
        [datetime] $ToStartTime,

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

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateNotNullOrEmpty()]
        [string] $ESEFilePath,

        [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
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                }
                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)
                }

                Get-DSCPullServerESEStatusReport @eseParams
            }
            SQL {
                $tsqlScript = "SELECT * FROM StatusReport"
                $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('*', '%')))
                }
                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 ($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
                    }
                }
            }
        }
    }
}

<#
    .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).
 
    .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 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()]
        [Alias('Name')]
        [string] $NodeName,

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

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateNotNullOrEmpty()]
        [string] $ESEFilePath,

        [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
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                }
                if ($PSBoundParameters.ContainsKey('AgentId')) {
                    $eseParams.Add('AgentId', $AgentId)
                }
                if ($PSBoundParameters.ContainsKey("NodeName")) {
                    $eseParams.Add('NodeName', $NodeName)
                }

                Get-DSCPullServerESERegistration @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('*', '%')))
                }

                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
                    }
                }
            }
        }
    }
}

<#
    .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.
 
    .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 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()]
        [ValidateNotNullOrEmpty()]
        [String] $TargetName,

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

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

        [Parameter(Mandatory, ParameterSetName = 'ESE')]
        [ValidateNotNullOrEmpty()]
        [string] $ESEFilePath,

        [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
        }
    }
    process {
        switch ($Connection.Type) {
            ESE {
                $eseParams = @{
                    Connection = $Connection
                }
                if ($PSBoundParameters.ContainsKey('TargetName')) {
                    $eseParams.Add('TargetName', $TargetName)
                }
                if ($PSBoundParameters.ContainsKey('ConfigurationID')) {
                    $eseParams.Add('ConfigurationID', $ConfigurationID)
                }

                Get-DSCPullServerESEDevice @eseParams
            }
            SQL {
                $tsqlScript = 'SELECT * FROM Devices'
                $filters = [System.Collections.ArrayList]::new()
                if ($PSBoundParameters.ContainsKey("TargetName")) {
                    [void] $filters.Add(("TargetName like '{0}'" -f $TargetName.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
                    }
                }
            }
        }
    }
}

<#
    .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])]
    [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 properteis
    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 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 = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [DSCNodeRegistration] $InputObject,

        [Parameter(Mandatory, ParameterSetName = 'Manual_Connection')]
        [Parameter(Mandatory, ParameterSetName = 'Manual_SQL')]
        [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')]
        [DSCPullServerSQLConnection] $Connection = (Get-DSCPullServerAdminConnection -OnlyShowActive -Type SQL),

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $Connection = PreProc -ParameterSetName $PSCmdlet.ParameterSetName @PSBoundParameters
        if ($null -eq $Connection) {
            break
        }
    }
    process {
        if (-not $PSBoundParameters.ContainsKey('InputObject')) {
            $existingRegistration = Get-DSCPullServerAdminRegistration -Connection $Connection -AgentId $AgentId
            if ($null -eq $existingRegistration) {
                throw "A NodeRegistration with AgentId '$AgentId' was not found"
            }
        } else {
            $existingRegistration = $InputObject
        }

        $PSBoundParameters.Keys.Where{
            $_ -in ($existingRegistration | Get-Member -MemberType Property).Name
        }.ForEach{
            if ($null -ne $PSBoundParameters.$_) {
                $existingRegistration.$_ = $PSBoundParameters.$_
            }
        }

        $tsqlScript = $existingRegistration.GetSQLUpdate()

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

<#
    .SYNOPSIS
    Overwrites 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 Overwrites of status report properties
    in the StatusReport table.
 
    .PARAMETER InputObject
    Pass in the statusreport object to be modified from the database.
 
    .PARAMETER JobId
    Modify properties for the statusreport with specified JobId.
 
    .PARAMETER Id
    Set the Id property for the existing device.
 
    .PARAMETER OperationType
    Set the OperationType property for the existing device.
 
    .PARAMETER RefreshMode
    Set the RefreshMode property for the existing device.
 
    .PARAMETER Status
    Set the Status property for the existing device.
 
    .PARAMETER LCMVersion
    Set the LCMVersion property for the existing device.
 
    .PARAMETER ReportFormatVersion
    Set the ReportFormatVersion property for the existing device.
 
    .PARAMETER ConfigurationVersion
    Set the ConfigurationVersion 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 StartTime
    Set the StartTime property for the existing device.
 
    .PARAMETER EndTime
    Set the EndTime property for the existing device.
 
    .PARAMETER LastModifiedTime
    Set the LastModifiedTime property for the existing device.
 
    .PARAMETER Errors
    Set the Errors property for the existing device.
 
    .PARAMETER StatusData
    Set the StatusData property for the existing device.
 
    .PARAMETER RebootRequested
    Set the RebootRequested property for the existing device.
 
    .PARAMETER AdditionalData
    Set the AdditionalData 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 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-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a' -NodeName 'lcmclient01'
 
    .EXAMPLE
    Get-DSCPullServerAdminStatusReport -JobId '80ee20f9-78df-480d-8175-9dd6cb09607a' | Set-DSCPullServerAdminStatusReport -NodeName 'lcmclient01'
#>
function Set-DSCPullServerAdminStatusReport {
    [CmdletBinding(
        DefaultParameterSetName = 'InputObject_Connection',
        ConfirmImpact = 'Medium',
        SupportsShouldProcess
    )]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_Connection')]
        [Parameter(Mandatory, ValueFromPipeline, ParameterSetName = 'InputObject_SQL')]
        [DSCNodeStatusReport] $InputObject,

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

        [Parameter()]
        [Guid] $Id,

        [Parameter()]
        [string] $OperationType,

        [Parameter()]
        [string] $RefreshMode,

        [Parameter()]
        [string] $Status,

        [Parameter()]
        [string] $LCMVersion,

        [Parameter()]
        [string] $ReportFormatVersion,

        [Parameter()]
        [string] $ConfigurationVersion,

        [Parameter()]
        [string] $NodeName,

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

        [Parameter()]
        [datetime] $StartTime,

        [Parameter()]
        [datetime] $EndTime,

        [Parameter()]
        [datetime] $LastModifiedTime,

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

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

        [Parameter()]
        [bool] $RebootRequested,

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

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

        [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')) {
            [void] $PSBoundParameters.Add('Connection', $Connection)
        }
        $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) {
            throw "A Report with JobId '$JobId' was not found"
        } else {
            $PSBoundParameters.Keys.Where{
                $_ -in ($existingReport | Get-Member -MemberType Property).Name
            }.ForEach{
                if ($null -ne $PSBoundParameters.$_) {
                    $existingReport.$_ = $PSBoundParameters.$_
                }
            }
            $tsqlScript = $existingReport.GetSQLUpdate()

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