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/
