User Detailed Reporting- Decoding the Permission

Hello All:

I am particularily interested in knowing how can i decode the values to match it the way it shows over the user details (user.edituser)

KUAF.GroupID (Int) >> Department ( Default Infor Technology Group --etc format)

KUAF.TimeZone (Int) >> Time Zone (GMT-08:00 Pacific -- etc format)

KUAF.UserPrivileges (Int) >> Privileges (Log-In enabled,......system administration rights etc format)?

So far my query look like this for SQL 2008 , LL 9.7.1 based application.

select KUAF.Name "Log-in Name",KUAF.GroupID as "Department",

KUAF.LastName + ', ' + KUAF.FirstName "User Name",KUAF.Title "Title",

KUAF.MailAddress "E-mail" ,KUAF.OfficeLocation "Office Location",

KUAF.TimeZone "Time Zone", KUAF.UserPrivileges "Privileges" from KUAF where Type = 0

Many thanks for your inputs!

Mukesh Giri

Find more posts tagged with

Comments

Kyle_Swidrovich

Hi Mukesh,

For the TimeZone values, have a look at my MemberService C# code example posted in the OTDN forums for web services:https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objaction=overview&objid=27298202

All groups are stored in KUAF as well, so you can obtain the group name from the GroupID.

I think the user privileges were decoded at one point somewhere in this forum… just can’t remember which post off the top of my head…

Cheers,

Kyle

Appu_Nair1

For SQLserver I use this it uses a bitmask to find which bit is ON

it is part of a script so there is more than what you want

use livelink;

select getdate();

PRINT N'HOW MANY UNDELETED USERS WE HAVE'

SELECT NAME,ID,LASTNAME,FIRSTNAME FROM KUAF WHERE DELETED=0 AND TYPE=0;
PRINT N'HOW MANY DELETED USERS WE HAVE'
SELECT NAME,ID,LASTNAME,FIRSTNAME FROM KUAF WHERE DELETED=1 AND TYPE=0;

PRINT N'HOW MANY UNDELETED GROUPSWE HAVE'
SELECT NAME,ID FROM KUAF WHERE DELETED=0 AND TYPE=1;
PRINT N'HOW MANY DELETED GROUPS WE HAVE'
SELECT NAME,ID FROM KUAF WHERE DELETED=1 AND TYPE=1;

PRINT N'WHO HAS PUBLIC ACCESS IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,11))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

PRINT N'WHO HAS SA IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,8))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

PRINT N'WHO HAS UA IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,4))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

PRINT N'WHO HAS CRMG IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,6))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

PRINT N'WHO HAS CRMU IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,5))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

PRINT N'WHO HAS LOGINENABLED IN THIS LIVELINK'
SELECT K.NAME AS LOGIN,K.LASTNAME+' '+K.FIRSTNAME AS NAME
FROM KUAF K WHERE FLOOR(K.USERPRIVILEGES/POWER(2,3))%2=1 AND DELETED=0 AND TYPE=0 ORDER BY LOGIN;

go

select getdate();

Mukesh_Giri

Thanks Appu\Kyle and Colin:

I was able to write the SQL query and when i run the query into SQL Server management studio everything works great while if i do paste the same code in Live-report i find that the case statement which i included for "timezone" and "Privileges" only "Privileges" is returned in both the fields.

Please see my case statements as

case k.timezone

when 1 then 'U.S. Pacific'

when 2 then 'U.S. Mountain'

when 3 then 'U.S. Central'

when 4 then 'U.S. Eastern'

when 5 then 'U.S. Alaska'

when 6 then 'U.S. Hawaii'

when 7 then '--------------------'

case

when k.userprivileges = 14 then 'No privileges/Disabled'

Any pointers why the userpriveleges only is returned in both the cloumns?

Thanks,

Mukesh Giri