Cognos Transformer Cube failure Alert Mechanism

Recently I had a requirement to create a automatic alert mechanism on cube build failure.
As we all know this can be a project by it self if we set it up in a traditional way of write cube logs to tables and creating reports on top of the tables and setting up events to send out alerts on any failure.
But with the help of a network admin I was able to setup alerts and it took some basic Vb coding too..
Below is the workflow of how the setup works and the basic requirements(this is for windows environment only):
There are two VB scripts we need to setup and schedule via windows scheduler.
First script writes the name of the Transformer log file into a .txt file. In my case the log file had a date that had to queried everyday before writing the log file name.


' Declare Variables for Oracle SQL
  Dim OdbcDSN
  Dim connect, connectionstr, sql, resultset
  
  Dim fso
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
'**********************************************************************************************
' Open the Oracle SQL Connection
  
  connectionstr="DSN=emtdb;UID=------;PWD=------"
  'wScript.Echo (connectionstr)
  
  Set connect = CreateObject("ADODB.Connection")
  
  connect.Open connectionstr
  
  sql = "select * from abcd.adcde "
  Set resultSet = connect.Execute(sql)
  
'***********************************************************************************************
 ' Above Oracle connection replacing the date suffix for the .mdc names 
      
Dim strYear, strMonth, strDay, strHour, strMinute, strSecond, Date_time


strYear = CStr(Year(Now()))


if len(CStr(Month(Now()))) <2 then 
strMonth ="0" & CStr(Month(Now())) 
else strMonth =CStr(Month(Now())) 
end if


if len(CStr(Day(Now()))) <2 then 
strDay = "0" & CStr(Day(Now())) 
else strDay = CStr(Day(Now()))
end if


if len(CStr(Hour(Now()))) <2 then 
strHour = "0" & CStr(Hour(Now())) 
else strHour = CStr(Hour(Now())) 
end if


if len(CStr(Minute(Now()))) <2 then
strMinute = "0" & CStr(Minute(Now())) 
else strMinute = CStr(Minute(Now()))
end if


if len(CStr(Second(Now()))) <2 then
strSecond = "0" & CStr(Second(Now()))
else strSecond = CStr(Second(Now())) 
end if


''''Date_time=strYear & strMonth & strDay & strHour & strMinute
Date_Time=ResultSet("cob_date")
'********************************************************************************
Log_File="TEXT_FILE_NAME.txt"
XFR_Log_File="TRANSORMER_LOG_FILE_BEGIN" & Date_Time &"LOG_FILE_END.log"


' Initialize Log File
'InitLogFile:
'wscript.echo Log_File


set w = fso.CreateTextFile(Log_File,True)
    w.WriteLine ( XFR_Log_file )

' Close the Oracle SQL connection
set ResultSet = Nothing


connect.Close
set Connect = Nothing

Now that we have the name of the transformer log file we want to search for any errors or failure in a .txt file.


@@echo off
set ERRORSTR=TR1901 (Replace with what error you want to search on)
set SRCDIR=TRANSFORMER_LOG_FILES_LOCATION
set /P LOGFILE=<TEXT_FILE_THAT_HAS_LOG_FILE_NAME_LOCATION\FILE_NAME.txt
echo Looking for logfile: %SRCDIR%\%LOGFILE%


set RCPT=abc@abc.com(Recipient email)
set MESSAGE= Cube Failed with ---- Error, Please login and fix.....


find "%ERRORSTR%" %SRCDIR%\%LOGFILE%
if %ERRORLEVEL%==1 GOTO :NO


sendmail /smtpserver email.company.com /to %RCPT% /from xyz@xyz.com /subject Error: %ERRORSTR% Found in %LOGFILE% /body %MESSAGE%


:NO
exit



For the email option to work you need to have a 3rd party free app called SendMail.exe
Now that we have the scripts that does the job our last step is to schedule them, I created .cmd file out of the 1st vb script and scheduled at 5:00Am and created a .bat file out of 2nd script and schedules it at 4:55Am so we have the name of the transformer file before 5:00AM job searches for any errors in current log file. Hope this is helpful.

Comments

Popular posts from this blog

Query Cognos 8/10 Content Store

Cognos 8/10 CAMID-User ID/LDAP ID report

How about an Online help forum?