D365FOLBDAdmin

1.0.194

functions/Get-D365LBDOrchestratorLastRunBookDetails.ps1

function Get-D365LBDOrchestratorLastRunBookDetails {
    <#
    .SYNOPSIS
  
 
   #>
    [CmdletBinding()]
    [alias("Get-D365OrchestratorLastRunBookDetails")]
    param ([Parameter(ValueFromPipeline = $True,
            ValueFromPipelineByPropertyName = $True,
            Mandatory = $false,
            HelpMessage = 'D365FO Local Business Data Server Name',
            ParameterSetName = 'NoConfig')]
        [PSFComputer]$ComputerName = "$env:COMPUTERNAME",
        [Parameter(ParameterSetName = 'Config',
            ValueFromPipeline = $True)]
        [psobject]$Config,
        [Parameter(ParameterSetName = 'Orch')]
        $OrchdatabaseServer,
        [Parameter(ParameterSetName = 'Orch')]
        $OrchdatabaseName
    )
    BEGIN {
    } 
    PROCESS {
        if (!$Config) {
            $Config = Get-D365LBDConfig -ComputerName $ComputerName -HighLevelOnly
        }
        if ($Config)
        {
            $OrchdatabaseServer = $Config.OrchdatabaseServer
            $OrchdatabaseName = $OrchdatabaseName.OrchDatabase
        }
       
       
        function Invoke-SQL {
            param(
                [string] $dataSource = ".\SQLEXPRESS",
                [string] $database = "MasterData",
                [string] $sqlCommand = $(throw "Please specify a query.")
            )

            $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

            $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
            $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)
            $connection.Open()
            
            $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
            $dataset = New-Object System.Data.DataSet

            $adapter.Fill($dataSet) | Out-Null
            $connection.Close()
            $dataSet.Tables
        }
        $Query = "Select RBT.[Order], CASE WHEN RBT.State = 0 THEN 'Not Started' WHEN RBT.State = 1 THEN 'In Progress' WHEN RBT.State THEN 'Failed' WHEN RBT.State = 4 THEN 'Cancelled' END AS TaskStatus,
        RBT.Name, RBT.Description, RBT.RunbookTaskId, RBT.TaskDefinitionName, RBT.State, RBT.Retries, RBT.StartDateTime, RBT.EndDateTime,
        DI.ID as EnvironmentID, DI.Name as EnvironmentName, DI.ActiveJobID, DI.State as EnvironmentState, DI.Status as EnvironmentStatus,
        OJ.JobID, OJ.CommandID, OJ.State, OJ.Exception, OJ.QueuedDateTime, OJ.QueuedDateTime, OJ.ScheduledDateTime, OJ.LastProcessedDateTime FROM
        DeploymentInstance DI JOIN
        OrchestratorJob OJ ON OJ.DeplomentInstanceID = DI.ID JOIN
        RunBookTask RBT ON RBT.JobID = OJ.JobID WHERE
        OJ.JobID = (select Top 1 JobID from RunBookTask ORDER BY StartDateTime DESC
        "

        try {
            $Sqlresults = invoke-sql -datasource $OrchdatabaseServer -database $OrchDatabase -sqlcommand $Query
            
            $Sqlresults
        }
        catch {}
    }
    END {
    }
}