Daily Drive Space Report

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