powershell - Task Scheduler - get history information into script variables

Question

Is there a way of getting the task scheduler history information into an array or variable inside a batch or PowerShell script.

For example, get the information such as task name, date and time when the task started (event ID: 100 ) and when it completed (event ID: 102). This is so I can update a SQL database with the information. (the sql table may look like this and I know how to insert into the database once ive got the information)

TaskName    TaskStart              TaskCompleted
task1       27/09/2017 09:00:00    27/09/2017 10:00:00    
task2       27/09/2017 12:00:00    27/09/2017 16:00:00    
task1       04/10/2017 09:00:00    04/09/2017 09:55:00    

Basically I don't know how to retrieve that information, if it is even possible. thanks

Answer 1

The Task Scheduler logs to the following event channel:
Microsoft-Windows-TaskScheduler/Operational

You can use Get-WinEvent to gather the events. Start out by defining a filter hash table for the id 100 start events

# Event filter for the initial query for all "Start" events in the last 24 hours
$EventFilter = @{
    LogName = 'Microsoft-Windows-TaskScheduler/Operational'
    Id = 100
    StartTime = [datetime]::Now.AddDays(-1)
}

We're gonna need to extract some property values from the start event, in order to find the correlated completion event, so let's create a Property Selector

# PropertySelector for the Correlation id (the InstanceId) and task name
[string[]]$PropertyQueries = @(
    'Event/EventData/Data[@Name="InstanceId"]'
    'Event/EventData/Data[@Name="TaskName"]'
)
$PropertySelector = New-Object System.Diagnostics.Eventing.Reader.EventLogPropertySelector @(,$PropertyQueries)

Now retrieve the start events, find the corresponding completion event, and output the information as a new custom object:

# Loop through the start events
$TaskInvocations = foreach($StartEvent in Get-WinEvent -FilterHashtable $EventFilter){
    # Grab the InstanceId and Task Name from the start event
    $InstanceId,$TaskName = $StartEvent.GetPropertyValues($PropertySelector)

    # Create custom object with the name and start event, query end event by InstanceId
    [pscustomobject]@{
        TaskName = $TaskName
        StartTime = $StartEvent.TimeCreated
        EndTime = $(Get-WinEvent -FilterXPath "*[System[(EventID=102)] and EventData[Data[@Name=""InstanceId""] and Data=""{$InstanceId}""]]" -LogName 'Microsoft-Windows-TaskScheduler/Operational' -ErrorAction SilentlyContinue).TimeCreated
    }
}

You can populate a DataTable with the objects in $TaskInvocations, or generate an insert query based on the property values