It can be quite useful to know when a Qlik application has failed in order to correct the issue and get your business users back up and running. While QlikView has a solid task failure notification feature, Qlik Sense currently falls short. Because there is no default facility by which to issue notifications for task failures in Qlik Sense (https://community.qlik.com/thread/157245), a third party solution must be employed.
This solution is composed of three parts:
- Qlik Sense Application
A Qlik Sense application is deployed against the Qlik Sense Scheduler Log files and the errors and failures are filtered for. An error/failure output file is generated.
- Email Generator
A command line tool called ‘blat’ is used to write a generic email with the output file from above as an attachment.
- Windows Task Scheduler
The windows task scheduler is employed to schedule the command line file, which generates the email, to run at regular intervals. Any scheduler of this type can work.
_____________________________________________
Implementation Instructions:
_____________________________________________
1) Qlik Sense Application
Write and schedule the Qlik Sense Application.
Sample Qlik Sense App Script:
//SET OUTPUT FILE LOCATIONS.
//NOTE: Data Source Connections Must be setup in order to set the variable below.
//============================================================
//This is a new output. The location of this file will also host the smpt mailer.
SET vL.Output_TextFile = [lib://ServerLogFolder/Scheduler\Trace\__TASK_ERROR.txt];
SET vL.CheckWindowInMin = 5;
//============================================================
//Nothing needs to be altered below.
//============================================================
OUTPUT:
Load
StartTime as LogTime,
AppName,
TaskName
& ‘ for App ‘ & AppName & ‘ reported status = ‘&
Status as Message
Where
Level=’ERROR’
OR
WILDMATCH(Status,’*fail*’)
;
LOAD
AppName,
TaskName,
Level,
Status,
TIMESTAMP(TIMESTAMP#(
REPLACE(LEFT(StartTime,13),’T’,’ ‘),’YYYYMMDD hhmm’),
‘MM-DD-YY @ hh:mm’)
as StartTime,
TIMESTAMP(TIMESTAMP#(
REPLACE(LEFT(StopTime,13),’T’,’ ‘),’YYYYMMDD hhmm’),
‘MM-DD-YY @ hh:mm’)
as StopTime
FROM [lib://ServerLogFolder/Scheduler\Trace\*_TaskExecution_Scheduler.txt] (txt, utf8, embedded labels, delimiter is ‘\t’, msq);
concatenate
LOAD
LogTime,
AppName,
Message
Where WILDMATCH(Description,’*fail*’)
;
Load
TIMESTAMP(TIMESTAMP#(
REPLACE(LEFT(“Timestamp”,13),’T’,’ ‘),’YYYYMMDD hhmm’),
‘MM-DD-YY @ hh:mm’)
as LogTime,
Replace(ObjectName,’|’,’ for App ‘) &
‘ reported status = ‘& Subfield(Description,’=’,-1) as Message,
SUBFIELD(ObjectName,’|’,-1) as AppName,
Description
FROM [lib://ServerLogFolder/Scheduler\Audit\*_AuditActivity_Scheduler.txt]
(txt, utf8, embedded labels, delimiter is ‘\t’, msq);
rename table OUTPUT to tmpOutput;
//Check last updated time of log file.
let vL.LogTimeGreaterThan = now()-($(vL.CheckWindowInMin)/1440);
noconcatenate
OUTPUT:
Load * Resident tmpOutput
where LogTime > $(vL.LogTimeGreaterThan) Order By LogTime;
drop table tmpOutput;
IF NoOfRows(‘OUTPUT’)>0 THEN
TRACE ============= Store Output;
STORE OUTPUT INTO $(vL.Output_TextFile) (txt);
ENDIF;
2) Email Generator
Download the ‘blat’ email utility from https://sourceforge.net/projects/blat/.
Installation can be as simple as copying the extracted BLAT files (blat.dll, blat.exe, blat.lib, blatdll.h) into the C:\Windows\system32 folder.
Paste the following code into a batch file, replacing the EMAILSERVER, USEREMAIL and USEREMAILPW with the appropriate values. The batch file must be located in the same folder as the output of the Qlik Sense application.
cd %myvar%
blat -to %1% -subject QLIKSENSE_TASK_ERROR -body see_attached -server EMAILSERVER -f USEREMAIL -u USEREMAIL -pw USEREMAILPW -attach __TASK_ERROR.txt
del /F %myvar%__TASK_ERROR.txt
3) Windows Task Scheduler
Schedule the batch file from Step 2 with Windows Task Scheduler to run at an appropriate interval.
Note that the batch file accepts a parameter for the target email address and can be added into the scheduler properties.
Within the task properties, be sure to check ‘Run with highest privileges’ and ‘Run whether user is logged in or not’.
Some IT Departments may require that this be a service account login setup by a network administrator.
NOTES:
Interval Timing
There are three places the interval for error checking must be set. (1) In the Qlik Sense App Script – see the vL.CheckWindowInMin variable. (2) In the QMC Task Scheduler (3) In Windows Task Scheduler. These intervals should be set equal to each other for most accurate results.
Security
The batch file can be made into a compiled application (exe file) with one of several common utilities. Ex. http://download.cnet.com/Bat-To-Exe-Converter-Portable/3000-2069_4-10555897.html
This can be useful in making the required email credentials more secure.
BLAT can also be configured to use the registry to store common parameters such as the email server, user name and password. The batch file can also be modified to accept these parameters at runtime.
Please consult your IT Department to ensure that you’ve considered all pertinent security policies.
