TM1 Forum

Posted: **Tue Nov 06, 2012 12:11 pm**

Hi

I have written a TI process for enable TM1 to send confirmation emails after executing a chore if there is any error log found. I am using VBS script (down loaded) and made some additional changes. And calling this VBS inside the TI.

I don't know where I made mistake but it is not working as expected. Sending the notification to the admins but not attaching the multiple error files.

I think i am making mistake in the time stamp line but not sure. Any help. Thanks in advance.

Thank
kkmk

Prolog:

#****Begin: Generated Statements***
#****End: Generated Statements****

# set empty string as quote char
DatasourceASCIIQuoteCharacter = '';

# set filename
sFileName = 'C:\send.txt';

# header
sHtml = '<html><head><style>.footer {font-size:11px;}; body {font-family: Calibri;font-size:11px;}</style></head><body>';
AsciiOutput ( sFileName, sHtml );

sHtml = '<table width="100%" cellspacing="0" style="border:1px solid #000000;font-size:11px;">';
AsciiOutput ( sFileName, sHtml );

sHtml = '<tr><td valign="middle" style="background-color:#999999;color:#FFFFFF;padding-left:5px;padding-right:5px;width:120px;"><strong>Notification
</stron
g></td>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</tr>';
AsciiOutput ( sFileName, sHtml );

sColor = '#EEEEEE';

sHtml = '<tr style="background-color:' | sColor | ';"><td valign="middle" style="vertical-align:top;padding-left:5px;padding-right:5px;">' | pBody | '
</td>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</tr>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</table>';
AsciiOutput ( sFileName, sHtml );

sHtml = '<br/><div class="footer" align="left">Notifications generated from TM1, provided by <br/><img src="http://nnorsovmas082/site_logo.png" width=
"80"/></div>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</body></html>';
AsciiOutput ( sFileName, sHtml );

#**************************************************************************************************************************************

#Setup Variables

Lastfile = '';
alllogs = '';

# Find todays date

s_TimeStamp = TimSt (Now, '\Y\m\d');

# Search the logs dir for last file

logfile = WildcardFileSearch( 'C:\Logs\TM1ProcessError_'|s_TimeStamp|' *.log', '');

ASCIIOUTPUT('c:\debug.txt' , logfile);

WHILE ( logfile @<> '' );

logname = 'C:\Logs\' | logfile;
alllogs = logname | ' ; ' | alllogs;
Lastfile = logfile;
logfile = WildcardFileSearch( 'C:\Logs\TM1ProcessError_'|s_TimeStamp|'*.log', '');

END;

# set filename
pAttachFile = alllogs;

EPILOG

#****Begin: Generated Statements***
#****End: Generated Statements****

sSender = 'sample@domain.com';
sSMTPSvr = 'exchgdomain.com';

IF( pAttachFile @<> '' );
arguments = 'CScript C:\sendMailOther.vbs '|sSMTPSvr |' ' | sSender | ' "'|pRecipient|'" "'|pSubject|'" "'
| sFileName | '" "' | alllogs | '"';
ELSE;
arguments = 'CScript C:\sendMailOther.vbs '|sSMTPSvr |' ' | sSender | ' "'|pRecipient|'" "'|pSubject|'" "'
| sFileName | '"' ;
ENDIF;

EXECUTECOMMAND(arguments ,1);
ASCIIOUTPUT('debug.csv',arguments);

SendMailOther.VBS

Dim SMTPServer
Dim SMTPPort
Dim Mail_from
Dim Mail_to
Dim Subject
Dim Body
Dim Attach

SMTPServer = WScript.Arguments(0)
Mail_from = WScript.Arguments(1)
Mail_to = WScript.Arguments(2)
Subject = WScript.Arguments(3)
Body= WScript.Arguments(4)
if WScript.Arguments.Count = 6 then
Attach= WScript.Arguments(5)
end if

set imsg = createobject("cdo.message")
set iconf = createobject("cdo.configuration")

Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/config ... /sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/config ... smtpserver") = SMTPServer
.Update
End With

With iMsg
Set .Configuration = iConf
.To = Mail_to
.From = Mail_from
.Subject = Subject
.HTMLBody = readFile(Body)
if Attach <> "" then
a= Split(Attach, ";")
for each x in a
.AddAttachment x
next
end if
.fields.update
.Send
End With

set imsg = nothing
set iconf = nothing

function readFile(sPath)
const forReading = 1
dim objFSO, objFile, sData
set objFSO = createobject("Scripting.FileSystemObject")
set objFile = objFSO.openTextFile(sPath, ForReading)
sData = ""
do until objFile.atEndOfStream
sData = sData & objFile.readLine & vbCrLf
loop
objFile.close
set objFile = nothing
set objFSO = nothing
readFile = sData
end function

Posted: **Tue Nov 06, 2012 7:37 pm**

It's very hard to determine what a batch or script file is doing from TI.

To debug, try spitting out the command that you're executing to an error log (ItemReject(arguments)), then copy and paste that into the command line to see if it works as you expect.

Also I note you're using a C:\ reference, I presume you're aware that this will be looking on the server's C:\, not your own?

Martin

Posted: **Tue Nov 06, 2012 10:30 pm**

That script will not handle multiple attachements

By the way a great use of this is in the StartupChores=<chorename> Parameter in the later versions of TM1 to let you know tm1 is loaded

try this vba:

Code: Select all

im SMTPServer
Dim SMTPPort
Dim Mail_from
Dim Mail_to
Dim Subject
Dim Body
Dim Attach

SMTPServer = WScript.Arguments(0)
Mail_from = WScript.Arguments(1)
Mail_to = WScript.Arguments(2)
Subject = WScript.Arguments(3)
Body= WScript.Arguments(4)
' JW20121002 Added check for multiple attachements
if WScript.Arguments.Count = 6 then
  Attach= WScript.Arguments(5)
end if

set imsg = createobject("cdo.message")
set iconf = createobject("cdo.configuration")

Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
.Update
End With

With iMsg
    Set .Configuration = iConf
        .To = Mail_to
        .From = Mail_from
        .Subject = Subject
        .HTMLBody = readFile(Body)
          if Attach <> "" then
				a= Split(Attach, ";")
						for each x in a
							.AddAttachment x
							next	
				
        end if
        .fields.update
        .Send
End With

set imsg = nothing
set iconf = nothing


function readFile(sPath) 
    const forReading = 1 
    dim objFSO, objFile, sData 
    set objFSO = createobject("Scripting.FileSystemObject") 
    set objFile = objFSO.openTextFile(sPath, ForReading) 
    sData = "" 
    do until objFile.atEndOfStream 
        sData = sData & objFile.readLine & vbCrLf 
    loop 
    objFile.close 
    set objFile = nothing 
    set objFSO = nothing 
    readFile = sData 
end function

here is the pro
chnage <location> to you logs location

Code: Select all

## This process is designed to be tacked onto the end of the overnight jobs to email errors so we can investigate


### Setup variables 
Lastfile = '';
alllogs = '';
s_TimeStamp = TimSt (Now - 1, '\Y\m\d');
# Find todays date (yesterdays)
#s_TimeStamp = TimSt (Now - 1, '\Y\m\d');


# Search the logs dir for last file
logfile = WildcardFileSearch( 'C:\<location>\TM1ProcessError_'|s_TimeStamp|'*.log', lastfile);

WHILE ( logfile @<> '' );
logname = 'C:\<location>\'|logfile;
alllogs =  logname | ';' | alllogs;
Lastfile = logfile;
logfile = WildcardFileSearch( 'C:\<location>\TM1ProcessError_'|s_TimeStamp|'*.log', lastfile);
END;

  # set empty string as quote char
  DatasourceASCIIQuoteCharacter = '';

  # set filename
pAttachFile =  alllogs;

# Could Debug to a file using this 
ASCIIOUTPUT('c:\<location>\Debug\XEmailErrorLogs_asci.txt',   alllogs);
  sFileName = 'C:\sent.txt';

  # header
  sHtml = '<html><head><style>.footer {font-size:11px;}; body {font-family: Calibri;font-size:11px;}</style></head><body>';
  AsciiOutput ( sFileName, sHtml );


  sHtml = '<table width="100%" cellspacing="0" style="border:1px solid #000000;font-size:11px;">';
  AsciiOutput ( sFileName, sHtml );

  sHtml = '<tr><td valign="middle" style="background-color:#006400;color:#FFFFFF;padding-left:5px;padding-right:5px;width:120px;"><strong>Error Logs
</strong></td>';
  AsciiOutput ( sFileName, sHtml );

  sHtml = '</tr>';
  AsciiOutput ( sFileName, sHtml );

sColor = '#90ee90';

sHtml = '<tr style="background-color:' | sColor | ';"><td valign="middle" style="vertical-align:top;padding-left:5px;padding-right:5px;">' | pBody | '
</td>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</tr>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</table>';
AsciiOutput ( sFileName, sHtml );

sHtml = '<br/><div class="footer" align="left">Notifications generated by \Custom\<location> and XEmailErrorLogs Process<br/></div>';
AsciiOutput ( sFileName, sHtml );

sHtml = '</body></html>';
AsciiOutput ( sFileName, sHtml );

Epilog

Code: Select all

#Get Servername
sServername = CellGetS(<cube location>);

sSender = sServername | '@<server>';
sSMTPSvr = '<smtp server>';

#Only Email if there are log files
IF( pAttachFile @<> '' );
arguments = 'CScript "C:\<location>\sendMailOther.vbs" '|sSMTPSvr |' ' | sSender | ' "'|pRecipient|'" "'| sServername |pSubject|'" 
"'| sFileName | '" "' | alllogs |  '"';
EXECUTECOMMAND(arguments ,1);
ENDIF;


#For Debug
ASCIIOUTPUT('C:\<location>\Debug\xEmailErrorLogs.csv',arguments);

Posted: **Wed Nov 07, 2012 3:12 am**

Thank you Martin and James, let me try your suggestions.

Thanks
kkmk

Posted: **Wed Nov 07, 2012 10:01 am**

Hi James,

Could you please tell me what is the value for sServername variable. ?

Thanks
kkmk

Posted: **Wed Nov 07, 2012 10:44 am**

Flow has a product called Flow Responder, which allows you to send emails from TI in a much simpler and more robust way. If you want to try it out, let me know (via PM or team@flowolap.com) and I'll hook you up.

Posted: **Wed Nov 07, 2012 7:55 pm**

I have created a sting attribute in my assumptions cube for my tm1 server name
I can then look this up so that the same code works in dev and prod as log as this variable is updated.

Code: Select all

#Get Servername
sServername = CellGetS('Assumptions','No version','No Year','Full Year','WindowsServerName');

Hope that helps

Posted: **Thu Nov 08, 2012 9:49 am**

Thank you James. Everything working fine.

Thanks
kkmk

Posted: **Thu Nov 08, 2012 9:16 pm**

Great, glad you got it sorted

Posted: **Thu Nov 15, 2012 9:30 am**

Hi,

I have scheduled a chore for email notification after all the other chores executed. It is working fine. Once again thanks to James.
But there is an another requirement that I have to schedule it after each chore executed. Could you help me with the steps to do this?

Thanks in advance.
KKMK

Posted: **Sat Nov 17, 2012 8:52 am**

Kkmk,
Check out http://www.tm1forum.com/viewtopic.php?f ... 7627#p7627
Also http://www.bihints.com/send_email_attachments
and http://www.tm1forum.com/viewtopic.php?f=3&t=6205

Posted: **Wed Apr 17, 2013 3:27 pm**

Hi,

The email notification process written in TI is no longer working, because we are no longer using exchange server since we’ve moved to Office 365, and using cloud servers for send and receive emails.

Is there any other alternate to re-enable our email notification from cloud servers?

Thanks in advance,
kkmk

Posted: **Wed Apr 17, 2013 3:29 pm**

Did you try searching on google for public SMTP servers that you can use? I know in the past we have used a Microsoft one.

Posted: **Wed Apr 17, 2013 8:26 pm**

Looks like you will need to setup a smtp relay
http://support.microsoft.com/kb/2600912

or modify the vb code to use the TLS Encryption method
http://www.configureoffice365.com/confi ... d-devices/
However it doesn't look like the schema this is using (http://schemas.microsoft.com/cdo/configuration) supports TLS encrpytion, it does support SSL
http://msdn.microsoft.com/en-us/library ... g.80).aspx

Posted: **Mon Nov 25, 2013 7:16 pm**

Has anyone change this code to be Outlook 2010 compliant now that CDO is unsupported ?

Posted: **Thu Dec 12, 2013 3:06 pm**

I set up the TM1 Process, Chore and vba script but nothing worked and I wasn't getting an error so II just assumed that this wasn't going to work until I dug a bit deeper.

This is still supported and works with Outlook 2010 as cdosys is separate from Outlook.

Ensure that cdosys.dll is registered on your server and also that Default SMTP Virtual Server is setup in IIS v6 even if you are running IIS7.

See the attached technote for setting up Default SMTP Virtual Server

http://stackoverflow.com/questions/3165 ... for-abcxyz

TM1 Forum

EMail Notification

EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification

Re: EMail Notification