// See the blog post https://www.quickintelligence.co.uk/sense-capacity-licence-notify/
// For details of how to connect to the API and how to set the parameters for the reporting

// This script can be used with the Quick Intelligence Instant Sense Application
// Download this from https://www.quickintelligence.co.uk/isa/
// You will also need the file SaaSSessionsLookups.xlsx to use this

// Note that this app has been kind of superseded by the SaaS Usage app, which gives more details by using a newer API.

// 14-09-2023 - Call to Licence endpoint amended to use Subject rather than UserID

// All code provided without support or warranty of any kind


// Max number of pages to load - increase if you have more than 10,000 rows of history
let iBackstop = 100;

// Flag to say whether we want to refresh all data
let iLoadAll	= 0;

// Name or alias of your Qlik Sense Cloud instance
let vInstance	= 'tenant.region.qlikcloud.com';

// The API key to use - generated under your Profile settings
let vAPIKey 	= '****** Insert Your API Key Here ******';

// Define where QVDs live
let vQVD        = 'lib://DataFiles/$(vInstance)/';

// The initial URL to use
let vURL = 'https://$(vInstance)/api/v1/audits?limit=100&eventType=com.qlik.user-session.begin';

// Functions for various purposes
set vDC = date(date#(left(purgechar([$1], 'TZ-:'), 14), 'YYYYMMDDhhmmss'), 'DD MMM YYYY hh:mm:ss');
set vDF = date($1, 'DD MMM YYYY hh:mm:ss');

// Initialise counters 
let iPage = 0;

// Connect to the REST connection
LIB CONNECT TO 'GenericGET';

// See if there is a QVD of audit records - find the latest date if so
if alt(FileSize('$(vQVD)Audit_UserSessionBegin.qvd'), 0) > 0 and iLoadAll <> 1 then
    TRACE Finding date of last stored audit record;
    
    tmpLatest:
    LOAD
    	max([Event Time]) as Latest
    FROM [$(vQVD)Audit_UserSessionBegin.qvd] (qvd);
    
    let vLatest	= alt(peek('Latest', -1, 'tmpLatest'), 0);
    
    DROP TABLE tmpLatest;
else
    let vLatest = 0;
end if

do while len(vURL) > 0 and iPage < iBackstop
	let iPage = iPage + 1;
    
    TRACE ***** Fetching Audit Page $(iPage);
    
    JSON:
    SQL SELECT 
        (SELECT 
            "id",
            "source",
            "contentType",
            "eventType",
            "eventId",
            "eventTime",
            "tenantId",
            "userId"
        FROM "data" PK "__KEY_data" FK "__FK_data"),
        (SELECT 
            (SELECT 
                "href"
            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 "$(vURL)",
         HTTPHEADER "Authorization" "Bearer $(vAPIKey)"  
        )
    ;

    // Append raw data to temporary table
    tmpData:
    LOAD
        id,
        [source],
        [contentType],
        [eventType],
        [eventId],
        [eventTime],
        [tenantId],
        [userId]
    RESIDENT JSON
    WHERE NOT IsNull(id);

    tmpPage:
    LOAD
        href
    RESIDENT JSON
    WHERE NOT IsNull(href);
    
    DROP TABLE JSON;
    
    // Next page URL, will be blank if all data fetched
    let vURL = peek('href', -1, 'tmpPage');
    DROP TABLE tmpPage;
    
    // If there is more data to load and we already have some check the date
    if len(vURL) > 0 and vLatest > 0 then
    	tmpOldestNew:
        LOAD
            min($(vDC(eventTime))) as OldestNew
        RESIDENT tmpData;
        
        let vOldestNew = peek('OldestNew', -1, 'tmpOldestNew');
        
        DROP TABLE tmpOldestNew;
        
        // If we already have rows newer that the oldest from the latest run
        // remove the next page URL
        let vURL = if(vOldestNew < vLatest, null(), vURL);
    end if
loop

Sessions:
LOAD
    *,
    subfield(Tenant, '.', 1)                        as [Tenant Prefix],
    Date(DayStart([Event Time]), 'DD MMM YYYY')     as Date,
    Date(MonthStart([Event Time]), 'MMM-YYYY')      as Month,
    Month([Event Time])                             as [Month Name],
    Year([Event Time])                              as Year,
    Hour([Event Time])                              as Hour,
    WeekDay([Event Time])                           as Day
    ;
LOAD
    1                                               as SessionCount,
    id                                              as AuditID,
    source                                          as Source,
    contentType                                     as [Content Type],
    eventType                                       as [Event Type],
    eventId                                         as EventID,
    $(vDC(eventTime))                               as [Event Time],
    '$(vInstance)'                                  as Tenant,
    tenantId                                        as TenantID,
    userId                                          as UserID,
    $(vDF(now()))                                   as [Row Fetched]
RESIDENT tmpData;

DROP TABLE tmpData;
    
// If there is already a QVD append from it
if alt(FileSize('$(vQVD)Audit_UserSessionBegin.qvd'), 0) > 0 then
    CONCATENATE(Sessions)
    LOAD
    	*
    FROM [$(vQVD)Audit_UserSessionBegin.qvd] (qvd)
    WHERE NOT EXISTS (AuditID)
    ;
end if

// Write the new QVD
STORE Sessions INTO [$(vQVD)Audit_UserSessionBegin.qvd] (qvd);


// Load user details
let vURL = 'https://$(vInstance)/api/v1/users?limit=100&status=invited%2Cactive%2Cdisabled%2Cdeleted';
let iPage = 0;

do while len(vURL) > 0 and iPage < iBackstop
    let iPage = iPage + 1;
    
    TRACE ***** Fetching User Page $(iPage);
    
    JSON:
    SQL SELECT 
        (SELECT 
            (SELECT 
                "href"
            FROM "next" FK "__FK_next")
        FROM "links" PK "__KEY_links" FK "__FK_links"),
        (SELECT 
            "id",
            "created",
            "lastUpdated",
            "status",
            "name",
            "subject",
            "picture",
            "email"
        FROM "data" PK "__KEY_data" FK "__FK_data")
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION (
      URL "$(vURL)",
      HTTPHEADER "Authorization" "Bearer $(vAPIKey)"  
    )
    ;

    // Append raw data to temporary table
    tmpUsers:
    LOAD
        id,
        created,
        lastUpdated,
        status,
        name,
        subject,
        picture,
        email
    RESIDENT JSON
    WHERE NOT IsNull(id);

    tmpPage:
    LOAD
        href
    RESIDENT JSON
    WHERE NOT IsNull(href);
    
    DROP TABLE JSON;
    
    // Next page URL, will be blank if all data fetched
    let vURL = peek('href', -1, 'tmpPage');
    DROP TABLE tmpPage;
loop

Users:
LOAD
    *,
    [User Name] & ' (' & Organisation & ')'     as [Name and Organisation]
    ;
LOAD
    *,
    subfield([User Email], '@', 2)              as Domain,
    textbetween([User Email], '@', '.')         as Organisation
    ;
LOAD
    1                                           as UserCount,
    id                                          as UserID,
    id						as tmpUserID,
    $(vDC(created))                             as [User Created],
    capitalize(status)                          as [User Status],
    $(vDC(lastUpdated))                         as [User Last Updated],
    capitalize(name)                            as [User Name],
    lower(email)                                as [User Email],
    picture                                     as [Picture Link],
    subject                                     as Subject,
    id                                          as UserExists
RESIDENT tmpUsers;

DROP TABLE tmpUsers;

// If there is already a Users QVD append from it
if alt(FileSize('$(vQVD)Audit_UserDetails.qvd'), 0) > 0 then
    CONCATENATE(Users)
    LOAD
    	*
    FROM [$(vQVD)Audit_UserDetails.qvd] (qvd)
    WHERE NOT EXISTS (tmpUserID, UserID)
    ;
end if

DROP FIELD tmpUserID;

// Write the new QVD
STORE Users INTO [$(vQVD)Audit_UserDetails.qvd] (qvd);


// Fetch the licences and attach these to the users we have
let vURL = 'https://$(vInstance)/api/v1/licenses/assignments?limit=100';
let iPage = 0;

do while len(vURL) > 0 and iPage < iBackstop
    let iPage = iPage + 1;
    
    TRACE ***** Fetching Licence Allocation Page $(iPage);
    
    JSON:
    SQL SELECT
        (SELECT 
            "type",
            "subject",
            "created",
            "excess"
        FROM "data" FK "__FK_data"),
        (SELECT
            (SELECT 
                "href"
            FROM "next" FK "__FK_next")
        FROM "links" PK "__KEY_links" FK "__FK_links")
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION (
      URL "$(vURL)",
      HTTPHEADER "Authorization" "Bearer $(vAPIKey)"  
    )
    ;

    Licences:
    LOAD
    	1                     as LicenceCount,
        subject               as Subject,
        capitalize(type)      as Licence,
        $(vDC(created))       as [Licence Allocated],
        excess                as Excess
    RESIDENT JSON
    WHERE NOT IsNull(subject);

    tmpPage:
    LOAD	
        href
    RESIDENT JSON
    WHERE NOT IsNull(href);

    DROP TABLE JSON;
    
    // Next page URL, will be blank if all data fetched
    let vURL = peek('href', -1, 'tmpPage');
    DROP TABLE tmpPage;
loop

// Store the Licence table
STORE Licences INTO [$(vQVD)Audit_Licences.qvd] (qvd);

// Create a mapping table of licence allocations
Map_LicenceType:
MAPPING LOAD
    upper(Subject) as subj,
    Licence
RESIDENT Licences;

DROP TABLE Licences;

// Load the licence type for all users we know of
UserLicences:
LOAD DISTINCT
    UserID,
    ApplyMap('Map_LicenceType', upper(Subject), if([User Status] = 'Invited', 'Invited', 'None')) as Licence
RESIDENT Users;


// Remove fields not needed in the front end - but kept in QVDs anyways
DROP FIELDS UserExists, AuditID, EventID, Subject;

// Clean up after ourselves
let vInstance   =;
let vAPIKey     =;
let iBackstop   =;
let vInstance   =;
let vAPIKey     =;
let vQVD        =;
let vURL        =;
let vDC         =;
let vDF         =;
let iPage       =;
let vLatest     =;
let vOldestNew  =;
let iLoadAll    =;