Sending Email from IBM Cognos TM1 using Windows Powershell

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);