This is the first PowerShell script that I wrote. I was inspired to start using PowerShell after I met with one of our developers. He creates scripts to automate repetitive administrative tasks (e.g. daily system health check). I asked the developer if he learned writing PowerShell from a book or a training class. He said that he neither read any book nor attend a PowerShell training class. Instead, he told me that he just knows what he wanted to achieve and then he searched the internet on how to achieve it. In other words, he just Googled the codes.
I always wanted to learn PowerShell. As a DBA, I think it would be beneficial to use this powerful tool. I read books about PowerShell and did the exercises but I feel I never progressed and I’m still not able to create anything.
So I tried to do what the developer told me. First, I started to think what I want to achieve and that is to send disk space usage of our Database Servers to my email. This is so that I won’t have to login to our DB servers everytime.
Below is the PowerShell script.
#.SYNOPSIS
# Sends Daily Disk Space Usage Report
#
#EXAMPLE
#.\Send-DiskSpaceUsageReport.ps1
#
$smtpServer = "Fsmtp.our.property.com.sg"
$smtpFrom = "NXPOPARVRAPPLE@nhg.local"
$smtpTo = "valenciajec@ncs.com.sg"
$messageSubject = "OPAS TTSH Daily Disk Space Usage Report"
[string]$messagebody = ""
$logs = Get-Content D:\DBA\DB_Health_Checks_Node1\6.DiskUsage_Node1_/TTSH.txt, \\10.240.xx.xx\d$\DBA\DB_Health_Checks_Node2\2.DiskUsage_Node2_TTSH.txt
foreach ($log in $logs )
{
$messagebody = $messagebody + $log + "`r`n"
}
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($smtpFrom,$smtpTo,$messagesubject,$messagebody)
This script will add a message body to emails sent from script. I have a SQL Server Agent job that generates the text file that contains the disk space usage report. So basically, the PowerShell script will get the contents of the text file and add it to the message body of the email.
The next step is to create a Scheduled Task to run the PowerShell script.
Open Task Scheduler and Create a new task. Name it and set your security options. Check "Run with highest privileges" as our scripts need to run as admin. Choose “Run whether user is logged on or not.”
Click on Actions tab and click New.
Action: Start a program
Program /script: PowerShell.exe
You don’t need to put a path as it should already be on your system
You first need to set the ExecutionPolicy. There are two options to do that either you set the ExecutionPolicy on the machine or on a per-script basis. I will only set it on a per-script basis but If you want to set the execution policy globally, you can issue this command from within PowerShell.
Set-ExecutionPolicy Unrestricted
To set the ExecutionPolicy on a per script basis, put the line below on the Add arguments (change the file name to your script’s file name.ps1)
-ExecutionPolicy Bypass -File D:\DBA\Scripts\PowerShell\Send-DiskSpaceUsageReport.ps1
Indicate the path of your script in the Start in(optional)
I encountered the error “Launch request ignored, instance already running (154257)”and the task failed to run. To resolve this issue, I changed the setting to “Stop the existing instance”
