Request audit API with filters

[paulcalvet]

Dear all,

I try to request Audit API with filters but it doesn't work.

When I request with this query, it works well:

https://mytenant.eu.qlikcloud.com/api/v1/audits

But when I try to add filter, 

https://mytenant.eu.qlikcloud.com/api/v1/audits?filter=(eventTime ge %222024-06-01T00:00:00.000Z%22)&limit=100

Do you know how I can use filter with Audit API ?

Thanks !

[paulcalvet]

Ok I see in the doc that we can't filter this API...

[CedLeb · Accepted solution]

@paulcalvet You can return all the audit records within a date range, using the query parameter eventTime.

For example:

https://mytenant.eu.qlikcloud.com/api/v1/audits?eventTime=2024-06-01T00:00:00.000Z/2024-06-07T00:00:00.000Z&limit=100

[paulcalvet]

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