Able to fetch only 100 rows for subsection attributes from API call using Qlik data load editor

[skumari]

Hi All,

I am trying to pull audit data for last 30 days in Qlik App analyzer using Data load editor and Rest API call.

After multiple try,I am successful in  pulling header data in a separate query and  the subsection data in separete query but subsection is only returning 100 rows and after that it stops. I am assuming same href URL is being passed each time for subsection that is why it only pull first 100 rows because first URL is new one for the system and subsequent URL looks same.

I do not know how to pull all the data from for subsection attributes. I am using pagination as well. Please suggest where I am going wrong.

Here is my code:

For Header attributes.

Sub get_audits

// 1. Time Range Setup

LET vNow = Date(Now(1), 'YYYY-MM-DD') & 'T' & Time(Now(1), 'hh:mm:ss') & 'Z';

LET vYesterday = Date(Now(1) - 90, 'YYYY-MM-DD') & 'T' & Time(Now(1) - 90, 'hh:mm:ss') & 'Z';

// 2. Parameters & Connection

LET vParams = 'limit=100&eventType=com.qlik.v1.analytics.analytics-app-client.sheet-view.opened&eventTime=$(vYesterday)/$(vNow)';

LIB CONNECT TO '$(vu_rest_connection)';

// 3. Loop Control Variables

LET vNextUrl = 'https://$(vu_tenant_fqdn)/api/v1/audits?$(vParams)';

LET vPrevUrl = '';        // To detect duplicate URLs

LET vPageCounter = 0;     // Safety counter

LET vMaxPages = 1000;      // Hard limit to prevent infinite loops

// 4. Pagination Loop

DO WHILE Len('$(vNextUrl)') > 0 AND '$(vNextUrl)' <> '$(vPrevUrl)' AND vPageCounter < vMaxPages

    LET vPrevUrl = vNextUrl;

    vPageCounter = vPageCounter + 1;

    TRACE >>> Fetching Page $(vPageCounter): $(vNextUrl);

    RestConnectorMasterTable:

    SQL SELECT

        "__KEY_root",

        (SELECT

            "eventId", "eventTime", "userId", "__KEY_data"

        FROM "data" PK "__KEY_data" FK "__KEY_root"),

        (SELECT

            (SELECT "href" FROM "next")

        FROM "links" FK "__KEY_root")

    FROM JSON (wrap on) "root" PK "__KEY_root"

    WITH CONNECTION ( URL "$(vNextUrl)" );

    // Check if any data was actually returned

    IF NoOfRows('RestConnectorMasterTable') = 0 THEN

        DROP TABLE RestConnectorMasterTable;

        EXIT DO; // Stop if the response is empty

    END IF

    // Store Data

    Audit_Temp:

    LOAD

        [eventId]    AS [eventid],

        [eventTime]  AS [eventtime],

        [userId]     AS [userid],

        [__KEY_data]

    RESIDENT RestConnectorMasterTable

    WHERE NOT IsNull([eventId]);

TRACE >>> $(vNextUrl);

    // 5. Extract Next URL

    LET vNextUrl = ''; // Clear for next check

    _NextPage:

    LOAD DISTINCT [href] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([href]);

    IF NoOfRows('_NextPage') > 0 THEN

        LET vNextUrl = Peek('href', 0, '_NextPage');

    END IF

    DROP TABLE _NextPage;

    DROP TABLE RestConnectorMasterTable;

LOOP;

// 6. Final Table Cleanup

IF vPageCounter > 0 THEN

    RENAME TABLE Audit_Temp TO Audit_Event;

    TRACE >>> Load Complete. Total Pages: $(vPageCounter);

ELSE

    TRACE >>> No data found for the specified period.;

END IF

END SUB

For subsection attributes:

Sub get_audits1

// 1. Time Range Setup

LET vNow = Date(Now(1), 'YYYY-MM-DD') & 'T' & Time(Now(1), 'hh:mm:ss') & 'Z';

LET vYesterday = Date(Now(1) - 90, 'YYYY-MM-DD') & 'T' & Time(Now(1) - 90, 'hh:mm:ss') & 'Z';

// 2. Parameters & Connection

LET vParams = 'limit=100&eventType=com.qlik.v1.analytics.analytics-app-client.sheet-view.opened&eventTime=$(vYesterday)/$(vNow)';

LIB CONNECT TO '$(vu_rest_connection)';

// 3. Loop Control Variables

LET vNextUrl = 'https://$(vu_tenant_fqdn)/api/v1/audits?$(vParams)';

LET vPrevUrl = '';        // To detect duplicate URLs

LET vPageCounter = 0;     // Safety counter

LET vMaxPages = 100;      // Hard limit to prevent infinite loops

// 4. Pagination Loop

DO WHILE Len('$(vNextUrl)') > 0 AND '$(vNextUrl)' <> '$(vPrevUrl)' AND vPageCounter < vMaxPages

    LET vPrevUrl = vNextUrl;

    LET vPageCounter = vPageCounter + 1;

    TRACE >>> Fetching Page $(vPageCounter): $(vNextUrl);

    RestConnectorMasterTable:

    SQL SELECT

        "__KEY_root",

        (SELECT

         "__KEY_data",

         "__FK_data",

            (SELECT

                "appId", "sheetId", "sheetTitle", "__KEY_data_u0","__FK_data_u0"

             FROM "data" PK "__KEY_data_u0" FK "__FK_data_u0"),

             (SELECT

                "__KEY_links",

                "__FK_links",

                  (SELECT

                      "href",

                      "__FK_Self"

                    FROM "Self" FK "__FK_Self")

                FROM "links" PK "__KEY_links" FK "__FK_links")

          FROM "data" PK "__KEY_data" FK "__FK_data")

    FROM JSON (wrap on) "root" PK "__KEY_root"

    WITH CONNECTION ( URL "$(vNextUrl)" );

    // Check if any data was actually returned

    IF NoOfRows('RestConnectorMasterTable') = 0 THEN

        DROP TABLE RestConnectorMasterTable;

        EXIT DO; // Stop if the response is empty

    END IF

    // Store Data

    Audit_Temp:

    LOAD

        [appId]      AS [AppID],

        [sheetId]    AS [sheetid],

        [sheetTitle] AS [sheettitle],

       __KEY_data_u0 as __KEY_data,

       __FK_data_u0 as __FK_data

    RESIDENT RestConnectorMasterTable

    WHERE NOT IsNull([__KEY_data_u0]);

    // 5. Extract Next URL

    LET vNextUrl = ''; // Clear for next check

    _NextPage:

    LOAD DISTINCT [href] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([href]); //AND [rel] = 'next';

    IF NoOfRows('_NextPage') > 0 THEN

        LET vNextUrl = Peek('href', 0, '_NextPage');

    END IF

    DROP TABLE _NextPage;

    DROP TABLE RestConnectorMasterTable;

LOOP;

// 6. Final Table Cleanup

IF vPageCounter > 0 THEN

    RENAME TABLE Audit_Temp TO Audit_Apps;

    TRACE >>> Load Complete. Total Pages: $(vPageCounter);

ELSE

    TRACE >>> No data found for the specified period.;

END IF

END SUB

[Leigh_Kennedy]

Can you share your trace output?  Specifically these lines:
TRACE >>> Fetching Page $(vPageCounter): $(vNextUrl);

I recommend using qlik cli with the verbose flag and looking at the GETs it makes.  The first get will return a GUID to be passed for the next batch. It looks like you are trying to do this, but it's clearly not working.  

 qlik audit ls --limit 500 --verbose 2>&1 | grep GET

GET https://xyz.us.qlikcloud.com/api/v1/audits?limit=100
GET https://xyz.us.qlikcloud.com/api/v1/audits?limit=100&next=KQAAAAlldmVudFRpbWUAUNUeSZ4BAAAHX2lkAGoOn3JqJJ1GxmbN7QA
GET https://xyz.us.qlikcloud.com/api/v1/audits?limit=100&next=KQAAAAlldmVudFRpbWUA4Iy-SJ4BAAAHX2lkAGoOhs...
GET https://xyz.us.qlikcloud.com/api/v1/audits?limit=100&next=KQAAAAlldmVudFRpbWUAyRS4SJ4BAAAHX2lkAGoOhS...
GET https://xyz.us.qlikcloud.com/api/v1/audits?limit=100&next=KQAAAAlldmVudFRpbWUAhsi3SJ4BAAAHX2lkAGoOhR...