Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Monday, October 21, 2013

Documenting Scheduled Tasks for all of my Servers

Yeah I'm getting the itch to write more regularly. I've only had yesterday's post in about 2 months, and it's time to get some stuff out there!

Today, I'm going to talk about a report that I run every week that collects every scheduled task running on my servers, puts it all into an excel file, and emails it to me.

Now, because it uses Excel, this is a task that needs to be run manually. Also, before you run it you should have a C:\Temp folder, and a list of servers in C:\lists\TaskSched-servers.txt -- or you can change those lines, which are below the functions.

#First, here are the functions that deal with Excel:
Function Release-Ref ($ref) 
        [System.__ComObject]$ref) -gt 0)

Function ConvertCSV-ToExcel
    Converts one or more CSV files into an excel file.
    Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
    file being the name of the worksheet.
  .PARAMETER inputfile
    Name of the CSV file being converted
  .PARAMETER output
    Name of the converted excel file
  Get-ChildItem *.csv | ConvertCSV-ToExcel -output 'report.xlsx'
  ConvertCSV-ToExcel -inputfile 'file.csv' -output 'report.xlsx'
  ConvertCSV-ToExcel -inputfile @("test1.csv","test2.csv") -output 'report.xlsx'
  Author: Boe Prox      
  Date Created: 01SEPT210      
  Last Modified:  
#Requires -version 2.0  
    SupportsShouldProcess = $True,
    ConfirmImpact = 'low',
DefaultParameterSetName = 'file'
Param (    
     HelpMessage="Name of CSV/s to import")]
     HelpMessage="Name of excel file output")]

Begin {     
    #Configure regular expression to match full path of each file
    [regex]$regex = "^\w\:\\"
    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)
    #Create Excel Com Object
    $excel = new-object -com excel.application
    #Disable alerts
    $excel.DisplayAlerts = $False

    #Show Excel application
    $excel.Visible = $False

    #Add workbook
    $workbook = $excel.workbooks.Add()

    #Remove other worksheets
    #After the first worksheet is removed,the next one takes its place

    #Define initial worksheet number
    $i = 1

Process {
    ForEach ($input in $inputfile) {
        #If more than one file, create another worksheet for each file
        If ($i -gt 1) {
            $workbook.worksheets.Add() | Out-Null
        #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
        $worksheet = $workbook.worksheets.Item(1)
        #Add name of CSV as worksheet name
        $ = "$((GCI $input).basename)"

        #Open the CSV file in Excel, must be converted into complete path if no already done
        If ($regex.ismatch($input)) {
            $tempcsv = $excel.Workbooks.Open($input) 
        ElseIf ($regex.ismatch("$($input.fullname)")) {
            $tempcsv = $excel.Workbooks.Open("$($input.fullname)") 
        Else {    
            $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")      
        $tempsheet = $tempcsv.Worksheets.Item(1)
        #Copy contents of the CSV file
        $tempSheet.UsedRange.Copy() | Out-Null
        #Paste contents of CSV into existing workbook

        #Close temp workbook

        #Select all used cells
        $range = $worksheet.UsedRange

        #Autofit the columns
        $range.EntireColumn.Autofit() | out-null

End {
    #Save spreadsheet

    Write-Host -Fore Green "File saved to $output"

    #Close Excel

    #Release processes for Excel
    $a = Release-Ref($range)

#Now here's the meat and potatoes:
$servers = Get-Content \\server\c$\lists\TaskSched-servers.txt
Remove-Item "C:\Temp\Scheduled Tasks Documentation.csv" -ErrorAction SilentlyContinue
$TempFile = "C:\Temp\Scheduled Tasks Documentation.csv"
$Attachment = "C:\temp\Scheduled Tasks Documentation.xlsx"
$Tasks = @()

Foreach ($Computername in $Servers){
$schtask = (schtasks.exe /query /s $ComputerName /V /FO CSV | ConvertFrom-Csv)
$schtask = ($schtask | where {$_.Taskname -notlike "*\Microsoft*" -and $_.Taskname -notlike "Taskname"})
$schtask = ($schtask | where {$_."Run as User" -notlike "Network Service"})
if ($schtask){
foreach ($sch in $schtask){
$sch  | Get-Member -MemberType Properties | 
ForEach -Begin {$hash=@{}} -Process {
If ($WithSpace){
($hash.($_.Name)) = $sch.($_.Name)} #End If
Else {
($hash.($($_.Name).replace(" ",""))) = $sch.($_.Name)} #End Else
} -End {$Tasks += (New-Object -TypeName PSObject -Property $hash)} #End Foreach
} #End Foreach
} #End If
} #End Foreach
$Tasks | select Hostname, TaskName, ScheduledTaskState, Status, LastResult, RunasUser, TasktoRun, Comment, NextRunTime, LastRunTime, ScheduleType, StartTime, Months, Days, Repeat:Every | export-csv $tempfile

#This Removes the first line of the file, which is just junk
$x = get-content $tempfile
$x[1..$x.count] | set-content $Tempfile

#Use the Functions above to import the CSV and output an Excel file
ConvertCSV-ToExcel -inputfile $Tempfile -output $Attachment

#Email me the file
$To = ""
$From = ""
$Subject = "PS Report - Scheduled Tasks - Documentation Purposes"
$Body = "This is a list of scheduled tasks on all servers, to be used for documentation purposes"
$SMTPServer = ""
Send-Mailmessage -to $To -Subject $subject -From $From -body $body -smtpserver $SMTPServer -attachments $Attachment

#Delete the Temp Files
remove-item $Attachment -force
Remove-Item $Tempfile

No comments:

Post a Comment