Auditing scheduled tasks with PowerShell and Excel

• Author
• Recent Posts

Alexander Pazik

Alexander is a Systems Engineer and IT Project Manager with a strong background in Active Directory, PowerShell, System Center, and VMware. You can connect with him on LinkedIn and learn more about the services offered by his consulting company Alxco Consulting

Latest posts by Alexander Pazik (see all)

• Configuration Items in Configuration Manager (SCCM, MECM) - Mon, Aug 22 2022
• Create and read SCVMM custom properties with PowerShell and the VMM Console - Mon, Apr 18 2022
• Prevent ransomware attacks on network shares with File Server Resource Manager (FSRM) - Mon, Mar 7 2022

This is very useful as this script will identify not only whether a scheduled task is successfully running, but also the last time the scheduled task successfully ran.

In addition to Microsoft Office, you will also need to have RSAT (Active Directory Users and Computers) installed on your computer to follow this guide. You will also need to make sure the necessary firewall ports are open for your workstation to communicate with the other computers in the OU you are querying. This can be done by allowing remote administration exceptions and enabling the firewall rule for WinRM.

Additionally, you will need to ensure the user account you are running the script under has "read" permissions to the OU you are querying, as well as Administrator permissions on all the computers in that OU. Now we are ready to begin writing our script.

Let's start off by telling PowerShell to open Excel and create a new workbook:

# Declare Variables
$Path = "C:\Users\$env:USERNAME\Desktop"
# Create Excel Workbook
# Open Excel
$excel = New-Object -ComObject excel.application
# Make Excel Visible and Hide Alerts (OPTIONAL)
$excel.visible = $true
$excel.DisplayAlerts = $true
# Add a Workbook
$workbook = $excel.Workbooks.Add()
# Select the Workbook
$workbook = $excel.Workbooks.Item(1)

If you would like to create the workbook silently in the background, change the two values under the comment "# Make Excel Visible and Hide Alerts (OPTIONAL)" from $true to $false. Next you have to add the command that's going to import the Active Directory (AD) module and query the desired OU:

# Query Active Directory OU
$AD = Get-ADComputer -Filter {OperatingSystem -like "*"} -Properties Name -SearchScope Subtree –SearchBase "OU=OrgUnit3,OU=OrgUnit2,OU=OrgUnit1,DC=your,DC=domain,DC=here" | Sort-Object Name

With the command to query AD now added, we can specify the commands to pull information from AD and dump it into our first worksheet, which will contain information about our first scheduled task.

# Create Worksheet 1
$worksheet1 = $workbook.Worksheets.Item("Sheet1")
$worksheet1.Name = "Your Scheduled Task #1 Name"
$worksheet1.Cells.Item(1,1) = 'Computer'
$worksheet1.Cells.Item(1,2) = 'Last Run'
$worksheet1.Cells.Item(1,3) = 'Result'
$worksheet1.Cells.Item(1,4) = 'Date'
$row = 2
$column = 1 
# Edit Worksheet 1
$AD |
ForEach-Object {
    Invoke-Command -ComputerName $_.Name -ScriptBlock {Get-ScheduledTaskInfo -TaskName "Scheduled Task 1" -TaskPath \ } -ErrorAction SilentlyContinue -ErrorVariable ConnError | % {
    if($_.LastTaskResult -EQ '0')
        {
        $Name = $_.PSComputerName
        $LRT = $_.LastRunTime
        $Result = "SUCCESS"
        $Date = get-date -format "MM/dd/yyyy"
        }
    if($_.LastTaskResult -NE '0')
        {
        $Name = $_.PSComputerName
        $LRT = $_.LastRunTime
        $Result = "FAILURE"
        $Date = get-date -format "MM/dd/yyyy"
        }
    }
    if($ConnError)
        {
        $Name = $_.Name
        $LRT = "N/A"
        $Result = "ERROR"
        $Date = get-date -format "MM/dd/yyyy"
        }
#Name
$worksheet1.Cells.Item($row,$column) = $Name
$column++
#Last Run Time
$worksheet1.Cells.Item($row,$column) = $LRT
$column++
#Result
$worksheet1.Cells.Item($row,$column) = $Result
$column++
#Date
$worksheet1.Cells.Item($row,$column) = $Date
$column++
$range = $worksheet1.Range(("A{0}"  -f $row),("D{0}"  -f $row))
$range.Select() | Out-Null
$usedRange = $worksheet1.UsedRange                        
$usedRange.EntireColumn.AutoFit() | Out-Null
If ($Result -EQ "SUCCESS") {
        #Sucess
        $range.Interior.ColorIndex = 10
    }
If ($Result -EQ "FAILURE") {
        #Failure
        $range.Interior.ColorIndex = 53
    }
If ($Result -EQ "ERROR") {
        #Error
        $range.Interior.ColorIndex = 15
    }
$column++
$row++
$column = 1
    }

This script is based on the logic that if the scheduled task exits with an error code of 0, then the task has run successfully. If the scheduled task exits with an error code other than 0, most likely the task did not run correctly or an error occurred at some point. If the computer cannot be reached over WinRM to check the status of the scheduled task, the script will make a note of this as an error and log it in the workbook.

Depending on the last run result, PowerShell will apply a custom color of green, red, or gray to the row containing the computer name and relevant information. Green meaning Success, red meaning Failure, and gray meaning Connection Error.

With this information in mind, we are now going to add the other commands to create a second worksheet for another scheduled task:

# Create Worksheet 2
$worksheet2 = $excel.Worksheets.Add()
$worksheet2 = $workbook.Worksheets.Item("Sheet2")
$worksheet2.Name = "Your Scheduled Task 2 Name"
$worksheet2.Cells.Item(1,1) = 'Computer'
$worksheet2.Cells.Item(1,2) = 'Last Run'
$worksheet2.Cells.Item(1,3) = 'Result'
$worksheet2.Cells.Item(1,4) = 'Date'
$row = 2
$column = 1
# Edit Worksheet 2h
$AD |
ForEach-Object {
    Invoke-Command -ComputerName $_.Name -ScriptBlock {Get-ScheduledTaskInfo -TaskName "Scheduled Task 2" -TaskPath \ } -ErrorAction SilentlyContinue -ErrorVariable ConnError | % {
    if($_.LastTaskResult -EQ '0')
        {
        $Name = $_.PSComputerName
        $LRT = $_.LastRunTime
        $Result = "SUCCESS"
        $Date = get-date -format "MM/dd/yyyy"
        }
    if($_.LastTaskResult -NE '0')
        {
        $Name = $_.PSComputerName
        $LRT = $_.LastRunTime
        $Result = "FAILURE"
        $Date = get-date -format "MM/dd/yyyy"
        }
    }
    if($ConnError)
        {
        $Name = $_.Name
        $LRT = "N/A"
        $Result = "ERROR"
        $Date = get-date -format "MM/dd/yyyy"
        }
#Name
$worksheet2.Cells.Item($row,$column) = $Name
$column++
#Last Run Time
$worksheet2.Cells.Item($row,$column) = $LRT
$column++
#Result
$worksheet2.Cells.Item($row,$column) = $Result
$column++
#Date
$worksheet2.Cells.Item($row,$column) = $Date
$column++
$range = $worksheet2.Range(("A{0}"  -f $row),("D{0}"  -f $row))
$range.Select() | Out-Null
$usedRange = $worksheet2.UsedRange                        
$usedRange.EntireColumn.AutoFit() | Out-Null
If ($Result -EQ "SUCCESS") {
        #Sucess
        $range.Interior.ColorIndex = 10
    }
If ($Result -EQ "FAILURE") {
        #Failure
        $range.Interior.ColorIndex = 53
    }
If ($Result -EQ "ERROR") {
        #Error
        $range.Interior.ColorIndex = 15
    }
$column++
$row++
$column = 1 
    }

With the worksheets now created, we can add the commands to close and save the workbook with the current date as the name:

# Save Excel Workbook
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$Format = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$workbook.SaveAs("$Path\$(get-date -format "MM.dd.yyyy").xls", $Format)
# Quit Excel
$excel.Workbooks.Close()
$excel.Quit()

Note that the workbook will be saved to the location specified at the beginning of the script. In this case, I chose to save the workbook to the Desktop. Once the script finishes running, open your newly created workbook to view the status of your scheduled tasks.

Scheduled tasks workbook