Many thanks @CedLeb !
I create an automation to retrieve the sheet name, is there a Rest API to list all sheet of an application ?
I didn't find it in the documentation (only with JSON/RPC).
Here is the full script to retrieve only open sheet event from audit and store into monthly QVD file :
// Create the table of month to extract
// -1 last two months
// -2 last three months
// Etc.
SET varMinDate = MonthStart(AddMonths(Today()-1, -1));
SET varMaxDate = MonthStart(AddMonths(Today()-1, 0));
// Create a table with the months to load
YEARMONTH:
LOAD DISTINCT
Left(Date(($(varMinDate) + IterNo() - 1), 'YYYY-MM'), 7) as %YYYYMM,
Left(Date(($(varMinDate) + IterNo() - 1), 'YYYYMM'), 6) as %YYYYMM_2
AutoGenerate(1)
WHILE ($(varMinDate) + IterNo() - 1) <= $(varMaxDate);
Set vu_rest_connection = 'Common:REST for analytics-app-client.sheet-view.opened';
LET vu_tenant_fqdn = GetSysAttr('tenantUrl');
Let vToday=Replace(timestamp(MonthStart(floor(Now(1))),'YYYY-MM-DD hh:mm:ss.fffZ'),' ','T');
LET Month_End = Replace(timestamp(MonthEnd(floor(Now(1))),'YYYY-MM-DD hh:mm:ss.000Z'),' ','T');
LIB CONNECT TO '$(vu_rest_connection)';
FOR EACH v_Period IN FieldValueList('%YYYYMM')
// Let vTimestamp = makedate(2024,6,1);
Let vTimestamp = Timestamp(makedate(num(SubField('$(v_Period)','-',1)),num(SubField('$(v_Period)','-',-1)),1));
LET vPeriodStart = Replace(timestamp(MonthStart(floor('$(vTimestamp)')),'YYYY-MM-DD hh:mm:ss.fffZ'),' ','T');
LET vPeriodEnd = Replace(timestamp(MonthEnd(floor('$(vTimestamp)')),'YYYY-MM-DD hh:mm:ss.fffZ'),' ','T');
SET vParams = 'eventType=com.qlik.v1.analytics.analytics-app-client.sheet-view.opened&eventTime=$(vPeriodStart)/$(vPeriodEnd)&limit=100';
AuditFull:
Load * inline [__KEY_root];
Do
NoConcatenate
AuditSLice:
Load * inline [__KEY_root];
NoConcatenate
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"id" AS "id_u0",
"contentType",
"eventId",
"eventTime",
"eventType",
"eventTypeVersion",
"source",
"tenantId" AS "tenantId_u0",
"userId",
"__KEY_data",
"__FK_data",
(SELECT
"createdByUser",
"description",
"id",
"sub",
"subType",
"tenantId",
"modifiedTime",
"modifierId",
"scriptId",
"unlocksAt",
"email",
"subject",
"appId",
"createdDate",
"creatorId",
"enabled",
"expiry",
"insightLink",
"name",
"ownerId",
"resourceId",
"resourceLink",
"resourceType",
"sharingTaskType",
"spaceId",
"thumbnail",
"transports",
"__FK_data_u0"
FROM "data" FK "__FK_data_u0"),
(SELECT
"__KEY_links",
"__FK_links",
(SELECT
"Href",
"__FK_Self"
FROM "Self" FK "__FK_Self"),
(SELECT
"href",
"__FK_self"
FROM "self" FK "__FK_self")
FROM "links" PK "__KEY_links" FK "__FK_links"),
(SELECT
"ownerId" AS "ownerId_u0",
"spaceId" AS "spaceId_u0",
"topLevelResourceId",
"__KEY_extensions",
"__FK_extensions",
(SELECT
"newValue",
"oldValue",
"path",
"__FK_updates"
FROM "updates" FK "__FK_updates"),
(SELECT
"sub" AS "sub_u0",
"subType" AS "subType_u0",
"__FK_actor"
FROM "actor" FK "__FK_actor")
FROM "extensions" PK "__KEY_extensions" FK "__FK_extensions")
FROM "data" PK "__KEY_data" FK "__FK_data"),
(SELECT
"Prev",
"__KEY_links_u0",
"__FK_links_u0",
(SELECT
"Href" AS "Href_u0",
"__FK_Self_u0"
FROM "Self" FK "__FK_Self_u0"),
(SELECT
"Href" AS "Href_u1",
"__FK_Next"
FROM "Next" FK "__FK_Next"),
(SELECT
"href" AS "href_u0",
"__FK_self_u0"
FROM "self" FK "__FK_self_u0"),
(SELECT
"href" AS "href_u1",
"__FK_next"
FROM "next" FK "__FK_next")
FROM "links" PK "__KEY_links_u0" FK "__FK_links_u0")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://mytenant.eu.qlikcloud.com/api/v1/audits?$(vParams)"
);
Concatenate(AuditSLice)
LOAD [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
left join (AuditSLice)
LOAD [id_u0] AS [id_u0],
[contentType],
[eventId],
[eventTime],
[eventType],
[eventTypeVersion],
[source],
[tenantId_u0] AS [tenantId_u0],
[userId],
[__KEY_data],
[__FK_data] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);
left join (AuditSLice)
LOAD
[appId],
[__FK_data_u0] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data_u0]);
left join (AuditSLice)
LOAD [__KEY_links],
[__FK_links] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links]);
left join (AuditSLice)
LOAD [Href],
[__FK_Self] AS [__KEY_links]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Self]);
left join (AuditSLice)
LOAD [href],
[__FK_self] AS [__KEY_links]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_self]);
left join (AuditSLice)
LOAD [Prev],
[__KEY_links_u0],
[__FK_links_u0] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links_u0]);
left join (AuditSLice)
LOAD [Href_u0] AS [Href_u0],
[__FK_Self_u0] AS [__KEY_links_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Self_u0]);
left join (AuditSLice)
LOAD [Href_u1] AS [Href_u1],
[__FK_Next] AS [__KEY_links_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Next]);
left join (AuditSLice)
LOAD [href_u0] AS [href_u0],
[__FK_self_u0] AS [__KEY_links_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_self_u0]);
left join (AuditSLice)
LOAD [href_u1] AS [href_u1],
[__FK_next] AS [__KEY_links_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_next]);
NextURL:
Load DISTINCT
[href_u1] AS NextURL
Resident RestConnectorMasterTable
Where NOT match(href_u1,'');
LET vParams = SubField(Peek('NextURL',0,'NextURL'),'?',-1);
LET vNextURLRows = NoOfRows('NextURL');
Drop Table NextURL;
drop table RestConnectorMasterTable;
Concatenate(AuditFull)
Load *
Resident AuditSLice;
Drop table AuditSLice;
Loop while $(vNextURLRows)>0;
store AuditFull into [lib://Temporary files:DataFiles/analytics-app-client-sheet-view.opened_$(v_Period).qvd](qvd);
drop table AuditFull;
NEXT v_Period
