Load the Historical Data in Governance Dashboard

[dmohanty]

Hi All,

Need to load the historical data into the Governance Dashboard - mainly into the SessionTaskAuditMaster table to show the performances in the Previous Timestamps.

I followed one approach -

1. Converted the table into a QVD and added a Date Field to extract and store data into the QVD from today onwards on daily basis.
2. Loaded the table into the Model by removing the Hidden Script.

But not sure how to get the previous dates from the logs. Could someone please help me more on this -

1. What is the source for the Governance Dashboard?
2. How to read those historical data and bring into the model (can we edit our own script there)?
3. Mainly needed for 'Publisher Task "Last Run" Status' sheet

[Tyler_Waterfall]

Are you trying to view task reload history or performance (CPU, RAM) history?

[dmohanty]

Hi twa‌,

Thanks for replying.

Mainly I am focusing on to get the the Publisher Task Reload history. Details to be in Publisher tab of the SHStatPub sheet.

[Tyler_Waterfall]

Okay.

So as you have discovered, the Governance Dashboard only loads the last reload status based on the  TaskResult*.xml files contained in C:\ProgramData\QlikTech\DistributionService\TaskResults (or wherever you have configured to store the Distribution service logs).

To get the high-level reload history, you will need to load the TaskExecutionHistory_*.xml files in the ..\DistributionService\TaskResultHistory\ folder and link that in with the SessionTaskAuditMaster by TaskId. Actually, to maintain the data model, you would need to concatenate those logs into SessionTaskAuditMaster and re-populate the Node_TS table - however this is going to get tricky fast because you don't have the HostName from the TaskExecutionHistory logs.

Here's an example of what you'd get from the TaskExecutionHistory logs:

<TaskExecutionHistoryItem

Duration="00:00:05.1562419"

EndTimeStamp="9/29/2015 12:39:14 PM"

StartTimeStamp="9/29/2015 12:39:09 PM"

TaskStatus="Failed"

TaskLogId="62cfe0da-17f6-4df2-8bf9-dd188abc68d2"

TaskId="6f32884a-60f0-4cf2-b956-df94fee55988"

/>

[Tyler_Waterfall]

You might also try something like the Publisher Analyzer tool from Hampus here https://community.qlik.com/docs/DOC-6580.

I think there's another tool out there too, but not sure.

Sorry there isn't an easy way to incorporate reload history into the current Governance Dashboard. It is high on the enhancement request list.

[dmohanty]

Okay, twa thanks for explanation.

Can we simply do a Load statement from the .\DistributionService\TaskExecutionHistory\ to load the TaskExecutionHistory_*.xml files and concatenate into SessionTaskAuditMaster.

Actually as there is no scripts in the Edit Script, so quite bit unsure how to add new Load statements.

[Tyler_Waterfall]

Yes, there lies the problem. The script of the Governance Dashboard is hidden (long history there), so you cannot simply insert custom script at the end (in fact there is an "exit script" at the end of the hidden script, so anything you write in the load script editor will be ignored ).

you could try using the UserConfig script as described in the documentation - in which you load those xml files. The problem, though, is that such a script runs before the data model is created.

However, consider the data model. You would need to link in by TaskID (highlighted below) and create a separate table with TaskID, timestamp (choose start or stop), duration, and result. This would work for analyzing task reload history by itself, but you would have a separate date (calendar) for reload history.

[dmohanty]

Thank you twa‌ for the explanation here.

Though I am not familiar is usage of the UserConfig option, but trying how to do it.

So you mean, once I load the TaskExecutionHistory_*.xml files , I can link that new table to PubDocTask table (table you have highlighted above). Right?

One other question please - What should be there in the UserConfig script - means syntax and scripts. How to put those?

[Tyler_Waterfall]

I would load the xml files into a new QlikView desktop app and then copy and paste the load script from there into a text file.

Something like the following, where you set dt_fmt_1 based on the format of the timestamp in the xml and set pathTaskExecutionHistory to where the TaskExecutionHistory xml files reside.

TaskExecutionHistoryItem:

LOAD TaskId as TaskID,

    TaskLogId,

    TaskStatus,

    StartTimeStamp,

    EndTimeStamp,

    subfield(Duration,'.',1) as [Task Duration],

    MonthName(date#(StartTimeStamp,'$(dt_fmt_1)')) as [Task History Month],

    Date(floor(date#(StartTimeStamp,'$(dt_fmt_1)'))) as [Task History Date],

    year(date#(StartTimeStamp,'$(dt_fmt_1)')) as [Task History Year],

    hour(timestamp#(StartTimeStamp,'$(dt_fmt_1)')) as [Task History Hour]

FROM [$(pathTaskExecutionHistory)\TaskExecutionHistory_*.xml] (XmlSimple, Table is [Root/TaskExecutionHistoryItems/TaskExecutionHistoryItem]);

[dmohanty]

Hi twa‌,

Thanks for the help here!

I couldn't find the format of the timestamp in my XML file, so unable to create a field called '$(dt_fmt_1)', as you have stated above

I am attaching one of my XML file here, could you please help more in formulating it?