PowerShell CLI 1.2 - Use Cases - Daily Plan Report

Starting Situation

User might be interested to automatically receive a report about the daily plan.
- The report includes the same information as available from the JOC Cockpit Daily Plan view.
- The report is provided as an Excel file similar to what is available for export from the JOC Cockpit Daily Plan view.
The report can be scheduled on a daily basis or more frequently to provide ongoing information about completed tasks and outstanding tasks.

Use Cases

Report Daily Plan from a job

The PowerShell CLI is used by jobs to create reports. Two modules are applied for this purpose

the JobScheduler PowerShell Module
a reporting PowerShell Module. This example make use of ImportExcel that can be used to create Excel reports on Windows and Linux.

The Get-JobSchedulerDailyPlan cmdlet is used to retrieve daily plan data and to forward them to the ImportExcel module within a job:

Daily Plan Report (Windows version)

<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Report Daily Plan" process_class="agent_windows">
  <script language="powershell"><![CDATA[
Import-Module $env:SCHEDULER_DATA/config/powershell/Modules/ImportExcel
Import-Module $env:SCHEDULER_DATA/config/powershell/Modules/JobScheduler

Connect-JobScheduler -Url $JOCCockpitUrl -Credential $JOCCockpitCredential | Out-Null

# start mode mapping
$startModes = @{"0"="single start"; "1"="repeat start-start"; "2"="repeat end-start"}

# Dates in local time zone, output includes local date format
Get-JSDailyPlan -Timezone (Get-Timezone) `
                |  Select-Object -Property @{name="Job Chain/Job"; expression={ "$($_.jobChain)$($_.job)"}}, `
                                           @{name="Order ID"; expression={$_.orderId}}, `
                                           @{name="Status"; expression={$_.state._text}}, `
                                           @{name="Job Stream"; expression={$_.jobStream}}, `
                                           @{name="Late"; expression={$_.late}}, `
                                           @{name="Start Type"; expression={ $startModes[ "$($_.startMode)"] }}, `
                                           @{name="Repeat Interval"; expression={$_.period.repeat}}, `
                                           @{name="Planned Start Time"; expression={ Get-Date $_.plannedStartTime }}, `
                                           @{name="Expected End Time"; expression={ Get-Date $_.expectedEndTime }}, `
                                           @{name="Expected Duration (sec.)"; expression={ (New-Timespan -Start "$($_.plannedStartTime)" -End "$($_.expectedEndTime)").Seconds }}, `
                                           @{name="Start Time"; expression={ Get-Date $_.startTime }}, `
                                           @{name="End Time"; expression={ Get-Date $_.endTime }}, `
                                           @{name="Duration (sec.)"; expression={ (New-Timespan -Start "$($_.startTime)" -End "$($_.endTime)").Seconds }} `
                | Export-Excel -Path /tmp/jobscheduler_reporting.xlsx -WorksheetName "Daily-Plan" -ClearSheet
				
Write-Output ".. report created: /tmp/jobscheduler_reporting.xls"
]]></script>
  <run_time/>
</job>

Explanations

Line 2-3: The job is executed with a Windows Agent that is assigned by a process class. The job is of type "powershell" and will use the Powershell version provided with the server.
Line 4-5: The required PowerShell modules are imported. They could be installed with any location in the filesystem
Line 7: The Connect-JS cmdlet is used to authenticate with the JOC Cockpit REST Web Service. The required URL and credentials are specified in a PowerShell profile, see PowerShell CLI 1.2 - Use Cases - Credentials Management
Line 10: For better readability of the report the start types of jobs are mapped to a textual representation (single start, cyclic start).
Line 13: The Get-JSDailyPlan cmdlet is called
- with the parameter -Timezone to specify to which timezone date values in the report should be converted. The parameter value -Timezone (Get-Timezone) specifies that the timezone of the Agent's server is used. Otherwise specify the desired timezone e.g. like this: -Timezone (Get-Timezon -Id 'GMT Standard Time'). Without using this parameter all date values are stored as UTC dates to the report.
- optionally with additional parameters, e.g. to specify the date for which the report is created A value -DateTo (Get-Date -Hour 0 -Minute 0 -Second 0).AddDays(8).ToUniversalTime() specifies that the report should cover the next 7 days (until midnight). Keep in mind that dates have to be specified in the UTC timezone. Without this parameter the report will be created for the next day.
- see the Get-JSDailyPlan cmdlet for a full parameter reference.
Line 14-26: From the output of the Get-JSDailyPlan cmdlet a number of properties are selected and and are specified in the sequence in which they should occur in the report.
- To add more speaking column headers the property names are mapped to a more readable textual representation.
- Consider the handling of date formats in line 21, 22 and 24, 25. Use of the Get-Date cmdlet converts the output format of dates (not the timezone) to the default format that is in place on the Agent's server. Without using the Get-Date cmdlet all date values will be stored to the report in ISO format, e.g. 2020-12-31 10:11:12+02:00 for a date in the European central timezone that is UTC+1 in winter time and UTC+2 in summer time.
- Line 23, 26 introduce a new property, a calculated duration. From the start time and end time values of a planned start and optionally of a past start the difference in seconds is calculated and forwarded to the report.
Line 27: The list of properties per result item of the daily plan is piped to the Export-Excel cmdlet that is available with the ImportExcel PowerShell module. The report file name is specified and optionally the worksheet. For a full list of parameters see the ImportExcel PowerShell Module.

Daily Plan Report (Linux version)

<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Report Daily Plan" process_class="agent_linux">
  <script language="shell"><![CDATA[pwsh -NoLogo -NonInteractive -Command '& {
    . $env:SCHEDULER_DATA/config/powershell/JobScheduler.PowerShell_profile.ps1
	Import-Module $env:SCHEDULER_DATA/config/powershell/Modules/ImportExcel
	Import-Module $env:SCHEDULER_DATA/config/powershell/Modules/JobScheduler

	Connect-JobScheduler -Url $JOCCockpitUrl -Credential $JOCCockpitCredential | Out-Null

	# start mode mapping
	$startModes = @{"0"="single start"; "1"="repeat start-start"; "2"="repeat end-start"}

	# Dates in local time zone, output includes local date format
	Get-JSDailyPlan -Timezone (Get-Timezone) `
                |  Select-Object -Property @{name="Job Chain/Job"; expression={ "$($_.jobChain)$($_.job)"}}, `
                                           @{name="Order ID"; expression={$_.orderId}}, `
                                           @{name="Status"; expression={$_.state._text}}, `
                                           @{name="Job Stream"; expression={$_.jobStream}}, `
                                           @{name="Late"; expression={$_.late}}, `
                                           @{name="Start Type"; expression={ $startModes[ "$($_.startMode)"] }}, `
                                           @{name="Repeat Interval"; expression={$_.period.repeat}}, `
                                           @{name="Planned Start Time"; expression={ Get-Date $_.plannedStartTime }}, `
                                           @{name="Expected End Time"; expression={ Get-Date $_.expectedEndTime }}, `
                                           @{name="Expected Duration (sec.)"; expression={ (New-Timespan -Start "$($_.plannedStartTime)" -End "$($_.expectedEndTime)").Seconds }}, `
                                           @{name="Start Time"; expression={ Get-Date $_.startTime }}, `
                                           @{name="End Time"; expression={ Get-Date $_.endTime }}, `
                                           @{name="Duration (sec.)"; expression={ (New-Timespan -Start "$($_.startTime)" -End "$($_.endTime)").Seconds }} `
                | Export-Excel -Path /tmp/jobscheduler_reporting.xlsx -WorksheetName "Daily-Plan" -ClearSheet
}'
]]></script>
  <run_time/>
</job>

Explantions

The Add-JobSchedulerEvent cmdlet returns an event object that can be used for later pipelining
Event processing occurs asynchroneously, therefore newly created events might take some seconds to be available for retrieval..
The Get-JobSchedulerEvent cmdlet retrieves an array of event objects that are available with JobScheduler.
A previously created event object can be pipelined to the Remove-JobSchedulerEvent cmdlet.
In a more general sense all events as returned from Get-JobSchedulerEvent can be pipelined to the Remove-JobSchedulerEvent cmdlet.

Space shortcuts

Page tree

Starting Situation

Use Cases

Report Daily Plan from a job