nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Are the queries correct?

Atiya_Sultana

Hi,1. I want the data of all log-in enabled users in Livelink. And I have executed the below query which is not giving the results expected. In a particular week, the unique logins data is 11(i have a separate query for that), whereas this query gives me only 2 user names for the login disabled users. When there are 11 unique logins in a week, how can be there no user with login-enabled account. Moreover it gives me data for disabled users which i don't want.Select auditid,auditstr,auditdate,userid, kuaf.name, value1,value2 from dauditnew, kuaf where dauditnew.userid=kuaf.id and auditid=10 and ((convert(varchar,value1) != '0' and value2 is null)) OR ((convert(nvarchar,value2) = '1001') and (convert(nvarchar,value1) is null)) and auditdate between '11-Jan-2009' and '18-Jan-2009' order by auditdate2. Also, I want the names of all users who have not been removed access from Livelink objects. The below query gives me data for the users added to a group along with group id for that week. But it doesn't give the data for all other users who are part of groups from before or earlier weeks what if they are not added to a group this particular week. Which means they had access earlier weeks and this particular week too though they are not added to a new group.Select auditid,auditstr,auditdate,userid, kuaf.name, value1,value2 from dauditnew, kuaf where dauditnew.userid=kuaf.id and auditid=30 and value2 is not null and convert(nvarchar,value2) != '1001' and auditdate between '11-Jan-2009' and '18-Jan-2009' order by auditdateOverall, I want data for all users who have the ability to access Livelink(login enabled, access available on objects) irrespective of whether they login or do not login.The above two queries will help me to do that.Please suggest what is wrong in the above queries so that i can correct and pull the required data from database. If you have any other way to achieve this, please do share with me.Many Thanks,Atiya.

Find more posts tagged with

Comments

John W. Simon, Jr.

1. If you are looking for all enabled users then you do not want to include the audit table in your query. You only need Kuaf, and to decode the userprivileges field (search for other threads in the KC to explain how to decode).2. Again, you don't want to look at audit to find who has permissions to an object. All of this information is contained in dtreeacl (the permissions field is another bitwise field you will have to decode).Cheers, I hope that helps.

Atiya_Sultana

Hi John,Thanks for the reply.I dont want to see who has what permission on an object. My requirement is to list the users who have the ability to access Livelink on a weekly basis. This criteria (ability to access Livelink) is met by 2 conditions.Condition1: A user should have his/her log-in enabled.Condition2: A user should have assigned permissions to on some objects or should be part of a group in Livelink (so that he/she doesn't get "Could not get Enterprise Workspace" error when he/she tries to login).If anyone the above condition is false for a week, then the user name should not appear in the data. Also, if a user(s) has these 2 conditions true for weeks continuously, then his/her name should appear in the data for all those week until one of the above 2 conditions become false. Similarly, for all users.Is there any way to achieve this?Many Thanks,Atiya.

David_Spirhanzl

Message from David Spirhanzl <David.Spirhanzl@syconix.cz> via eLink

eLink

Hi Atiya,

I'm not sure I did understand correctly.

You can easily get information about users that are CURRENTLYallowed to login and see Enterprise Workspace (membership of certain group).

Condition1: select * from ladmin.KUAF where (KUAF.UserPrivileges% 2 = 1); //MSSQL syntax

Condition2: select * from ladmin.KUAF as k INNER JOINladmin.KUAFChildren c ON k.ID=c.ChildID where c.ID = <target group ID>;//e.g. 1001 for DefaultGroup

You can also see attached Excel to this Topic which containsmentioned codes for KUAF.UserPrivileges:

https://knowledge.opentext.com/knowledge/llisapi.dll/open/14505127

Hope this helps.

Best

David Spirhanzl

From: eLink Discussion: Livelink LiveReportsDiscussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, February 03, 2009 5:59 PM
To: eLink Recipient
Subject: [***SPAM***] re - queries correct?

re - queries correct?		Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/02/03 11:56
In reply to: re - queries correct?		Posted by conocoadmin (Simon, John) on 2009/01/30 09:19

Hi John,

Thanks for the reply.

I dont want to see who has what permission on an object. My requirement is to list the users who have the ability to access Livelink on a weekly basis. This criteria (ability to access Livelink) is met by 2 conditions.

Condition1: A user should have his/her log-in enabled.
Condition2: A user should have assigned permissions to on some objects or should be part of a group in Livelink (so that he/she doesn't get "Could not get Enterprise Workspace" error when he/she tries to login).

If anyone the above condition is false for a week, then the user name should not appear in the data. Also, if a user(s) has these 2 conditions true for weeks continuously, then his/her name should appear in the data for all those week until one of the above 2 conditions become false. Similarly, for all users.

Is there any way to achieve this?

Many Thanks,
Atiya.

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Are the queries correct?
Discussion:	Livelink LiveReports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Atiya_Sultana

Hi David,You got it correct.I exactly want the data of uses whose login is enabled and who can see the enterprise workspace. But on a weekly basis. Is there a way to achieve this?Many Thanks,Atiya.

David_Spirhanzl

There is no log for changes in permissions as far as I know.And also no log for groups membership.The information can though be collected using a SQL job gathering data e.g. in special table. Then you would be able to get requested status.Would this be the solution for you?

Appu_Nair

David ,Isn't it true starting with 9.5 and dauditnew there is more enhanced auditing capabilities.I see these in a 9.7.1under events that are auditable would that give the poster any luck.

David_Spirhanzl

Hi,this is new for me.Actually if the auditing of Log-in Disabled, Membership Changed or Members Changed are turned on, then all the needed information is later available.As I tested, it seems that in DAuditNew table you can find User changes such as Log-in Disabled under AuditID=10 and Membership of user in a group under AuditID=30.Thank you for announcing, Appu ;-)RegardsDavid

Evert_Kuiken_(ekuiken_-_(deleted))

Hi Atiya, I think what you need is a scheduled WebReport that runs every week and creates an Excel file. The LiveReport it should run should filter for users with read permissions on ObjectID 2000 and Login Enabled.I do not see the necessity of querying the DAuditNew table. Unless you want to know whether a user has actually logged in and/or accessad any object in the past week.EvertPS I could probably give you a hint about the actual query, but not at this time.

Atiya_Sultana

Hi Appu/David,Thanks for the reply.Log-in Disabled audit gets recorded only if a log-in disabled user accesses Livelink and gets a message "Your login is disabled. Contact administrator".Yes, membershipchanged string or auditid=30 gives the data for the users added or removed from a group. I have used it in my query but it requires modifications :(Any help is appreciated.Many thanks,Atiya.

David_Spirhanzl

I've prepared a query that returns last change for specified UserID, AuditDate (e.g. Monday of monitored week) and AuditID (30 for membershipchanged).select TOP (1) CONVERT(nvarchar,a.Value2) from DAuditNew as a where a.AuditID = 30 and a.UserID = and (CONVERT(nvarchar,a.Value1) = '' or CONVERT(nvarchar,a.Value2) = '') and a.AuditDate < '' order by a.AuditDate descIf the first record has Value2 of 1001 (DefaultGroup) then the last change of membership of the user in DefaultGroup was "became a member".You can then list all users who meet this condition:select k.Name from KUAF as k where k.Type = 0 and '1001' = (select TOP (1) CONVERT(nvarchar,a.Value2) from DAuditNew as a where a.AuditID = 30 and a.UserID = k.ID and (CONVERT(nvarchar,a.Value1) = '1001' or CONVERT(nvarchar,a.Value2) = '1001') and a.AuditDate < '2008-10-02' order by a.AuditDate desc)If DefaultGroup is default (what usually is) for newly created user then you get all users who were members till 2008-10-02.You can prepare similar queries for getting users who were members in a specific date range.Similarly the following select returns all users who's Login wasn't Disabled right before specified date:select k.Name from ladmin.KUAF as k where k.Type = 0 and'''1''' = (select TOP (1) CONVERT(nvarchar,a.Value1) from ladmin.DAuditNew as a where a.AuditID = 10 and a.UserID = k.ID and (CONVERT(nvarchar,a.Value1) = '''1''' or CONVERT(nvarchar,a.Value1) = '''0''') and a.ValueKey is null and a.AuditDate < '2009-02-05' order by a.AuditDate desc) or k.Type = 0 and not exists (select TOP (1) CONVERT(nvarchar,a.Value1) from ladmin.DAuditNew as a where a.AuditID = 10 and a.UserID = k.ID and CONVERT(nvarchar,a.Value1) = '''0''' and a.ValueKey is null and a.AuditDate < '2009-02-05' order by a.AuditDate desc) The second condition is here for showing users, who has no similar record in DAuditNew and thus they were never disabled.Hope this helps. Maybe this is not the most DB-optimalized solution, but it works for me...Best RegardsDavid