Sending Email from IBM Cognos TM1 using Windows Powershell - Clear Insight

Once you start building a TM1 solution, you’ll quickly notice a lack of email components or tools… That’s because there aren’t any out of the box. Don’t sweat it though. There are several good sites that describe how to use a VBscript file to send out an email. VBscript is fine and works well, but it’s a very old technology. I think the replacement for VBscript is Microsoft Windows Powershell. It’s already installed on your Windows server. I just needs a few commands to allow it to start accepting script requests. Below are some instructions on how to send email from TM1 using Powershell.

Why Reinvent the Wheel?

So, if there are already good scripts using VBscript that send email, why would I try to reinvent the wheel by doing the same thing a different way? Powershell can do much more than just send email. The script below doesn’t just send email. This script does the following

Attaches the error log files and the .cfg file
Reads the most recent error file and puts the contents of the file directly into the body of the email
Shows some basic server environment variables
Most recent lines of the TM1server.log file
Creates hyperlinks to the log folder, cfg folder and data folder

I encourage everyone to start with something like this, but extend it, to build the most meaningful email feedback tool for TM1 we can. Please comment here or tweet updates to @bobgillvan.

Steps to Setup TM1 Email Using Powershell

1) Turn on Powershell

Start..All Programs…Accessories..Windows Powershell…Windows Powershell (ISE)

set-executionpolicy Unrestricted

F5 (This just runs the code in the code window)

2) Customize and Test the Script

Here is the script. Just save this to a .ps1 file and then you can run it with powershell. I recommend calling this email.ps1 and then you can run Powershell ./email.ps1. You’ll also need to enter your SMTP server information and any secure port references.

function sendMail{

Write-Host “Sending Email”

$pathLog = “C:\TM1Logs”

$pathCfg = “C:\TM1Data”

$pathTemp = “C:\Temp”

#Wait to make sure all files from the TI process are written to disk. Sometimes there is a delay

$i=0

$filesWritten = $False

do {

$filesWritten = test-path C:\TM1Logs\tempemailfile*

$i = $i+1

}

while ((-not $filesWritten) -and ($i -lt 10000))

$fileToEmail = Get-Content C:\temp\TempEmailFileToEmail.txt

$fileToName = Get-Content C:\temp\TempEmailFileToName.txt

$fileFromEmail = Get-Content C:\temp\TempEmailFileFromEmail.txt

$fileFromName = Get-Content C:\temp\TempEmailFileFromName.txt

$fileToEmail = Get-Content C:\temp\TempEmailFileToEmail.txt

$fileToName = Get-Content C:\temp\TempEmailFileToName.txt

$fileSubject = Get-Content C:\temp\TempEmailFileSubject.txt

$fileBody = Get-Content C:\temp\TempEmailFileBody.txt

$fileLinkApplicationLogin = Get-Content C:\temp\TempEmailFileLinkApplicationLogin.txt

$fileLinkDataFolder = Get-Content C:\temp\TempEmailFileLinkDataFolder.txt

$fileLinkErrorFolder = Get-Content C:\temp\TempEmailFileLinkErrorFiles.txt

# Remove doublequotes

$fileToEmail = $fileToEmail.Replace(“”””,””)

$fileToName = $fileToName.Replace(“”””,””)

$fileFromEmail = $fileFromEmail.Replace(“”””,””)

$fileFromName = $fileFromName.Replace(“”””,””)

$fileToEmail = $fileToEmail.Replace(“”””,””)

$fileToName = $fileToName.Replace(“”””,””)

$fileSubject = $fileSubject.Replace(“”””,””)

$fileBody = $fileBody.Replace(“”””,””)

$fileLinkApplicationLogin = $fileLinkApplicationLogin.Replace(“”””,””)

$fileLinkDataFolder = $fileLinkDataFolder.Replace(“”””,””)

$fileLinkErrorFolder = $fileLinkErrorFolder.Replace(“”””,””)

# $fileToEmail

# $fileToName

# $fileFromEmail

# $fileFromName

# $fileToEmail

# $fileToName

# $fileSubject

# $fileBody

# $fileLinkApplicationLogin

# $fileLinkDataFolder

# $fileLinkErrorFolder

#SMTP server name

$smtpServer = “smtp.gmail.com”

#Creating a Mail object

$msg = new-object Net.Mail.MailMessage

#Creating SMTP server object

$smtp = new-object Net.Mail.SmtpClient(“smtp.gmail.com”,587)

# $smtp = new-object Net.Mail.SmtpClient(“smtp.secureserver.net”,465)

$smtp.EnableSsl = $true

$smtp.Credentials = New-Object System.Net.NetworkCredential(“gmailuser@gmail.com”, “securegmailapppassword”);

# $smtp.Credentials = New-Object System.Net.NetworkCredential(“username”, “password”);

#Email structure

$msg.From = “TM1Administrator@somewhere.org”

$msg.ReplyTo = “TM1Administrator@somewhere.org”

$msg.To.Add($fileToEmail)

# $msg.To.Add(“user@somewhere.org”)

$msg.subject = $fileSubject

#Find most recent error file

# Get-ChildItem $pathLog | where {$_.Name -like “TM1ProcessError*.log”} | sort LastWriteTime | select -last 1 | Write-Host

$fileLastErrorText = Get-ChildItem $pathLog | where {$_.Name -like “TM1ProcessError*.log”} | sort LastWriteTime | select -last 1 | Get-Content

#Attach Error Files

Get-ChildItem $pathLog| % { if ($_.Name -like “TM1ProcessError*.log”) {$msg.Attachments.Add($_.FullName)} }

# Get-ChildItem $pathLog| % { if ($_.Name -like “TM1ProcessError*.log”) {Write-Host $_.FullName} }

#Attach TM1Server Log

Get-ChildItem $pathLog | % { if ($_.Name -like “TM1server.log”) {$msg.Attachments.Add($_.FullName)} }

# Get-ChildItem $pathLog | % { if ($_.Name -like “TM1server.log” -and $_Size -lt 1) {Write-Host $_.FullName} }

#Attach tm1s.cfg

Get-ChildItem $pathCfg | % { if ($_.Name -like “TM1s.cfg”) {$msg.Attachments.Add($_.FullName)} }

#Show system links

$msg.body = $fileBody + “`n`n” + “System links:`n`tLogin`t`t” + $fileLinkApplicationLogin + “`n”

$msg.body = $msg.body + “`tError Folder`t” + $fileLinkErrorFolder + “`n”

$msg.body = $msg.body + “`tData Folder`t” + $fileLinkDataFolder + “`n”

#Show details of last error

if ($fileLastErrorText -ne “”) {

$msg.body = $msg.body + “`n`n” + “Contents of most recent error file:`n” + $fileLastErrorText

}

#Show the most recent lines of the tm1server.log file

$fileTM1serverLogFile = Get-Content ($pathLog + “\tm1server.log”)

$y=0

foreach( $x in $fileTM1ServerLogFile.length..1) {

$y += 1

if ($y -lt 20){

$fileTM1ServerLog += “`t” + $fileTM1ServerLogFile[$x] + “`n”

}

$msg.body = $msg.body + “`n`n” + “Most recent lines of the TM1Server.log file:” + $fileTM1serverLog

#Capture Server Variables

$msg.body = $msg.body + “`n`nServer Information:`n”

$msg.body = $msg.body + “`tServer Name:`t`t`t” + $env:COMPUTERNAME + “`n”

$msg.body = $msg.body + “`tDNS:`t`t`t`t” + [System.Net.DNS]::GetHostName() + “`n”

$systemMemoryFree = “{0:N0}” -f (get-counter -counter “\Memory\Available MBytes”).CounterSamples[0].CookedValue

$msg.body = $msg.body + “`tFree Memory:`t`t`t” + $systemMemoryFree + ” MB`n”

# $msg.body

#Save a text file with the body for debugging

# $msg.body | out-file c:\temp\TempEmailFile_FinishedEmail.txt

#Sending email

$smtp.Send($msg)

#Clear temp files

remove-item c:\temp\TempEmailFile* -recurse

}

#Calling function

sendMail

3) Call it from a TI

This part isn’t very exciting. This script accepts command line variables, so all we need to do is generate a command line string and then send it to ExecuteCommand(). Here is code for the prologue tab that gives you a structure to work with.

vsLinkErrorFiles = ‘\\server\e$\tm1logs\cxmd’;
vsLinkDataFolder = ‘\\server\F$\tm1data’;
vsLinkApplicationLogin = ‘http://server/tm1web/TM1WebLogin.aspx’;

# Get the To for this environment, if necessary
If (psTo @=”);
psTo = ‘user@somewhere.org’;
EndIf;

# Get the CC for this environment, if necessary
If (psCC @=”);
psCC = ‘user@somewhere.org’;
EndIf;

# Get the From for this environment, if necessary
If (psFrom @=”);
psFrom = ‘admin@somewhere.org’;
EndIf;

# Write the contents of the email to a file for use by the script

vsTempFile = ‘f:\Temp\TempEmailFile’;

#Subject
ASCIIOUTPUT(vsTempFile | ‘Subject.txt’, psSubject);

#ToEmail
ASCIIOUTPUT(vsTempFile | ‘ToEmail.txt’, psTo);

#ToName
ASCIIOUTPUT(vsTempFile | ‘ToName.txt’, ‘Users’);

#FromEmail
ASCIIOUTPUT(vsTempFile | ‘FromEmail.txt’, psFrom);

#FromName
ASCIIOUTPUT(vsTempFile | ‘FromName.txt’, ‘TM1 Administrator’);

#Body
ASCIIOUTPUT(vsTempFile | ‘Body.txt’, psBody);

#ErrorFiles
ASCIIOUTPUT(vsTempFile | ‘LinkErrorFiles.txt’, vsLinkErrorFiles);

#DataFolder
ASCIIOUTPUT(vsTempFile | ‘LinkDataFolder.txt’, vsLinkDataFolder);

#LinkApplicationLogin
ASCIIOUTPUT(vsTempFile | ‘LinkApplicationLogin.txt’, vsLinkApplicationLogin);

vCommand = ‘powershell.exe f:\tm1data\cxmd\email.ps1 ‘;

ASCIIOUTPUT (‘f:\temp\email_asciiOutput.txt’, ‘Send Email is running command ‘ | vCommand);
ExecuteCommand( vCommand,0);

Share This Story, Choose Your Platform!