Ariel,
Try this script. It will give you all the executions found in the folder of the log files. Then you'll get the data you need and can build charts in the QlikView-application to compare the last with averages.
Possibly you could also install Goverance Dashboard, and look into the qvd(s) / qvx(s) it is producing and build your own application on this.
Good luck!
Johan
IF Len('$(vFolderDistributionService)') = 0 then
LET vFolderDistributionService = 'C:\ProgramData\QlikTech\DistributionService';
ENDIF
SUB DoDirTasks (Root)
for each File in filelist ('$(Root)' & '\*.xml')
LET TaskId = Replace(Replace(SubField('$(File)', '\', SubStringCount('$(File)', '\') + 1), 'Task_', ''), '.xml', '');
IF not IsNull(TableNumber('ExistingTasks')) Then
isFileAnExistingTask:
NoConcatenate
LOAD *
Resident ExistingTasks
Where TaskId = '$(TaskId)';
ENDIF
IF Len('$(vLimitFileName)') = 0 or NoOfRows('isFileAnExistingTask') > 0 then
QlikViewServerTasks_Recipients:
LOAD ObjectType AS RecipientObjectType,
ID AS RecipientID,
SubField(ID, '\', 1) AS Domain,
SubField(ID, '\', 2) AS [Group name],
'$(TaskId)' AS TaskId
FROM [$(File)] (XmlSimple, Table is [DistributeTask/Resources/Resource/AddRecipient]);
QlikViewServerTasks_Resource:
LOAD Type,
Path AS TargetPath,
DistributionMethod,
ID AS ResourceID,
MountPoint,
'$(TaskId)' AS TaskId
FROM [$(File)] (XmlSimple, Table is [DistributeTask/Resources/Resource]);
QlikViewServerTasks_ScheduleTrigger:
LOAD RecurrenceType,
RepeatEvery,
HourStart,
Enabled AS Enabled_Trigger,
ID AS TriggerId,
'$(TaskId)' AS TaskId
FROM [$(File)] (XmlSimple, Table is [DistributeTask/StartTriggers/ScheduleTrigger]);
QlikViewServerTasks_TaskInfo:
LOAD *,
Upper (Container) as CONTAINER;
LOAD ID,
Generation,
Name AS TaskName,
Enabled AS Enabled_Task,
SendAlertMail,
AllowPluginClient,
AllowMobileClient,
AllowZFClient,
AllowPDFGeneration,
DownloadAccess,
ExportAccess,
NameTemplate,
SubField([SourceDocument/FileName], '\', SubStringCount([SourceDocument/FileName], '\') + 1) as SourceFileName,
[SourceDocument/FileName] as SourceFullFileName,
If (Index(SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 3), '.qvw') > 0,
Null(),
SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 3)) as Folder,
If (Index(SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 3), '.qvw') > 0,
Null(),
SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 2)) as Container,
If (Index(SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 3), '.qvw') > 0,
Null(),
SubField(SubField(Replace([SourceDocument/FileName], '$(vBaseSourcePath)' & '\', ''), '\', 1), '.', 2)) as Environment,
'$(TaskId)' AS TaskId,
'Distribute task' as [Task type]
FROM [$(File)] (XmlSimple, Table is [DistributeTask]);
// End of [Task_e075c5e5-d47e-4b5e-87e2-ee46db3e7a82.xml] LOAD statements
QlikViewServerTasks_TaskInfo:
LOAD ID,
Generation,
Name AS TaskName,
Enabled AS Enabled_Task,
SendAlertMail,
Null() as AllowPluginClient,
Null() as AllowMobileClient,
Null() as AllowZFClient,
Null() as AllowPDFGeneration,
Null() as DownloadAccess,
Null() as ExportAccess,
Null() as NameTemplate,
Null() as SourceFileName,
Null() as SourceFullFileName,
Null() as Folder,
Null() as Container,
Null() as Environment,
Null() as CONTAINER,
'$(TaskId)' AS TaskId,
'External program task' as [Task type]
FROM [$(File)] (XmlSimple, Table is [ExternalProgramTask]);
// End of [Task_e075c5e5-d47e-4b5e-87e2-ee46db3e7a82.xml] LOAD statements
ENDIF
IF not IsNull(TableNumber('isFileAnExistingTask')) Then
DROP Table isFileAnExistingTask;
ENDIF
next File
for each Dir in dirlist ('$(Root)' & '\*' )
call DoDirTasks ('$(Dir)')
next Dir
end sub
sub DoDirTaskExecutionHistory (Root)
for each File in filelist ('$(Root)' & '\*.xml')
LET TaskId = Replace(SubField('$(File)', '\', SubStringCount('$(File)', '\') + 1), 'Task_', '');
// QlikViewServerTasks_TaskExecutionHistory:
// LOAD ObjectType AS RecipientObjectType,
// ID AS RecipientID,
// SubField(ID, '\', 1) AS Domain,
// SubField(ID, '\', 2) AS [Group name],
// '$(TaskId)' AS TaskId
// FROM [$(File)] (XmlSimple, Table is [DistributeTask/Resources/Resource/AddRecipient]);
//
// Directory;
// // Start of [TaskExecutionHistory_e075c5e5-d47e-4b5e-87e2-ee46db3e7a82.xml] LOAD statements
QlikViewServerTasks_TaskExecutionHistoryItems:
LOAD TaskId,
TaskLogId,
TaskStatus,
Date(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)'), 'YYYY-MM-DD hh:mm:ss') AS StartTimeStamp,
Date(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)') - DayStart(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)')), 'hh:mm:ss') AS StartTime,
Date(DayStart(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)')), 'YYYY-MM-DD') AS StartDate,
Date(MonthStart(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)')), 'YYYY-MM-DD hh:mm:ss') AS StartMonth,
Date(Date#(EndTimeStamp, '$(vDateTimeFormatInFiles)'), 'YYYY-MM-DD hh:mm:ss') AS EndTimeStamp,
If (Num(DayStart(Today())-DayStart(Date#(StartTimeStamp, '$(vDateTimeFormatInFiles)')), '0') < 7, 'Last week', Null()) as LastWeek,
Timestamp#(Duration, 'hh:mm:ss.fffffff') AS Duration,
Duration * 24 * 60 AS Duration_Minutes,
1 As TaskExecutionCount
FROM [$(File)] (XmlSimple, Table is [Root/TaskExecutionHistoryItems/TaskExecutionHistoryItem])
Where Exists (TaskId);
// End of [TaskExecutionHistory_e075c5e5-d47e-4b5e-87e2-ee46db3e7a82.xml] LOAD statements
next File
for each Dir in dirlist ('$(Root)' & '\*' )
call DoDirTaskExecutionHistory ('$(Dir)')
next Dir
end sub
