Sending email from Qlikview

[nishantrft]

Hi All,

Could anyone can sent me a macro script for auto email alerts for users to know the Qlikview task is Success or failed.?

[Vegar]

You don't need macro for that. There are email functionality available in QlikView through alerts.

Check out blog post on the topic: https://www.quickintelligence.co.uk/qlikview-alerts/

You also have alert setup available in the QMC.

[stevedark]

Is the reload running from the QMC? If so you can enter SMTP settings and an alert recipient through the QMC settings and emails will send on failure. 

The solution in my blog post can be used in instances when the reload is complete,  but the data is not as expected. 

[nishantrft]

Thanks Vegar,

Your point really works well for Task success , Can you help me to implement if that particular task failed also need an email alert. Is it possible ?

[stevedark]

Do you have your SMTP settings correct in QMC (System / Mail Server) and Alerts (System / QlikView Servers / Alerts)?

[nishantrft]

Yes , We do have configured correct SMTP settings. My scenario is We have 5/6 different application hosted in QMC and different application have respective qvw's and task. In that a particular qlikview task  say Sales.qvw., whenever refresh happened we need a status email like refresh is success or failure.

[stevedark]

The simple approach is that you have QMC notifying of failure and a QlikView alert notifying of success. The biggest drawback of this is that you can not customise the alert from the QMC.

If you want to have something cleaner you will need to parse the QMC log files in order to pull out which tasks have succeeded or failed. I have this as part of an app I have written.

If you want to go this route try looking in C:\ProgramData\QlikTech\DistributionService\TaskResults and enumerate around the files in there. The code I have is a bit messy, as it is written to deal with log files from many different QlikView versions, but if you just build it for your own it should be quite simple.

You will want to do a for each vFile in ('$(vFolder)*.xml') and go from there.

Good luck!

[nishantrft]

Thanks Steve,

Could you share the code so that i can take it as a reference. 

Also i have read that both success and failure alert can be manageable inside a macro.

[stevedark]

The code is part of a wider notifications and logging system, but you should be able to pick out the bits you need. You will see code where it is looking for v11 or previous. If you are on v12 you may need to tweak that.

The problem with macros is that they don't always fire when running from QMC.

let vDistributionFolder = 'C:\ProgramData\QlikTech\DistributionService\';

// Perform checks on the performance of the QMC

// work out what version we are on and pick the appropriate folder
let vQVVersion = mid(QlikViewVersion(), 1, 2);
let vTaskListFolder = if(vQVVersion = '11', 'Task', 'TaskResult');
let vTaskListIDOffset = if(vQVVersion = '11', 5, 11); // offset when doing a mid of the filename

for each vFile in FileList('$(vDistributionFolder)$(vTaskListFolder)s\$(vTaskListFolder)_*.xml')
if vQVVersion = '11' or FileTime('$(vFile)') > today() - 60 then
DistributionTasks:
LOAD
replace(mid('$(vFile)', index('$(vFile)', '$(vTaskListFolder)_') + $(vTaskListIDOffset), 99), '.xml', '') as TaskID
AUTOGENERATE(1)
;
end if
next

let vNoOfTasks = Alt(NoOfRows('DistributionTasks'), 0);

if vNoOfTasks > 0 then
// Count the number of scheduled tasks
NoOfTasks:
LOAD
'Number of tasks scheduled in QMC' as Status,
$(vNoOfTasks) as Value
AUTOGENERATE(1)
;
STORE NoOfTasks INTO $(vStatusFolder)QMC Task Count.txt (txt);
DROP TABLE NoOfTasks;

for iTask = 0 to NoOfRows('DistributionTasks') - 1;
let vTaskID = Peek('TaskID', iTask, 'DistributionTasks');

if Alt(FileSize('$(vDistributionFolder)TaskResults\TaskResult_$(vTaskID).xml'), 0) > 0 then
TaskExecutionStatus:
LOAD
1 as TaskExecutionStatusCount,
TaskID,
OwnedByClusterID,
RunID,
TaskManuallyAborted,
PreviousRunHadErrors,
InternalError,
LastExecution,
StartedAt,
FinishedAt,
MaxRunTime,
StartCount,
HasWarnings,
Status,
Modified,
[LogFinalEntries/LogFinalEntry/Type] as ResultType,
[LogFinalEntries/LogFinalEntry/Date] as ResultDate,
[LogFinalEntries/LogFinalEntry/Text] as ResultText
FROM [$(vDistributionFolder)TaskResults\TaskResult_$(vTaskID).xml] (XmlSimple, Table is [Root]);
end if
next

if Alt(NoOfRows('TaskExecutionStatus'), 0) > 0 then
STORE TaskExecutionStatus INTO $(vDataFolder)TaskExecutionStatus.qvd (qvd);

// Count the number of tasks presently in a failed state
TaskErrorCount:
LOAD
'Number of QMC tasks in a failed state' as Status,
sum(if(ResultType = 'Error', 1, 0)) as Value
RESIDENT TaskExecutionStatus
;

STORE TaskErrorCount INTO $(vStatusFolder)QMC Task Error Count.txt (txt);
DROP TABLE TaskErrorCount;

DROP TABLE TaskExecutionStatus;
end if

for each vFile in FileList('$(vDistributionFolder)1\Log\Root_*.txt')
TaskExecutionLog:
LOAD
1 as TaskExecutionCount,
Date(Date#(@1, 'MM/DD/YYYY hh:mm:ss.ffffff'), '$(vDateFormat) hh:mm:ss') as TaskEecutionDateTime,
@2 as TaskExecutionStatus,
@3 as TaskExecutionMessage
FROM $(vFile)
(txt, utf8, no labels, delimiter is '\t', msq);
next

if Alt(NoOfRows('TaskExecutionLog'), 0) > 0 then
// Count the number of task executions today
TaskExecutionsToday:
LOAD
'Number of QMC tasks executed today' as Status,
COUNT(TaskEecutionDateTime) as Value
RESIDENT TaskExecutionLog
WHERE mid(TaskExecutionMessage, 1, 😎 = 'The task'
AND DayStart(TaskEecutionDateTime) = today()
;
STORE TaskExecutionsToday INTO $(vStatusFolder)QMC Tasks Executed Today.txt (txt);

DROP TABLE TaskExecutionsToday;

// Count the number of task failures today
TaskFailuresToday:
LOAD
'Number of QMC tasks failed today' as Status,
COUNT(TaskEecutionDateTime) as Value
RESIDENT TaskExecutionLog
WHERE mid(TaskExecutionMessage, 1, 😎 = 'The task'
AND TaskExecutionStatus = 'Error'
AND DayStart(TaskEecutionDateTime) = today()
;
STORE TaskFailuresToday INTO $(vStatusFolder)QMC Tasks Failed Today.txt (txt);

DROP TABLE TaskFailuresToday;

// Count the number of task executions this week
TaskExecutionsThisWeek:
LOAD
'Number of QMC tasks executed this week' as Status,
COUNT(TaskEecutionDateTime) as Value
RESIDENT TaskExecutionLog
WHERE mid(TaskExecutionMessage, 1, 😎 = 'The task'
AND WeekStart(TaskEecutionDateTime) = WeekStart(today())
;
STORE TaskExecutionsThisWeek INTO $(vStatusFolder)QMC Tasks Executed This Week.txt (txt);

DROP TABLE TaskExecutionsThisWeek;

// Count the number of task failures this week
TaskFailuresThisWeek:
LOAD
'Number of QMC tasks failed this week' as Status,
COUNT(TaskEecutionDateTime) as Value
RESIDENT TaskExecutionLog
WHERE mid(TaskExecutionMessage, 1, 😎 = 'The task'
AND TaskExecutionStatus = 'Error'
AND WeekStart(TaskEecutionDateTime) = WeekStart(today())
;
STORE TaskFailuresThisWeek INTO $(vStatusFolder)QMC Tasks Failed This Week.txt (txt);

DROP TABLE TaskFailuresThisWeek;
end if

STORE TaskExecutionLog INTO $(vDataFolder)TaskExecutionLog.qvd (qvd);

DROP TABLE TaskExecutionLog;

DROP TABLE DistributionTasks;
end if

let vFile =;
let vNoOfTasks =;
let iTask =;
let vTaskID =;
let vTaskListFolder =;
let vTaskListIDOffset =;

If you are interested, there is a screenshot of the app that this feeds into in this blog post:

https://www.quickintelligence.co.uk/keep-qlikview-simple/