Step #1: Create a new Powershell file with the below code in it. Name the file "Check_SQL_Servers.ps1".
Step #2: Create a new text file with a list of Microsoft SQL Servers in it. Name the file "AllSQLServers".
Step #3: Create a new batch and include in it: "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -file "Check_SQL_Servers.ps1"".
Name the file "CheckSQLServers".
Step #4: Create a new scheduled task to execute the new batch file.
Step #5: Create a folder called "Reports".
Include all these items in the same directory.
Powershell Code:
# GET SQL SERVER RESOURCES
$strFileName = 'U:\Powershell\Reports\CPUUsage.txt'
$strFileName2 = 'U:\Powershell\Reports\TestConnection.txt'
$strFileName3 = 'U:\Powershell\Reports\DiskUsage.txt'
$strFileName4 = 'U:\Powershell\Reports\MEMUsage.txt'
If (Test-Path $strFileName) {Remove-Item $strFileName}
If (Test-Path $strFileName2) {Remove-Item $strFileName2}
If (Test-Path $strFileName3) {Remove-Item $strFileName3}
If (Test-Path $strFileName4) {Remove-Item $strFileName4}
foreach ($svr in get-content "U:\Powershell\AllSQLServers.txt")
{
write-host '# Test Connection Check - ' $svr '...'
Test-Connection $svr | Out-File U:\Powershell\Reports\TestConnection.txt -append
}
foreach ($svr in get-content "U:\Powershell\AllSQLServers.txt")
{
write-host '# CPU Usage Check - ' $svr '...'
$svr | Out-File U:\Powershell\Reports\CPUUsage.txt -append
#Get-WmiObject -class Win32_PerfFormattedData_PerfOS_Processor -Property Name,PercentProcessorTime -ComputerName $svr | where{$_.Name -eq "_Total"} | Out-File U:\Powershell\Reports\CPUUsage.txt -append
Get-WmiObject win32_processor -ComputerName $svr | Measure-Object -property LoadPercentage -Average | Out-File U:\Powershell\Reports\CPUUsage.txt -append
}
foreach ($svr in get-content "U:\Powershell\AllSQLServers.txt")
{
write-host '# Disk Usage Check - ' $svr '...'
$svr | Out-File U:\Powershell\Reports\DiskUsage.txt -append
Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne "_Total"} | select-object Name, PercentFreeSpace | format-list | Out-File U:\Powershell\Reports\DiskUsage.txt -append
#$svr +":"; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne "_Total"} | select-object Name, PercentFreeSpace | format-list | Out-File U:\Powershell\DiskUsage.txt -append
#filter percent free:
#$svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne "_Total" -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list
}
foreach ($svr in get-content "U:\Powershell\AllSQLServers.txt")
{
write-host '# MEM Usage Check - ' $svr '...'
$svr | Out-File U:\Powershell\Reports\MEMUsage.txt -append
Get-WmiObject win32_OperatingSystem -ComputerName $svr | %{"Total Physical Memory: {0}KB`n, Free Physical Memory : {1}KB`n, Total Virtual Memory : {2}KB`n, Free Virtual Memory : {3}KB" -f $_.totalvisiblememorysize, $_.freephysicalmemory, $_.totalvirtualmemorysize, $_.freevirtualmemory} | Out-File U:\Powershell\Reports\MEMUsage.txt -append
}
$files = Get-ChildItem “U:\Powershell\Reports\” | Where {-NOT $_.PSIsContainer} | foreach {$_.fullname}
Send-MailMessage -To 'xyz@xyz.ca' -From 'xyz@xyz.ca' -SMTPServer 'xya.xya.ca' -Subject 'Powershell - SQL Server Daily Resource Report' -Body 'Files attached' -Attachments $files
Image 1:
|
|
|
|
|
|