It's very long I use the script from Qlik license Session Monitor so it's very long:
SUB monitor_apps_REST_app
LIB CONNECT TO 'monitor_apps_REST_app (qsvm_administrateur)';
RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u4",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u3",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"availabilityStatus",
"__KEY_root",
(SELECT
"userId",
"userDirectory",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"name" AS "name_u2",
"__FK_stream"
FROM "stream" FK "__FK_stream")
FROM JSON (wrap on) "root" PK "__KEY_root";
LET NumRows_monitor_apps_REST_app = NoOfRows('RestConnectorMasterTable');
map_stream:
Mapping LOAD
[__FK_stream] AS [__KEY_root],
[name_u2] AS Stream
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stream]);
App_Stream:
LOAD
[id_u4] AS ObjectId,
ApplyMap('map_stream',__KEY_root,'Unpublished') as [App Stream]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
map_app_owner:
Mapping LOAD
[__FK_owner] AS [__KEY_root],
[userDirectory] & '\' & [userId] as AppOwner
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_owner]);
App:
LOAD
[id_u4] AS ObjectId,
[id_u4] AS AppId,
date(alt(
date#(left(createdDate_u0,10),'YYYY-MM-DD'),
date#(left(createdDate_u0,10),'YYYY/MM/DD'),
date#(left(createdDate_u0,10),'MM-DD-YYYY'),
date#(left(createdDate_u0,10),'MM/DD/YYYY'),
date#(left(createdDate_u0,10),'YYYY.MM.DD'),
'No valid date')
) as [App Created Date],
date(alt(
date#(left(modifiedDate_u0,10),'YYYY-MM-DD'),
date#(left(modifiedDate_u0,10),'YYYY/MM/DD'),
date#(left(modifiedDate_u0,10),'MM-DD-YYYY'),
date#(left(modifiedDate_u0,10),'MM/DD/YYYY'),
date#(left(modifiedDate_u0,10),'YYYY.MM.DD'),
'No valid date')
) as [App Modified Date],
[modifiedByUserName_u0] AS [App Modified By],
[name_u3] AS [App Name QRS],
if(left(publishTime,4)='1753','Never',timestamp(publishTime)) AS [App Publish Time],
[published] AS [App Published],
[description] AS [App Description],
floor([fileSize]/1024) AS [App File Size], // In Kb
timestamp([lastReloadTime]) AS [App Last Reload Time],
[availabilityStatus] AS [App Availability Status],
ApplyMap('map_app_owner',__KEY_root,'Unknown Owner') AS [App Owner]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
// Unify the App Name from the Logs and from QRS (To present the most up-to-date App Name while preserving App Name history)
tempAppName:
NoConcatenate
Load
Distinct ObjectId,
ObjectId as AppIdQRS,
[App Name QRS]
Resident App;
Outer Join (tempAppName)
Load
Distinct ObjectId,
ObjectId as AppIdHistorical,
[App Name] as [App Name Historical]
Resident LogContent
Where len([App Name])>0;
AppName:
NoConcatenate
Load
ObjectId,
if(isnull([App Name QRS]),[App Name Historical],[App Name QRS]) as [App Name],
if(isnull(AppIdQRS) and index(ObjectId,'|')=0,AppIdHistorical,AppIdQRS) as AppId,
if(index(ObjectId,'|')>0,null(),isnull([App Name QRS])*-1) as [AppId Removed from QRS],
[App Name Historical]
Resident tempAppName;
Drop Table tempAppName;
Drop Field [App Name] from LogContent;
Drop Field [App Name QRS] from App;
Drop Field AppId from App;
// End unify App Name
LIB CONNECT TO 'monitor_apps_REST_app (qsvm_administrateur)';
RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u3",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u2",
"appId",
"sourceAppId",
"targetAppId",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"thumbnail",
"savedInProductVersion",
"migrationHash",
"dynamicColor",
"availabilityStatus",
"privileges" AS "privileges_u2",
"schemaPath" AS "schemaPath_u0",
"stream",
"__KEY_root",
(SELECT
"id" AS "id_u0",
"createdDate",
"modifiedDate",
"modifiedByUserName",
"value",
"schemaPath",
"__KEY_customProperties",
"__FK_customProperties",
(SELECT
"id",
"name",
"valueType",
"privileges",
"__KEY_definition",
"__FK_definition",
(SELECT
"@Value",
"__FK_choiceValues"
FROM "choiceValues" FK "__FK_choiceValues" ArrayValueAlias "@Value")
FROM "definition" PK "__KEY_definition" FK "__FK_definition")
FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),
(SELECT
"id" AS "id_u1",
"userId",
"userDirectory",
"name" AS "name_u0",
"privileges" AS "privileges_u0",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_tags"
FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u0"),
(SELECT
"id" AS "id_u2",
"name" AS "name_u1",
"privileges" AS "privileges_u1",
"__FK_stream"
FROM "stream" FK "__FK_stream")
FROM JSON (wrap on) "root" PK "__KEY_root";
[choiceValues]:
LOAD [@Value] AS [@Value],
[__FK_choiceValues] AS [__KEY_definition]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_choiceValues]);
[definition]:
LOAD [id] AS [id],
[name] AS [name],
[valueType] AS [valueType],
[privileges] AS [privileges],
[__KEY_definition] AS [__KEY_definition],
[__FK_definition] AS [__KEY_customProperties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_definition]);
[customProperties]:
LOAD [id_u0] AS [id_u0],
[createdDate] AS [createdDate],
[modifiedDate] AS [modifiedDate],
[modifiedByUserName] AS [modifiedByUserName],
[value] AS [value],
[schemaPath] AS [schemaPath],
[__KEY_customProperties] AS [__KEY_customProperties],
[__FK_customProperties] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customProperties]);
[owner]:
LOAD [id_u1] AS [id_u1],
[userId] AS [userId],
[userDirectory] AS [userDirectory],
[name_u0] AS [name_u0],
[privileges_u0] AS [privileges_u0],
[__FK_owner] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_owner]);
[tags]:
LOAD [@Value_u0] AS [@Value_u0],
[__FK_tags] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);
[stream]:
LOAD [id_u2] AS [id_u2],
[name_u1] AS [name_u1],
[privileges_u1] AS [privileges_u1],
[__FK_stream] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stream]);
[root]:
LOAD [id_u3] AS [id_u3],
[createdDate_u0] AS [createdDate_u0],
[modifiedDate_u0] AS [modifiedDate_u0],
[modifiedByUserName_u0] AS [modifiedByUserName_u0],
[name_u2] AS [name_u2],
[appId] AS [appId],
[sourceAppId] AS [sourceAppId],
[targetAppId] AS [targetAppId],
[publishTime] AS [publishTime],
[published] AS [published],
[description] AS [description],
[fileSize] AS [fileSize],
[lastReloadTime] AS [lastReloadTime],
[thumbnail] AS [thumbnail],
[savedInProductVersion] AS [savedInProductVersion],
[migrationHash] AS [migrationHash],
[dynamicColor] AS [dynamicColor],
[availabilityStatus] AS [availabilityStatus],
[privileges_u2] AS [privileges_u2],
[schemaPath_u0] AS [schemaPath_u0],
[stream] AS [stream],
[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
ENDSUB
