Abort Queued Deployments.ps1

# Written by Colby Bouma
# This is a script for Mmuni: https://support.pdq.com/hc/en-us/community/posts/211677967/comments/115000826331
# https://gist.github.com/Colby-PDQ/38d033889303e42faf0648aa7d2ec2fe
#
# v 003

$Deploy_DB_File = "$env:SYSTEMDRIVE\ProgramData\Admin Arsenal\PDQ Deploy\Database.db"
$Error_Message = "<Error><Message>Aborted</Message><Type>AdminArsenal.Remote.Agent.AbortedException</Type><StackTrace></StackTrace></Error>"

# This is a stripped down version of a function from a blog post I am writing
function Submit-Query ( $Query ) {

    $Query_Output = sqlite3.exe "$Deploy_DB_File" "$Query" 2>&1

    # I borrowed the error handling from this, but kind of went my own way with it
    # http://serverfault.com/questions/340711/redirect-stderr-to-variable-in-powershell
    if ( $Query_Output.Exception -ne $null ) {

        Write-Log "ERROR: Submit-Query encountered an error"
        Write-Log $Query_Output.Exception

    } else {

        return $Query_Output

    }

}

function Write-Log {

    $Timestamp = Get-Date -Format "HH:mm:ss.fff"
    $Log_Message = "$Timestamp --- $args"
    Write-Output $Log_Message

}



$DeploymentComputerIds_To_Abort = Submit-Query "SELECT DeploymentComputerId FROM DeploymentComputers WHERE Stage = 'Queued';"

ForEach ( $DeploymentComputerId_To_Abort in $DeploymentComputerIds_To_Abort ) {

    # Unfortunately the DeploymentUser isn't populated for Queued deployments, so we have to retrieve it from the Credentials table.
    $DeploymentId = Submit-Query "SELECT DeploymentId FROM DeploymentComputers WHERE DeploymentComputerId = '$DeploymentComputerId_To_Abort';"
    $UserId = Submit-Query "SELECT UserId from Deployments WHERE DeploymentId = '$DeploymentId';"
    $DeploymentUser = Submit-Query "SELECT UserName FROM Credentials WHERE CredentialsId = '$UserId';"

    Submit-Query "UPDATE DeploymentComputers SET Stage = 'Finished', Error = '$Error_Message', DeploymentUser = '$DeploymentUser' WHERE DeploymentComputerId = '$DeploymentComputerId_To_Abort';"

}

Oops, I forgot the Write-Log function in version 1.