SCOrch FTP Integration Pack Update v1.3

I've just released v1.3 of the Orchestrator IP for FTP/SFTP.  You can find it on codeplex at this link:

https://scorchestrator.codeplex.com/releases/view/94807

Please install this in a test environment before deploying to production!  Also, please comment on any issues/bugs you may encounter.

********
Since adding the extra "UsePassive" and "UseBinary" input fields, please note the following steps required for existing installs upgraded to v1.3.  These fields must be commited to the database in the existing activity before they will work.

1)  Stop runbooks where the activities are located.
2)  Check out the runbook
3)  Open the activity and click "Finish" (no changes required)
4)  Check in the runbook
(Thanks to Jeff for the insight for commiting the added fields to existing activities!)
********

Activity Change Log:

 

Create Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Delete File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Delete Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Download File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for deleting source file(s)

Add option for bin/ascii transfer

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

List Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Rename File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Upload File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for deleting source file(s)

Add option for bin/ascii transfer

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Empty File/0 byte Error Handling

Audit Jobs started from Orchestration Console (Who Started It)

This post I'll explain how to setup a runbook that runs a daily audit report of runbook jobs started from the Orchestration Console (OC).  As more end users and external applications get access to start runbooks, it's important to know who started a runbook at any given time.

The runbook itself is fairly simple.







First, we need to query the database for the jobs table ([Microsoft.SystemCenter.Orchestrator.Runtime].Jobs) which holds most of the data for the jobs.  One key to both queries is to flatten the data which we will later break back out into multi-valued data.




Select POLICIES.Name, Jobs.CreatedBy, Jobs.CreationTime, Jobs.Status
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
INNER JOIN POLICIES ON Jobs.RunbookId = POLICIES.UniqueID
Where Jobs.CreatedBy != 'S-1-5-500' and datediff(HOUR, Jobs.CreationTime, SYSUTCDATETIME ()) <= 24
Order By Jobs.CreationTime Desc


You can also extract parameters, runbook servers specified, etc... from the query if you like.  I joined the POLICIES table onto Jobs to get the name of the runbook from the guid.  Since this is a daily report, I selected jobs created in the past 24 hours.  I've also filtered out jobs started by the SID - "S-1-5-500".  This will narrow our results down to the OC.  Jobs started from the designer console or "internally" to SCOrch will have that SID.  Jobs created from the OC will show the SID of the user that started the job.

Next, we run almost the same query just to get the SIDs of the users we need to translate.


Select Jobs.CreatedBy
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
Where Jobs.CreatedBy != 'S-1-5-500' and datediff(HOUR, Jobs.CreationTime, SYSUTCDATETIME ()) <= 24
Group By Jobs.CreatedBy


Now to the script that puts it all together.  You'll notice from this screenshot where I've used the published data to parse.





Here is the script (I've left out the published data alphabet soup since it would be translated to the guid).

$RawResults = ""

$MyObjectCol = @()

$Regex = [regex] '/*__%__'
$Results = $Regex.Split("$RawResults")
ForEach($Data in $Results)
    {
    If ($Data.Split(";")[0].Length -ne 0) {
    $Field1 = $Data.Split(";")[0]}
    Else {$Field1 = $null}
    If ($Data.Split(";")[1].Length -ne 0) {
    $Field2 = $Data.Split(";")[1]}
    Else {$Field2 = $null}
    If ($Data.Split(";")[2].Length -ne 0) {
    $Field3= $Data.Split(";")[2]}
    Else {$Field3 = $null}
    If ($Data.Split(";")[3].Length -ne 0) {
    $Field4= $Data.Split(";")[3]}
    Else {$Field4 = $null}

    $MyObject = new-object psobject
    $MyObject | add-member -MemberType noteproperty -name RunbookName -value $Field1
    $MyObject | add-member -MemberType noteproperty -name InvokedBy -value $Field2
    $MyObject | add-member -MemberType noteproperty -name Date -value $Field3
    $MyObject | add-member -MemberType noteproperty -name JobStatus -value $Field4
    $MyObjectCol += $MyObject
    }

#Remove Empty Objects
$MyObjectCol = @($MyObjectCol | Where-Object {$_.RunbookName -ne $null})

#Convert SID to Username
$Users = @()

$SIDs = ""
$SIDs = $SIDs.Split("__%__")
ForEach ($SID in $SIDs) {
    If ($SID.Length -ne 0) {
        $objSID = New-Object System.Security.Principal.SecurityIdentifier ($SID)
        $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
        $User = $objUser.Value
        ForEach ($Object in $MyObjectCol) {
             $Object.InvokedBy = $Object.InvokedBy -Replace("$SID","$User")
        }
    }
}

#Convert UTC to Local Time
$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)

ForEach ($Object in $MyObjectCol) {
     $Object.Date = [System.TimeZoneInfo]::ConvertTimeFromUtc($Object.Date, $TZ)
}

$MyObjectCol = $MyObjectCol | Sort Date -Desc | ft -AutoSize | Out-String -Width 4096 -Stream | %{ $_.Trim() }
$MyObjectCol | Out-File -FilePath "C:\Temp\JobAudit.txt"


I create a hash table to save the end results in.  The script converts the SIDs to usernames and also converts the time from UTC to local.  It then saves the output to a txt file which we'll email as an attachment.  I chose to do it this way so the data would be saved in columns and formatted cleanly to read.  You could also format it as a list to put directly into the body of the email.




Finally, just for housekeeping, the txt file is deleted.