Microsoft is launching a new export format for Azure Logging in the next weeks (https://docs.microsoft.com/en-us/azure/kusto/concepts/) and guess what, Power BI can already natively import this, so I wanted Qlik to read this, too.
The result of the Kusto POST-Call comes as JSON (see here for a sample) but the data model created by the Qlik's REST Connector "select data" wizard was sub-optimal: All values of all tables come in just one column, while you hoped to get n tables with x columns each. So I had to massage the initial data returned.
I spare you the details on how I made the script, you can check yourself in the attached .qvf file if you like. Basically, I had to
- join tablename information from the root table down to other tables and
- to add an auto-increment number in two tables which restarts at 1 when the foreign key changes
This is what the REST-Connector Wizard creates for you ...
After my massage with Qlik Scripting I have added necessary fields:
My script computes the correct ultimate load-statement itself, something like this (dont worry if you can't read it, it "pivots" rows into columns 🙂
LOAD
Only(If(GoesToColumn = 1, @Value)) AS [Timestamp],
Only(If(GoesToColumn = 2, @Value)) AS [ClientRequestId],
Only(If(GoesToColumn = 3, @Value)) AS [ActivityId],
Only(If(GoesToColumn = 4, @Value)) AS [SubActivityId],
Only(If(GoesToColumn = 5, @Value)) AS [ParentActivityId],
Only(If(GoesToColumn = 6, @Value)) AS [Level],
Only(If(GoesToColumn = 7, @Value)) AS [LevelName],
Only(If(GoesToColumn = 8, @Value)) AS [StatusCode],
Only(If(GoesToColumn = 9, @Value)) AS [StatusCodeName],
Only(If(GoesToColumn = 10, @Value)) AS [EventType],
Only(If(GoesToColumn = 11, @Value)) AS [EventTypeName],
Only(If(GoesToColumn = 12, @Value)) AS [Payload]
RESIDENT Rows
WHERE Rows.TableName = '$(vTable)'
GROUP BY __KEY_Rows;
Finally, I drop all the tables created by the REST Connector wizard, to get this nice to read format:
