Qlik Sense SaaS: Getting User Email

[Marcoimp]

Hi,

is there a way, in Qlik Sense SaaS edition, to get the user email instead of the OsUser() info? I just need to recognize users in order to switch on/off some sheets or table column...

Some help?

Thank you.

Marco

[timmy]

Any news on this topic? I face the same issue at a customer.

[MarcoImperiale]

Hi Timmy,

no answers.

I resolved using an Inline Table with OsUser() IDP code as key, UserName, eMailAddress and some other info and then, filtering fields I need to show with the OsUser() in SetAnalysis... it works, even not so confortable...

UserName
=only({<UserCode={'$(=Subfield(OsUser(),'UserId=',2))'}>}UserName)

eMail
=only({<UserCode={'$(=Subfield(OsUser(),'UserId=',2))'}>}UserMail)

[Marcoimp]

This is my same solution, but it is very strange that there is no other simpler way to get it !!!

[stevedark · Accepted solution]

Hi @Marcoimp 

I know this thread is a bit old, but I hope the information is still useful.

It feels like there should be an easier way, but how I go about this is to download the list of users from the API and then you can join from the IDP subject to the users email. This can then be matched to OSUser() using set analysis.

=if(count({<%AdminOSUser={'$(=subfield(OSUser(),'=',3))'}>}[Admin Email]) > 0, 1, 0)

The list of users comes from the following API URL:

let vURL = 'https://$(vInstance)/api/v1/users?limit=100'

A worked example of how to connect to and pull the information for this table is given as one of the example scripts with my Instant Sense Application, which you can download here:

https://www.quickintelligence.co.uk/instant-qlik-sense-app/

Inside the zip you will find a DataSets folder, and in there SaaSSessions, this has a script which loads all session starts from the API, but the bit you need is just the User API part.

I'm currently working on a script to get full usage details, which will be added to the download soon - just testing it at the moment.

Hope that helps.

Steve

[Marc82]

This setAnalysis don't work for me. 

UserName and UserMail doesn't exist.

Could you help me please?

Thank you

[stevedark · Accepted solution]

Hi @Marc82 

You need to pull the email address from the API first and then join it into the data model.

As I mentioned there is a full script, that includes this in the Instant Sense App which I reference above, the key bits are this though:

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

// 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 ******';

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

// 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

This blog post talks about how to connect to the API and create the GenericGET connection:

https://www.quickintelligence.co.uk/sense-capacity-licence-notify/

Hope that helps,

Steve