PRTG Custom Notifications: Outbound Calls on “Down” Alarm

The last blog I wrote about custom file sensor to track an abnormally large file and raise alarm based on that helped me gaining more confidence in PRTG. I am now of opinion that good folks at Paessler AG have tried their level best to make it as open as they could when it comes to customization and integration with other systems. It worked great with Powershell to figure out a problem that could collapse one of my applications, and also, it gave me another idea.

Problem Statement

We use SMS and Email as primary notification channels for any warning or down state alarm. I use Nova Launcher on Note 5 having bug of not showing Email Count on my Nine (for MS Exchange) email client. For SMS, we previously had a product that somehow didn’t turn out to be totally compatible as per our requirement, so we’re developing our own at the moment. Further to this, even if both of them work, they’re not ample enough to wake anyone of us in case something serious happens in night (P.S: We don’t entertain night duties). The best communication medium therefore in such duration of entire day is nothing but the GSM or PSTN calls and we decided to use Robot Calls for “Down” state cases.

Pre-Requisites

  • PRTG (Free or Commercial)
  • CTI Integration Knowledge with your Voice System
  • PRTG Admin Access

PRTG Custom Notification Settings

Login with PRTGAdmin Account or any other account with same permission level and go to Setup > Notifications.

2016-03-12 22_15_07-Windows 7 - Dev [Running] - Oracle VM VirtualBox
PRTG Setup Page
Now click on “Add New Notification” button at the bottom of this page.

2016-03-12 22_23_06-Program Manager
Click on “Add New Notification”

Give this Custom Notification a name and trigger action as per your accord. I wanted to have only Down State therefore I selected as per screenshot:

2016-03-12 22_25_10-Windows 7 - Dev [Running] - Oracle VM VirtualBox
Name & Summarization
Set Access Rights as per your environment:

2016-03-12 22_36_50-Windows 7 - Dev [Running] - Oracle VM VirtualBox
Access Rights
Here starts the real part. In notification actions you have to check on Execute Program option. Provide the credential set which is capable of executing this program. In my case, I wrote a Powershell Script that required admin privileges. Notice the Program File name “Notify.ps1” which is our script – the medium of communication between PRTG and External Application. As per Paessler, here you can Select the program that PRTG executes. You can choose from *.exe, *.bat, *.cmd, *.com or *.ps1 files located in the Notifications/exe subfolder of your PRTG installation. If this list is empty, please copy all files you want to appear to the executables directory.

2016-03-12 22_38_44-Windows 7 - Dev [Running] - Oracle VM VirtualBox
Execute Program Setup

PRTG Host Machine Settings

 

Go to C:\Program Files (x86)\PRTG Network Monitor\Notifications\EXE OR C:\Program Files\PRTG Network Monitor\Notifications\EXE depending upon your 32 or 64 bit machine. This is the folder where you have to save your script. Just an idea about how I am doing it, we have a dialer with an option to keep on looking for a new number in DB table and dial it out the moment it finds one. So what I’m doing with this script is to execute a stored procedure in my MS SQL Server to update the records and once dialer finds it updated, there’s an outbound calls on all numbers inserted via stored procedure. The dialer with robot call option uses particular wav file (I generated one from here). You can use any other as per your requirement.

So here’s the Notify.ps1 making the execution of stored procedure possible:

===========================START===========================
$con = new-object "System.data.sqlclient.SQLconnection"
#Set Connection String
$con.ConnectionString =(“Data Source=mssqlserver.mydomain.local;Initial Catalog=HN_MSSQLDB;Integrated Security=SSPI;User ID=username;Password=mypassword”)
$con.open()
$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
#Let's Call Remote Procedure
$sqlcmd.CommandText = “EXEC [HN_MSSQLDB].[dbo].[UpdatePRTG]”
$rowsAffected = $sqlcmd.ExecuteNonQuery()

#This section you can use for time stamping with little improvement.
$text = "Alarm Raised Again"
$text | Out-File "C:\Program Files (x86)\PRTG Network Monitor\Notifications\EXE\test.txt"
#Exit 0 is important or the custom notification won't work.
exit 0;
============================END============================

To be honest, the script isn’t doing any extraordinarily intelligent stuff. It’s just communicating with SQL Server which further interacts with dialer. But the good thing is that we can do it pretty easily using PRTG.

Bringing it to Action

For some reason, I couldn’t get this part directly from Paessler’s stock documentation, so I have to use their forum from where I got a response pretty quickly within a day. It was simple enough. Simply navigate to the group/device/sensor for which you want to configure this notification, go to Notification Setting and create new notification as per your requirement. In my case, I wanted to do it for all of my machines and all sensors, so I went to the Root level and did my things as under which were then inherited across all the sensors:

2016-03-12 23_46_43-Windows 7 - Dev [Running] - Oracle VM VirtualBox
Select Root Level
And then create Alarm as under:

2016-03-12 23_52_47-Windows 7 - Dev [Running] - Oracle VM VirtualBox
Add Alarm with Custom Notification Created Earlier

Further Ideas

 

PROVIDED that I find some time, I will try to formulate more general standard setting to use minimal Asterisk based VM with SIP Trunk Setup to open the dialer part for organizations that may require it. Luckily, I didn’t have to work hard at this part in my scenario since we’re having Call Center as our core services.

Another idea is to let this setting be part of default PRTG Notification type, i.e. Email, SMS and SIP Calling (where user can provide SIP Details for calling).

 

Advertisements

PRTG Custom Sensor: Alarm for Large File

Requirement

Need to check for any file larger then let’s say 60 Mb and save its name somewhere, and raise alarm on PRTG.

Tools/Environment

Powershell, PRTG, Windows Server 2012 R2.

Powershell Script

Following script will look for any file larger then 60 Mb, of file type wav, in F:\Dir\SubDir\SubDirL2\SubDirL3\ and will save it to F:\Dir\res.txt and keep it on appending it with the results.

##Mention the path to search the files
$path = “F:\Dir\SubDir\SubDirL2\SubDirL3\”
##Find out the files greater than equal to below mentioned size
$size = 60MB
##Limit the number of rows in text file mentioning file names
$limit = 5
##Find out the specific extension file
$Extension = “*.wav”
##script to find out the files based on the above input
$largeSizefiles = get-ChildItem -path $path -recurse -ErrorAction “SilentlyContinue” -include $Extension | ? { $_.GetType().Name -eq “FileInfo” } | where-Object {$_.Length -gt $size} | sort-Object -property length -Descending | Select-Object Name, @{Name=”SizeInMB”;Expression={$_.Length / 1MB}},@{Name=”Path”;Expression={$_.directory}} -first $limit
IF ($largeSizefiles -eq $NULL)

{
$res = “NORMAL”
$date = Get-Date
Out-File -FilePath F:\Dir\res.txt -InputObject $date -Encoding Default
Out-File -FilePath F:\Dir\res.txt -InputObject $res -Encoding Default -Append
}
ELSE
{
$res = “ERROR”
$date = Get-Date -UFormat “%Y%m%d%H%M%S”
Out-File -FilePath F:\Dir\res.txt -InputObject $date -Encoding Default
Out-File -FilePath F:\Dir\res.txt -InputObject $res -Encoding Default -Append
$largeSizefiles | Out-File -FilePath F:\Dir\res.txt -Encoding Default -Append
}

Run Script Periodically

Using Windows Task Scheduler, the script can be run periodically every 5 minutes. Create new task with following settings:

2016-01-28 16_41_52-10.150.10.160 - Remote Desktop Connection

2016-01-28 16_43_49-10.150.10.160 - Remote Desktop Connection

2016-01-28 16_44_25-10.150.10.160 - Remote Desktop Connection.png

 

PRTG Sensor Settings

  • Go to corresponding probe and the machine where you set up the previous Powershell Script.
  • Go to device list of that machine and add new sensor “File Sensor” with following settings:

2016-01-28 16_52_59-60MB+ Recorded File _ Sensor Details _ PRTG

2016-01-28 16_55_12-60MB+ Recorded File _ Sensor Details _ PRTG2016-01-28 16_56_33-60MB+ Recorded File _ Sensor Details _ PRTG.png

Change Scanning interval to your desired time.

Results:

2016-01-28 16_58_39-60MB+ Recorded File _ Sensor Details _ PRTG

The error will be displayed whenever there’s wav file larger then 60 Mb in the location mentioned earlier. You can also look for any word other then “Error” and raise the alarm.

Powershell Script to Check Domain Machines Disk Sizes

We have around 15 servers in the data center all part of single domain. Our support folks used to take their Disk Volume Sized twice a day to send it to Analysis team. They were using Remote Desktop to manually login to take the sizes until I wrote this script to make their lives easier.

Please note that this is an unoptimized, first draft of the script. I will try to further improve and optimize it later on and will include Excel Integration with it.

<#
Script Name:                      SysCheckAuth
Author:                                Maaz Bin Mahmood
Tested Platforms:               Windows 8.1, Windows Server 2012
Dated:                                 26th October 2014
Contact Email:                    pointed.out@gmail.com
#>

<#First step is to declare the authentication. We will be needing Username in Domain\User format and Password that will be prompted in the beginning. We are assuming that all Servers have same password as happened in my case. The IP or Server Name is different for every machine. In case passwords are different too, you will have to prompt for password for every machine.
#>

$UserName = “DOMAIN\user”
$Password = Read-Host -AsSecureString “Enter Your Password:”
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $UserName , $Password

Write-Output “`r`n`r`nGenerating Health Check of Web Server 1… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.26 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “C Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.26 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.26 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.26 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Web Server 2… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.169.1.26 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.169.1.26 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.169.1.26 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.169.1.26 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of ACD Server 1… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.28 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.28 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.28 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.28 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of ACD Server 2… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.169.1.28 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.169.1.28 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.169.1.28 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.169.1.28 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of SQL Server 1… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalF = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.size}
$gbTotalF = [int64]$TotalF / 1GB
Write-Output “`r`nF Drive (DB File) Total Capacity: “$gbTotalF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeF = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.freespace}
$gbFreeF = [int64]$FreeF / 1GB
Write-Output “`r`nF Drive (DB File) Available Capacity: “$gbFreeF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalL = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’L:'” | Foreach-Object {$_.size}
$gbTotalL = [int64]$TotalL / 1GB
Write-Output “`r`nL Drive (Log File) Total Capacity: “$gbTotalL | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeL = get-WmiObject win32_logicaldisk -Computername 192.168.1.27 -Credential $Credential -Filter “DeviceID=’L:'” | Foreach-Object {$_.freespace}
$gbFreeL = [int64]$FreeL / 1GB
Write-Output “`r`nL Drive (Log File) Available Capacity: “$gbFreeL | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of SQL Server 2… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalF = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.size}
$gbTotalF = [int64]$TotalF / 1GB
Write-Output “`r`nF Drive (DB File) Total Capacity: “$gbTotalF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeF = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.freespace}
$gbFreeF = [int64]$FreeF / 1GB
Write-Output “`r`nF Drive (DB File) Available Capacity: “$gbFreeF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalL = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’L:'” | Foreach-Object {$_.size}
$gbTotalL = [int64]$TotalL / 1GB
Write-Output “`r`nL Drive (Log File) Total Capacity: “$gbTotalL | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeL = get-WmiObject win32_logicaldisk -Computername 192.169.1.27 -Credential $Credential -Filter “DeviceID=’L:'” | Foreach-Object {$_.freespace}
$gbFreeL = [int64]$FreeL / 1GB
Write-Output “`r`nL Drive (Log File) Available Capacity: “$gbFreeL | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Slave 1-1… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “C Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Slave 1-2… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.3.41 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.3.41 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.3.41 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.3.41 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Slave 1-3… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.3.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Slave 1-4… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.169.3.41 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.169.3.41 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.169.3.41 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.169.3.41 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of FLASH Server… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.35 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.35 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.35 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.35 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Recording Server… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalF = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.size}
$gbTotalF = [int64]$TotalF / 1GB
Write-Output “`r`nF Drive (DB File) Total Capacity: “$gbTotalF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeF = get-WmiObject win32_logicaldisk -Computername 192.168.1.40 -Credential $Credential -Filter “DeviceID=’F:'” | Foreach-Object {$_.freespace}
$gbFreeF = [int64]$FreeF / 1GB
Write-Output “`r`nF Drive (DB File) Available Capacity: “$gbFreeF | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Telepoti Server 1… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.168.1.32 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.168.1.32 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.168.1.32 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.168.1.32 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Write-Output “`r`n`r`nGenerating Health Check of Telepoti Server 2… Please wait`r`n_____________________________________________________________`n`r`n” | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append
$TotalC = get-WmiObject win32_logicaldisk -Computername 192.169.1.32 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.size}
$gbTotalC = [int64]$TotalC / 1GB
Write-Output “`r`nC Drive Total Capacity: “$gbTotalC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeC = get-WmiObject win32_logicaldisk -Computername 192.169.1.32 -Credential $Credential -Filter “DeviceID=’C:'” | Foreach-Object {$_.freespace}
$gbFreeC = [int64]$FreeC / 1GB
Write-Output “`r`nC Drive Available Capacity: “$gbFreeC | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$TotalD = get-WmiObject win32_logicaldisk -Computername 192.169.1.32 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.size}
$gbTotalD = [int64]$TotalD / 1GB
Write-Output “`r`nD Drive Total Capacity: “$gbTotalD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

$FreeD = get-WmiObject win32_logicaldisk -Computername 192.169.1.32 -Credential $Credential -Filter “DeviceID=’D:'” | Foreach-Object {$_.freespace}
$gbFreeD = [int64]$FreeD / 1GB
Write-Output “`r`nD Drive Available Capacity: “$gbFreeD | Tee-Object -FilePath  $PSScriptRoot\output.txt -Append

Email Campaign with Passing Variables using Outlook

We were campaigning for GITEX Technology Week when came through the problem of sending Unique Promo Codes to around 200 different Email IDs, saved in an Excel File. Copy pasting each unique code against each email address and then sending it, could have been tiresome method for our marketing lady and thus I decided to help her by introducing some automation.

Just to give you a little synopsis to the situation, I have recently joined this Microsoft products oriented organization after sticking to Linux based one after two years. Had it been a Linux Server, things would have been easier and better optimized, but with only 4 days of Microsoft experience after long time, I expect the code to be little buggy, though I tested it on 3 different machines, all of them running on Windows 8.1, Microsoft Outlook/Excel 2007. Also, I’m not a professional programmer, rather I’ve been in the vicinity of those creepy folks therefore I won’t be responsible if anything goes wrong (though there are least of such chances).The inspiration and the basis was taken from this site however, I found some presentation issues and scattered information across different pages and this I ended up writing this blog to put everything at simple point. Let’s Start…

The Excel File

Our Excel was something like that:

1.png

Figure 1 – Excel File Example

Notice the duplicate entries of emails in cells B2 and B4. The recipients shouldn’t receive multiple emails for the same content and therefore, we will be handling this duplication as well. You can use any other table however, to stay consistent to the VB Macro (code that will make it happen), you should use the emails in Column 2, or modify the macro according to your requirement.

Prerequisites

Windows 7 or 8, MS Outlook 2007 (or above), MS Excel 2007 (or above), Good enough Processor & RAM to handle 200 Outlook Drafts (Tested smoothly on Core i5 + 4GB), little programming knowledge & some HTML concepts.

Step – 1: Enable Developer Mode in MS Excel

By default the Developer tab which holds the option for running, creating and editing macros is turned off in the Ribbon.

Excel 2007

  1. Open the Excel file you have data in.

  2. Click on the Office logo in the left top and choose Excel Options.

  3. In the Popular section, enable the option: Show Developer tab in the Ribbon.

  4. Press OK to close the open dialog.

XT4d95L0bsRW5OThuo7zu-gjN9FRkY6-6-TFa1RaENqn5Tw-m9jl5ozUkA_YPdVUYhqc4nNrbe4gQZZruglKfiRxPpLx_EJjOx6KH2QdBsGmOjz5__svVU1XS7J0w-klJg

Figure 2 – Excel Ribbon Menu

Tztk5E6oOB_nc047oypCGeCaDLo2qjdGqVF36UuJC-aUNhjqMiA_6t__Xoj433OMyEcqHZswYXIgEY3nRwzyXqzP_JIdvf58DwO7rVzn0fGt2g5BWzgbdBosXQBOSVaxtg

Figure 3 – Excel Options

Step 2: Enable Macros in Excel

Macros are by default turned off in Excel for security reasons. You will need to turn them on in order to use them here in this case.

To turn on the Macros, go to the same Excel Options as indicated in Figure 3, however, now you have to select Trust Center option instead of default Popular option, as shown in Figure 4. Press Trust Center Settings on bottom right corner.

gPm4meLCD4GSRbPoaN738qFClOsob2ifUh3WbsW4RUJA1H90YB9MzXQvAus3JK_1mcNE6O2pI9wAMpDPGqM_r4gR-WJFJW9AmSmcZFCVNS4B57MF3ObmBl-5Etf8pYxUQA

Figure 4 – Trust Settings Center Button

Clicking on this button will open new Macro Options window. Select “Enable All Macros” and “Trust Access to the VBA Project Object Model”, as indicated in Figure 5.

k2omx0OJfyKhQdhUIRuVaR7VrJwPlyXj1lzvBMFmqVTO4Pt-LOpxkh80ZJHe2kEsNez6ObwEuFxnwPm7j_d4aiRyfAx78HmMxX6vxk-mxY2CnIJBg0iMJeXUunlHMYediw

Figure 5 – Enabling Excel Macro

Step – 3: Open Excel Macro Window

This step can be the most confusing one while working on this project. As a matter of fact, this is the core part of all the procedure. In order to create a Standard Macro, you will have to open the Macro Interface. Simply go to the Developer tab that you enabled in Step 2 and click on Visual Basic button, as indicated in Figure 6.

nSddspPlDXA2q0iLJP-DmLUx3mQ2ghezhMpvesUz-YQpH2bS3i_WLRp-if8Od22MirdrBcQzJwSkj9I76TifX0IhyQPmqE3vWje2f4vXs86SOJ5cieEjSf7ljHUtWF8pfQ

Figure 6 – Opening Visual Basic Macro Editor

Macro interface will open once you press the button. The window will be something like one shown below:

pc7VXkDGOa_Zzjvz0guRE8tHscp_FV1KCe3-aO0ay1YqBH67H2ZkW5l6NgXE5VmbZXnzTBbZLcc-IQWOAzu-GhepACDi2kETmcmhmrnL4DuBX2_WZ_tdlF-o0RmcGxI7mQ

Figure 7 – VBA Macro Interface

Don’t be afraid or overwhelmed with the window as your main concern will be with the highlighted in red section.

Step – 4: Enabling Strict Variable Declaration

In order to avoid unnecessary errors and memory leakage, you will have to enable this option. You can do it by selecting “This Workbook on top right section, and then going to Tools top bar menu and then check the option of Required Variable Declaration, as indicated in Figure 8.

LHY-gcqUzi1px7Cm_PmxLbgcENyJhEvEVzkQz1eH4BxSVh9b1ylBwkU3VI08rHz8Kc3_WedN-MfabnZrrRLAJVomZp74HYJ4deEYijAN6MTe0Od-575h8uuNejVpT466-A

Figure 8 – VB Macro Editor Options

Then:

rz9p9giJFUh6T9IN2ttNLh6PfVOfIov5KZfBbcBEKcf5wZJm4WDEf9Fwcoz2z9tVR-K8jxyY0sXTRyfz5VNu-og6lDFsg5kcVm0LzbEkXcTOTKUNv00Vhs-p5K3VsTEbWg

Figure – 9: Enabling Require Variable Declaration

Select OK and go back to VB Editor.

Step – 5: Inserting the Macro VB Code

Double click on “This Workbook” option to open VB Editor. Go to the end of page (end of anything you see there) and insert this code:

Sub Pass_Variables()

Dim OutApp As Object

Dim OutMail As Object

Dim rng As Range

Dim Ash As Worksheet

Dim Cws As Worksheet

Dim Rcount As Long

Dim Rnum As Long

Dim FilterRange As Range

Dim FieldNum As Integer

On Error GoTo cleanup

Set OutApp = CreateObject(“Outlook.Application”)

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

‘Set filter sheet, you can also use Sheets(“MySheet”)

Set Ash = ActiveSheet

‘Set filter range and filter column (column with e-mail addresses)

Set FilterRange = Ash.Range(“A1:H” & Ash.Rows.Count)

FieldNum = 2 ‘Filter column = B because the filter range start in column A

‘Add a worksheet for the unique list and copy the unique list in A1

Set Cws = Worksheets.Add

FilterRange.Columns(FieldNum).AdvancedFilter _

Action:=xlFilterCopy, _

CopyToRange:=Cws.Range(“A1”), _

CriteriaRange:=””, Unique:=True

‘Count of the unique values + the header cell

Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

‘If there are unique values start the loop

If Rcount >= 2 Then

For Rnum = 2 To Rcount

‘Filter the FilterRange on the FieldNum column

FilterRange.AutoFilter Field:=FieldNum, _

Criteria1:=Cws.Cells(Rnum, 1).Value

‘If the unique value is a mail addres create a mail

If Cws.Cells(Rnum, 1).Value Like “?*@?*.?*” Then

With Ash.AutoFilter.Range

On Error Resume Next

Set rng = .SpecialCells(xlCellTypeVisible)

On Error GoTo 0

End With

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

Dim j As String

j = RangetoHTML(rng)

Dim k As String

k = “<p>” + “<strong>” + “<strong>” + “Best Regards,” + “</strong>” + “</p>” + “<p>” + “<strong>” + “</strong>” + “</p>” + “<p>” + “<strong>” + “ABC Company” + “</strong>” + “</p>”

.to = Cws.Cells(Rnum, 1).Value

.Subject = “Your Promo Code”

.HTMLBody = j + k

.Display ‘Or use Send

End With

On Error GoTo 0

Set OutMail = Nothing

End If

‘Close AutoFilter

Ash.AutoFilterMode = False

Next Rnum

End If

cleanup:

Set OutApp = Nothing

Application.DisplayAlerts = False

Cws.Delete

Application.DisplayAlerts = True

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

End Sub

Function RangetoHTML(rng As Range)

‘ Changed by Ron de Bruin 28-Oct-2006

‘ Working in Office 2000-2013

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook

TempFile = Environ$(“temp”) & “/” & Format(Now, “dd-mm-yy h-mm-ss”) & “.htm”

‘Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With

‘Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

‘Read all data from the htm file into RangetoHTML

Set fso = CreateObject(“Scripting.FileSystemObject”)

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, “align=center x:publishsource=”, _

“align=left x:publishsource=”)

‘Close TempWB

TempWB.Close savechanges:=False

‘Delete the htm file we used in this function

Kill TempFile

Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

Step – 6: Running The Macro

You can press F5 or the Play Button on top bar as indicated in Figure 10.

zLzqFYq6UIJCLT1jVKPqJJTQ_wv5gxGZpPPVi-0KGtYR9-zFqMu64z_r9JJKbWlW66Pvmbp98dyl53eQ_f7gaScW7hZOSMU5KZZccETBpuIwpQNAJn_inSdLuLDxqCM6BQ

Figure 10 – Running VB Macro

The Macro will open three Outlook Draft emails, as there are total of 3 unique emails addresses. One address is repeated and thus the details attached to this one will be sent in single email, as indicated in Figure 11.

WspNO8-65gxuOr2dapyZqe1O3pPbU6ktcFDuFSMBXE2Z-UX-fT4m9WizlUz5lm5agRiAWx_AwRu6DXKd0Ayil-a108VhMEVLxaOxjaHA1ksMurF2Epo6jl72wJJ4d2oKAQ

Figure 11 – Opening Multiple Outlook Drafts

And now you can just click on Send Button instead of copy pasting and inserting unique code manually on every window.

Outlook Limitations

Outlook can accommodate no more than 29 Kb of data in each of “To, CC or BCC”. Thus, if you save your contacts with just name, email and promo code of more or less equal length as mine, you will be able to send somewhere around 120-140 contacts that email. You will have to modify your Excel to send the emails to remaining contacts.