Get Task Scheduler log and save into a table

Question

Hello,

How do I get windows server "Task Scheduler" history log using PowerShell script and save the schedulers details into table in SQL Server.

I want the below information

1. Task Name
2. Task Schedule Time
3. Task Start date and time
4. Task Finish date and time
5. Task Next run date and time
6. Task Completed Successfully or not

Below is table script and required field which I want to save into the table.

CREATE TABLE [dbo].[TaskSchedulerDetails](

[Id] [int] IDENTITY(1,1) NOT NULL,

[TaskName] [nvarchar] NULL,

[ScheduleTaskTime] [nvarchar] NULL,

[TaskStartDateTime] [datetime] NULL,

[TaskEndDateTime] [datetime] NULL,

[NextRunDatetime] [datetime] NULL,

[IsTaskSuccess] [bit] NULL,

CONSTRAINT [PK_TaskSchedulerDetails] PRIMARY KEY CLUSTERED

(

[Id] ASC

)

)

Please help on this.

Thanks in advance.

Answer 1

Here is a script that I had that reports on a specific task or all tasks for a given number of hours. You can modify it to write to SQL.

#----------------------------------------------
# Script: TaskResults.ps1
# Author: Motox80 on Microsoft Technet Forums
#----------------------------------------------
param( $TaskName = '', 
       $Hours=1,
       [switch]$debug)                                    # The task name to monitor
#start-transcript -path c:\temp\tr.log
cls
"Analyzing task results for {0} for a period of {1} hours. " -f $taskname, $Hours                    # Time frame in hours
$tf = $Hours * 3600000
$elna = @('Microsoft-Windows-TaskScheduler/Operational')
#$AllEvents = $null
if ($AllEvents -eq $null) {
    'Reading events.'
    $AllEvents = @()               # prepare array so we can append to it
    
    $xml = "<QueryList><Query Id=""0"" Path=""Microsoft-Windows-TaskScheduler/Operational"">
            <Select Path=""Microsoft-Windows-TaskScheduler/Operational"">*[System[TimeCreated[timediff(@SystemTime) &lt;= $tf]]]</Select>
            </Query></QueryList>"
	
    $AllEvents = Get-WinEvent -FilterXml $XML -ErrorAction SilentlyContinue  # append the events (if any)
}
else {
    'Reusing event data.'              # when testing with ISE 
}
[System.Collections.ArrayList]$AllTasks =@()
foreach ($event in $Allevents){
    [xml]$xe = $event.ToXml()
    $TName = (($xe.Event.EventData.Data | Where-Object name -eq TaskName).'#text') + ""
    $IID = ($xe.Event.EventData.Data | Where-Object name -eq InstanceId).'#text' + ($xe.Event.EventData.Data | Where-Object name -eq TaskInstanceId).'#text'
    if ($debug) {
        "{0} - {1} - {2}" -f $event.Id, $TName,  $IID
    }
    if(($TName -match $Taskname) -or ($TaskName -eq "")) {
        if ($IID) {
            $cid = $Alltasks | where {$_.ID -eq $IID}
            if ($cid -eq $null) {                               #add a new entry
                $cid = [PSCustomObject]@{
                        ID       = $IID
                        Name     = $TName
                        Start    = $event.TimeCreated           # default to the event time 
                        End      = $null
                        Trigger  = $null
                        RC       = $null
                }
                $foo = $Alltasks.add($cid)
            }
            if ($event.Id -eq 100) {
                $cid.Start = $event.TimeCreated
            }
            if ($event.Id -eq 102) {
                $cid.End = $event.TimeCreated
            } 
            
            if ($event.Id -eq 107) {
                $cid.Trigger = 'Time'
            } 
            if ($event.Id -eq 110) {
                 $cid.Start = $event.TimeCreated
            } 
            if ($event.Id -eq 200) {
                $cid.Name = $TName 
            } 
            if ($event.Id -eq 201) {
                [long]$rc = ($xe.Event.EventData.Data | Where-Object Name -eq ResultCode).'#text'
                if ($rc -gt  2147483648) {      # greater than 8000 0000
                    $cid.rc = "{0:X}" -f $rc    # format as hex 
                } else {
                    $cid.rc =  $rc              # else use decimal 
                }  
            } 
            if ($event.Id -eq 203) {
                $cid.Trigger = "Failed"
                [long]$rc = ($xe.Event.EventData.Data | Where-Object Name -eq ResultCode).'#text'
                if ($rc -gt  2147483648) {      # greater than 8000 0000
                    $cid.rc = "{0:X}" -f $rc    # format as hex 
                } else {
                    $cid.rc =  $rc              # else use decimal 
                }  
                 
            } 
			#$lastevent = $event 
        } else {    
              # "{0}  {1}   {2}" -f $event.Id, $event.TimeCreated, ($event.Message + ( " " * 160)).substring(0,160).trim()
        }
    }    
}
# Now output the reults
if ($debug) {
    $AllTasks | Format-Table -AutoSize
    "==============================================================="
}
$AllTasks | Format-Table -AutoSize -Property Start, End, RC, Trigger, Name
#stop-transcript