Tuesday, July 24, 2018

Grab WFA execution log with powershell

For logging purposes, you might want the need to grab a WFA execution log.  I initially wrote this to dump those in an OCI database, but for this post, I narrowed it down to just dumping them in CSV-files (jobinfo, input parameters & return parameters).  The original script also updates statuses (for example, when a jobstatus changes from failed => resume => completed), but that compare is only possible when we dump in a database and i can query it again, so for this csv-output method, an update won't work (but I kept the code in comment).

I did make the script incremental.  Job logs can take a while to grab, so I allow a timeout.  I keep the last jobid in a simple text-file.  Original, obviously, I stored this in the database.  I you are interested in the the database version, give me a ping.



param(

    [parameter(mandatory=$true,helpmessage="The name for WFA Credentials to search in WFA")]
    [pscredential]$wfaCredential,

    [parameter(mandatory=$true,helpmessage="Wfa Hosts")]
    [string[]]$wfaHosts,

    [parameter(mandatory=$false,helpmessage="Number of records to fetch in one API call")]
    [int]$maxRecords=100,

    [parameter(mandatory=$false,helpmessage="The timeout in minutes")]
    [int]$timeOutMinutes=5

)

$stopwatch = [System.Diagnostics.Stopwatch]::startNew()
$scriptstarttime = get-date

#============================================================================

#============================================================================
# DO NOT CHANGE CODE BELOW THIS LINE
#============================================================================

if (-not ([System.Management.Automation.PSTypeName]'ServerCertificateValidationCallback').Type){
    Write-Host "Adding certificate validation callback type"
    Add-Type @"
        using System;
        using System.Net;
        using System.Net.Security;
        using System.Security.Cryptography.X509Certificates;
        public class ServerCertificateValidationCallback
        {
            public static void Ignore()
            {
                ServicePointManager.ServerCertificateValidationCallback += 
                    delegate
                    (
                        Object obj, 
                        X509Certificate certificate, 
                        X509Chain chain, 
                        SslPolicyErrors errors
                    )
                    {
                        return true;
                    };
            }
        }
"@
}
[ServerCertificateValidationCallback]::Ignore();

function fixdate($d){
    get-date $d -Format "yyyy-MM-dd hh:mm:ss"
}

function nextEncode($next){
    if($next){
        return ([uri]::EscapeDataString(("{0}" -f [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($next)))))
    }else{
        return ""
    }
}

function getrest($uri){
    $fail=$true
    $failcount=3
    while($fail -and ((--$failcount) -ge 0)){
        try {
            #write-host -ForegroundColor Magenta "--> $uri"
            # Force TLS
            [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
 
            $output = Invoke-RestMethod -Headers $headers -Method Get -Uri $uri -Credential $wfaCredential -ContentType "application/json"
            $fail = $false
        } catch {
            # throw
            write-warning "Retrying [$failcount]..."
            sleep -Seconds 5
            # An error occured. Either workflow does not exist or credentials are wrong
            #exit
        }
    }
    if($fail){
        throw "Failed to invoke [$uri]"
    }else{
        return $output
    }
}

$jobs = @()
$inputparams = @()
$returnparams = @()

try{
    foreach ($h in $wfaHosts) {
    
        $lastid = $null
        $lastidinserted = $null
        $foundlast = $false
        $nexttag = $false
        $hname = $h
        $jobid = $null
        $resturl = "https://$h/rest"

        try{
            Write-Host "[$hname] Connecting to wfa"

            # Find RESTful execution url for WFA workflow

            $headers = @{}
            $headers["Accept"] = "application/json"

            $lastrecord = 0
            $lastrecordinserted = 0
            $list = @()

            # get last from local file
            if(Test-Path .\$hname.txt){
                $lastrecordinserted = get-Content -Path .\$hname.txt

            }

            if(-not $lastrecord){
                $lastrecord=0
                Write-Host -foregroundcolor magenta "[$hname] Initializing - first run"
            }else{
                Write-Host "[$hname] Last found job_id = $lastrecord"
            }

            if(-not $lastrecordinserted){
                $lastrecordinserted=0
                Write-Host "[$hname] Last run was finished"
            }else{
                Write-Host -foregroundcolor magenta "[$hname] Last run not finished, continueing from $lastrecordinserted"
                $next = ",maxRecords=$maxrecords,highestJobId=$lastrecordinserted"
                $nexttag = nextEncode $next
            }

            do{

                if($nexttag){
                    $output = getrest ("{0}/workflows/jobs?nextTag={1}" -f $resturl,$nexttag)
                }else{
                    $output = getrest ("{0}/workflows/jobs" -f $resturl)
                }
                if($output.nextTag){
                    $nexttag = $output.nextTag
                }else{
                    $nexttag = ""
                }

                # Adding job
                Write-Host ("[$hname] Got {0} api results" -f $output.total_records)
                foreach($r in $output.records){

                    if($stopwatch.Elapsed.Minutes -ge $timeOutMinutes){
                        Throw "Timing out... exiting"
                    }

                    $jobid = $r.jobid
                    if($jobid -eq $h.last_job_id){
                        Write-Host "[$hname] All new records are found... exiting insert loop"
                        $foundlast = $true
                        break
                    }

                    Write-Host "[$hname] adding $jobid..."
                    $jobstatus = $r.jobstatus
                    $js = $jobstatus
                    $wf = $workflow
                    $userInputValues = $jobstatus.userInputValues
                    $returnParameters = $jobstatus.returnParameters

                    $jobid | Out-File .\$hname.txt -Force

                    $o = @{
                        host = $h
                        workflow_uuid = $r.workflow.uuid
                        workflow_name = $r.workflow.name
                        workflow_description = $r.workflow.description
                        job_id = $jobid
                        host_id = $h.id
                        status = $js.jobStatus
                        type = $js.jobType
                        scheduleType = $js.scheduleType
                        startTime = (fixdate $js.startTime)
                        endTime = (fixdate $js.endTime)
                        plannedExecutionTime = (fixdate $js.plannedExecutionTime)
                        phase = $js.phase
                        submittedBy = $js.submittedBy
                    }

                    $jobs += New-Object -TypeName psobject -Property $o

                    # add userinput
                    if($userInputValues.Length -gt 0){
                        foreach($u in $userInputValues){
                            $p = @{
                                name = $u.key
                                value = $u.value
                                job_id = $jobid
                            }
                            $inputparams += New-Object -TypeName psobject -Property $p
                        }
                    }

                    # add returnparams
                    if($returnParameters.Length -gt 0){
                        foreach($rp in $returnParameters){
                            $p = @{
                                name = $u.key
                                value = $u.value
                                job_id = $jobid
                            }
                            $returnparams += New-Object -TypeName psobject -Property $p
                        }
                    }

                }
       
            }while($nexttag -and -not $foundlast)

            # reached the end
            Write-Host "[$hname] discovery run completed"

## modify without db does not work ##           #======================
## modify without db does not work ##           # now go back in time
## modify without db does not work ##           #======================
## modify without db does not work ##
## modify without db does not work ##           Write-Host "[$hname] Checking modified statuses [starting from $jobid for $checkStatusDays days]"
## modify without db does not work ##
## modify without db does not work ##
## modify without db does not work ##           try{
## modify without db does not work ##               Write-Host "[$hname] Getting database job statuses"
## modify without db does not work ##               $job_statuses = @{}
## modify without db does not work ##               $rs = Invoke-DbQuery -Query ("SELECT job_id,status FROM wfa_execution_log.job WHERE host_id={0} AND startTime > DATE_ADD(NOW(), INTERVAL -$checkStatusDays DAY) AND job_id <= $jobid" -f $h.id) -Connection $Connection
## modify without db does not work ##               if($rs[0] -gt 0){
## modify without db does not work ##                   Write-Host ("[$hname] Found {0} old jobs in last $checkStatusDays days" -f $rs[0])
## modify without db does not work ##                   $rs | select -skip 1 | %{$job_statuses[[string]($_.job_id)]=$_.status}
## modify without db does not work ##               }else{
## modify without db does not work ##                   Write-Host ("[$hname] No old jobs found")
## modify without db does not work ##               }
## modify without db does not work ##           }catch{
## modify without db does not work ##               Write-Host -foregroundcolor red "[$hname] Failed to get list of old jobs"
## modify without db does not work ##               throw    
## modify without db does not work ##           }
## modify without db does not work ##           
## modify without db does not work ##           $next = (",maxRecords=$maxrecords,highestJobId={0}" -f ($jobid+1))
## modify without db does not work ##           $nexttag = nextEncode $next
## modify without db does not work ##
## modify without db does not work ##           do{
## modify without db does not work ##
## modify without db does not work ##               $output = getrest ("{0}/workflows/jobs?nextTag={1}" -f $h.rest_url,$nexttag)
## modify without db does not work ##               if($output.nextTag){
## modify without db does not work ##                   $nexttag = $output.nextTag
## modify without db does not work ##               }else{
## modify without db does not work ##                   $nexttag = ""
## modify without db does not work ##               }
## modify without db does not work ##
## modify without db does not work ##               # Adding job
## modify without db does not work ##               Write-Host ("[$hname] Found {0} api results" -f $output.total_records)
## modify without db does not work ##               foreach($r in $output.records){
## modify without db does not work ##
## modify without db does not work ##                   if($stopwatch.Elapsed.Minutes -ge $timeOutMinutes){
## modify without db does not work ##                       Throw "Timing out... exiting"
## modify without db does not work ##                   }
## modify without db does not work ##
## modify without db does not work ##                   $jobid = $r.jobid
## modify without db does not work ##                   $jobstatus = $r.jobstatus.jobstatus
## modify without db does not work ##
## modify without db does not work ##                   if($job_statuses.ContainsKey([string]$jobid)){
## modify without db does not work ##
## modify without db does not work ##                       $oldstatus = $job_statuses[[string]$jobid]
## modify without db does not work ##
## modify without db does not work ##                       # skipping already completed ones
## modify without db does not work ##                       if($oldstatus -ne "COMPLETED"){
## modify without db does not work ##
## modify without db does not work ##                           
## modify without db does not work ##                           $js = $jobstatus
## modify without db does not work ##                           
## modify without db does not work ##                           if($oldstatus -ne $jobstatus){
## modify without db does not work ##
## modify without db does not work ##                               Write-Host "[$hname] Job $jobid has changed from $oldstatus -> $jobstatus..."
## modify without db does not work ##                              
## modify without db does not work ##                               # update last id inserted
## modify without db does not work ##                               # save last job id and commit
## modify without db does not work ##                               $q = "UPDATE job SET status='{0}' WHERE job_id={1} AND host_id={2};"
## modify without db does not work ##                               $query += ($q -f $jobstatus,$jobid,$h.id)
## modify without db does not work ##                               $result = Invoke-DbNonQuery -Query $query -Connection $Connection
## modify without db does not work ##                               if($result -lt 0){
## modify without db does not work ##                                   throw "[$hname] Failed to update job $jobid [result = $result]"
## modify without db does not work ##                               }
## modify without db does not work ##
## modify without db does not work ##                           }else{
## modify without db does not work ##                               #Write-Host "[$hname] Job $jobid has not changed, skipping..."
## modify without db does not work ##                           }
## modify without db does not work ##
## modify without db does not work ##                       }else{
## modify without db does not work ##                           #Write-Host "[$hname] Job $jobid is completed, skipping..."
## modify without db does not work ##                       }
## modify without db does not work ##                   
## modify without db does not work ##                   }else{
## modify without db does not work ##
## modify without db does not work ##                       $foundlast = $true
## modify without db does not work ##                       Write-Host "[$hname] Reached end of $checkStatusDays days, exiting"
## modify without db does not work ##                       break
## modify without db does not work ##
## modify without db does not work ##                   }
## modify without db does not work ##
## modify without db does not work ##               }
## modify without db does not work ##      
## modify without db does not work ##           }while($nexttag -and -not $foundlast)


        }catch{
            Write-Host -foregroundcolor red "[$hname] Failed processing host"
            if($_.Exception.Message -match "Timing out"){
                throw
            }
            if($WfaServer){
                throw
            }else{
                Write-Host -foregroundcolor red $_.Exception.Message
                Continue
            }
        }

    }

    $timestamp = $(get-date -f MM-dd-yyyy_HH_mm_ss)
    $jobs | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | out-file .\jobs_csv_$timestamp.txt
    $inputparams | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | out-file .\inputparams_csv$timestamp.txt
    $returnparams | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | out-file .\returnparams_csv$timestamp.txt
    
}catch{
    Write-Host -foregroundcolor red $_.Exception.Message
    throw
}finally{
    Write-Host ("Ran for {0} seconds" -f $stopwatch.Elapsed.Seconds)
    $stopwatch.stop()
}

No comments :

Post a Comment