Hi Experts,
Hope all are doing well!.
Friends I need some help/suggestion on below requirements.
1.I have a one Service Scheduler Log file where it contains all APP ID's which were failed during Execution (See Attachment 1) from where I have to consider those all APP ID's and need to pick those Log files.
2. In log files folder(See Attachment) both failed and successfully executed files present.(But Only Failed files need to be consider) having same APP id's from Log folder where all log files are stored after execution.
3. Then I need to find out the Error message from those respective APP Id's after the failed execution and need to send those Error message.
4. Also I need latest log files only from log folder and the same APP ID must be present in Service schedule file.
5. The following Code I am using but I am missing somewhere. Please guide me on this.
Script to Read from Service scheduler file
Service_Scheduler:
LOAD
// Sequence#,
// "ProductVersion",
"Timestamp",
// Severity,
// Hostname,
Id,
// Description,
// ProxySessionId,
// ProxyPackageId,
// RequestSequenceId,
UserDirectory,
"UserId",
ObjectId,
SubField(ObjectId, '|',2) As AppID,
// FileTime() As FileDate,
timestamp#(left(Timestamp,18), 'DD-MM-YYYY hh:mm:ss') as Timestamp_Script_SS,
ObjectName,
// Service,
// Origin,
// Context,
// Command,
// Result,
Message,
Id2
FROM [lib://FLDR_Qlik_System_Scheduler_Log/A11W2V1351_Service_Scheduler.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
Let vNumberOfRows = NoOfRows('Service_Scheduler');
For x=0 to $(vNumberOfRows)-1
Let vAppID = Peek('AppID', $(x), 'Service_Scheduler');
Let vTimestamp = Peek('Timestamp', $(x), 'Service_Scheduler');
Next
Script to Read Log Files from Log Folder
LogFiles:
LOAD
FileName() As FileName
, timestamp#(Mid(FileName(),37,18), 'DD-MM-YYYY hh:mm:ss') as Timestamp_Script_LogFile
, FileTime() As FileDate
, Left(FileName(),36) As LogFiles_AppID
, RowNo() As RowNumber
, @1 As LogScript
, Right([@1], Len([@1]) - Index([@1], ' ', 1)) as LogScriptText
FROM [lib://FLDR_Qlik_ArchivedLogs_Script/*2022*.log](txt, utf8, no labels, delimiter is '\n', no quotes, no eof)
where Floor(FileTime())= $(vToday)
;
//Add the last row number
Left Join(LogFiles)
Load
FileName //Joined field
, Max(RowNumber) As LastRowNumber
Resident LogFiles
Group By FileName
;
//Get the execution result from the last 2 lines of the log script
Left Join(LogFiles)
Load
FileName //Joined field
, Lower( SubField( Subfield( Concat(LogScript, '.') //Combine the last 2 rows
, 'Execution ', 2 //Keep the text after the word "Execution "
)
, '.', 1 //Keep the text before the dot
)
) As ExecutionResult
Resident LogFiles
Where RowNumber > LastRowNumber - 2 //Get the last 2 rows
Group By FileName
;
Temp:
Load *,
If(ExecutionResult='failed', ExecutionResult) as ExecutionResult_Failed
//ExecutionResult
Resident LogFiles
where ExecutionResult='failed'
AND WildMatch(LogScriptText,'*Error: *');
Drop Tables LogFiles;
Let vNumberOfErrorRows = NoOfRows('Temp');
For x=0 to $(vNumberOfErrorRows)-1
Let vAppID1 = Peek('LogFiles_AppID', $(x), 'Temp');
Let vErrorText = Peek('LogScriptText', $(x), 'Temp');
Next
Rename table Temp to LogFiles;
Exit Script;
The above code considers all logfile and then it is filtering the files whenever it getting "Error:" as matching string,
I am getting the following output as attached.
