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.
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
Post a Comment