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.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
Function ConvertCSV-ToExcel
{
<#
.SYNOPSIS
Converts one or more CSV files into an excel file.
.DESCRIPTION
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
.EXAMPLE
Get-ChildItem *.csv | ConvertCSV-ToExcel -output 'report.xlsx'
.EXAMPLE
ConvertCSV-ToExcel -inputfile 'file.csv' -output 'report.xlsx'
.EXAMPLE
ConvertCSV-ToExcel -inputfile @("test1.csv","test2.csv") -output 'report.xlsx'
.NOTES
Author: Boe Prox
Date Created: 01SEPT210
Last Modified:
#>
#Requires -version 2.0
[CmdletBinding(
SupportsShouldProcess = $True,
ConfirmImpact = 'low',
DefaultParameterSetName = 'file'
)]
Param (
[Parameter(
ValueFromPipeline=$True,
Position=0,
Mandatory=$True,
HelpMessage="Name of CSV/s to import")]
[ValidateNotNullOrEmpty()]
[array]$inputfile,
[Parameter(
ValueFromPipeline=$False,
Position=1,
Mandatory=$True,
HelpMessage="Name of excel file output")]
[ValidateNotNullOrEmpty()]
[string]$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
$workbook.worksheets.Item(2).delete()
#After the first worksheet is removed,the next one takes its place
$workbook.worksheets.Item(2).delete()
#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
$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
$worksheet.Paste()
#Close temp workbook
$tempcsv.close()
#Select all used cells
$range = $worksheet.UsedRange
#Autofit the columns
$range.EntireColumn.Autofit() | out-null
$i++
}
}
End {
#Save spreadsheet
$workbook.saveas("$output")
Write-Host -Fore Green "File saved to $output"
#Close Excel
$excel.quit()
#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 = "me@contoso.com"
$From = "helpdesk@contoso.com"
$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 = "SMTPServer.contoso.com"
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