PowerShell allows you to easily export data into an Excel workbook. You can use this feature to keep track of everything from logon and logoff events to disk usage on one or multiple computers. In this guide, I am going to discuss a PowerShell script that enables you to audit the status of Group Policy scheduled tasks and write the results to an Excel workbook.

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.

Subscribe to 4sysops newsletter!

Scheduled tasks workbook

Scheduled tasks workbook

+2
1 Comment
  1. Suresh 2 years ago

    Hi Alex

    First of all , a BIG thank you for your wonderful script. As a newbie to PS, this has helped me a lot of time and effort.

    With that said , will it be possible that I can get this report for a set of servers in my domain ? We have about 50 servers that I need to run this aganist ? Is there a small tweak that could fetch me what I need ?

    Thanks again !

    0

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2021

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account