This post aims to setup an automatic space report email script for Windows. In Linux, the function df -m is used to display the amount of space on drives in clear readable manner.
/* Snippet taken from MOBAXTERM */
[2016-06-29 15:01.14] /drives/c/mine/sql
[$$$$] ➤ df -m
Filesystem 1M-blocks Used Available Use% Mounted on
C:/mine/home 304893 108067 196826 35% /home/mobaxterm
C: 304893 108067 196826 35% /drives/c
J: 215040 177142 37898 82% /drives/j
S: 112635 87374 25261 78% /drives/s
U: 572412 438530 133882 77% /drives/u
V: 153597 22683 130914 15% /drives/v
W: 153597 22683 130914 15% /drives/w
X: 215040 177142 37898 82% /drives/x
Y: 153597 22683 130914 15% /drives/y
Z: 572412 438530 133882 77% /drives/z
───────────────────────────────────────────────────────────────────
[2016-06-30 08:57.54] /drives/c/mine/sql
[$$$$] ➤
In Windows, there is no df -m nor does the UnxUtils df.exe seem to want to work as shown below.
C:\mine\Programs\unixutils\df -m
C:\mine\Programs\unixutils\df: cannot read table of mounted filesystems
To help keep tabs on a development environment and the disk space the backups consume, I've resorted a home made solution.
REQUIREMENTS:
1. Access to Task Scheduler
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts
Step 1
Create the directories
mkdir E:\dba\script\backupfile_report
Step 2
Create the file backupfile_report.bat in the above working directory. Once created, load it with the following script:
SET WORKING_DIR=E:\dba\script\backupfile_report
SET PATH=%PATH%;%WORKING_DIR%
SET MONITOR_DIRECTORY=G:\RMAN_Backups
cd %WORKING_DIR%
E:
SETLOCAL EnableDelayedExpansion
REM change code page 1252 is used to remove the Unicode characters of the dir command output
chcp 1252
REM ^ is used to escape characters like greater or less than sybols from the output
REM The pre tag is used so that the email spaces the output at fixed spacing
echo ^<pre^> > %WORKING_DIR%\backupfile_report.log
echo FILES IN BACKUP DIR >> %WORKING_DIR%\backupfile_report.log
echo =================== >> %WORKING_DIR%\backupfile_report.log
dir /O:D %MONITOR_DIRECTORY% | find "7z" >> %WORKING_DIR%\backupfile_report.log
echo ^<br^> >> %WORKING_DIR%\backupfile_report.log
echo FREE SPACE >> %WORKING_DIR%\backupfile_report.log
echo ========== >> %WORKING_DIR%\backupfile_report.log
dir G:\ | find "bytes free" >> %WORKING_DIR%\backupfile_report.log
echo ^</pre^> >> %WORKING_DIR%\backupfile_report.log
mailhtml.vbs <FROM_ADDRESS> <TO_ADDRESS> "<SERVER> Backup Location Report" %WORKING_DIR%\backupfile_report.log
Step 3
Create the emailing script mailhtml.vbs in the same directory as the batch file.
Load the file up with the following script.
'Grab the parameters
from_address = WScript.Arguments(0)
to_address = WScript.Arguments(1)
subject = WScript.Arguments(2)
file = WScript.Arguments(3)
'Set the file reading variables
Const ForReading = 1
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
'Set the mail properties
SMTPServer = "<SMTP_RELAY_SERVER>"
Recipient = to_address
From = from_address
Subject = subject
'Create the mail object
GenericSendmail SMTPserver, From, Recipient, Subject, Message
Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)
Message = fso.OpenTextFile(file,ForReading).ReadAll
'Assisgn the values to our mail object and send the mail off
set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Subject = Subject
msg.HTMLBody = Message
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub
Step 4
Create the task scheduler for the batch script. To see how this is done, see Step 4 in the "Cluster Health Report" post. Alternatively, import the following task scheduler export:
<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
<RegistrationInfo>
<Date>2016-06-29T14:36:45.1784573</Date>
<Author>ADMIN</Author>
<Description>This reports to us the G:\RMAN_Backup location as well as the space left on the G drive.</Description>
</RegistrationInfo>
<Triggers>
<CalendarTrigger>
<StartBoundary>2016-06-29T05:30:00</StartBoundary>
<Enabled>true</Enabled>
<ScheduleByDay>
<DaysInterval>1</DaysInterval>
</ScheduleByDay>
</CalendarTrigger>
</Triggers>
<Principals>
<Principal id="Author">
<UserId>ADMIN</UserId>
<LogonType>S4U</LogonType>
<RunLevel>HighestAvailable</RunLevel>
</Principal>
</Principals>
<Settings>
<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings>
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings>
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden>
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>P3D</ExecutionTimeLimit>
<Priority>7</Priority>
</Settings>
<Actions Context="Author">
<Exec>
<Command>E:\dba\script\backupfile_report\backupfile_report.bat</Command>
<WorkingDirectory>E:\dba\script\backupfile_report\</WorkingDirectory>
</Exec>
</Actions>
</Task>
Here is the contents of the email sent.
OUTPUT:
FILES IN BACKUP DIR
===================
2016/06/27 05:00 PM 694 DB1_2016_06_27.7z
2016/06/27 05:12 PM 566 DB2_2016_06_27.7z
2016/06/27 05:33 PM 13 397 DB3_2016_06_27.7z
2016/06/27 05:38 PM 897 066 419 DB4_2016_06_27.7z
2016/06/27 05:38 PM 694 DB5_2016_06_27.7z
2016/06/27 05:47 PM 1 728 202 632 DB6_2016_06_27.7z
2016/06/27 05:56 PM 1 216 270 881 DB7_2016_06_27.7z
2016/06/27 06:06 PM 1 927 492 151 DB8_2016_06_27.7z
2016/06/27 06:14 PM 1 733 744 322 DB9_2016_06_27.7z
2016/06/28 05:00 PM 694 DB1_2016_06_28.7z
2016/06/28 05:24 PM 566 DB2_2016_06_28.7z
2016/06/28 05:54 PM 13 397 DB3_2016_06_28.7z
2016/06/28 05:58 PM 892 120 199 DB4_2016_06_28.7z
2016/06/28 05:58 PM 694 DB5_2016_06_28.7z
2016/06/28 06:07 PM 1 720 066 139 DB6_2016_06_28.7z
2016/06/28 06:16 PM 1 213 519 663 DB7_2016_06_28.7z
2016/06/28 06:39 PM 1 906 291 500 DB8_2016_06_28.7z
2016/06/28 06:54 PM 1 721 172 646 DB9_2016_06_28.7z
2016/06/29 05:00 PM 694 DB1_2016_06_29.7z
2016/06/29 06:10 PM 13 577 939 773 DB2_2016_06_29.7z
2016/06/29 07:00 PM 10 541 191 641 DB3_2016_06_29.7z
2016/06/29 07:16 PM 857 023 418 DB4_2016_06_29.7z
2016/06/29 07:29 PM 694 DB5_2016_06_29.7z
2016/06/29 08:13 PM 1 666 050 028 DB6_2016_06_29.7z
2016/06/29 08:49 PM 1 160 641 332 DB7_2016_06_29.7z
2016/06/29 09:16 PM 1 878 692 682 DB8_2016_06_29.7z
2016/06/29 09:43 PM 1 720 452 716 DB9_2016_06_29.7z
2016/06/29 10:18 PM 5 990 226 449 DB0_2016_06_29.7z
FREE SPACE
==========
13 Dir(s) 48 683 343 872 bytes free
