No.of active users per department

Hi,I am looking for a LiveReport which can output department name, active users in that department.I searched the discussions but the problem is all posted SQL queries give the output of department name as 'DefaultGroup' instead of the actual department name of that user. For example, user1 belongs to DeptA. So in the output against this user name, I should see DeptA instead of DefaultGroup.1. Can anyone suggest any way how I can achieve this or is there a way to output the groupname as the department of a user instead of DefaultGroup?2. Also, is there a way to count a user as active is he/she is accessing the top level folders in the enterprise workspace. Example: there are 10 level1 folders in enterprise workspace. So if a user is accessing the data from folder1, then he should be listed to belong to that department.Any help is greatly appreciated.Many Thanks,Atiya.

Find more posts tagged with

Comments

Tim_Hunter

1. You get a users Department witht the following query, replace username with a valid username, this matches the Department in the edit user screenselect u.name, u.mailaddress, g.name from kuaf uinner join kuaf g on g.id = u.groupidwhere u.name = 'username'2. You would need to look in the audit table (I assume you are auditing) for the specific folder id which will give you a list of useridSomething like this, replace 123456789 with the folderid select * from dauditnew da where da.dataid = 123456789and da.auditstr IN ('View', 'Fetch')

Atiya_Sultana

Thanks for the reply Tim.1. I get the department name as DefaultGroup. Please see attached screen shot. And I need names of all active users in all departments.2. The second SQL query doesn't return any result. We have auditing enabled in Livelink.Many Thanks,Atiya.

Atiya_Sultana

Sorry missed the attachment.

Craig_Stanley

Not sure about Question 2. But on point 1 we do this through linking a user id back to our HR system. We have however discussed this issue internally as part of some proposed changes to our system set up and I would be interested to get other people's thoughts. All of our internal users are currently in the Default group and permissions are defined through additional groups which users are members of. As part of a roll out of Records Management we have discussed creating departmental groups for all employees and adding those groups to the Default group so as not to alter the existing permissions model (many objects have permissions linked to teh default group). This will then provide an easier method of reporting Departmental use of Livelink and meet some further RM reporting requirements. Will organising the default group in such a way cause any performance problems?Craig Stanley Information ServicesNorthumbrian Water craig.stanley@nwl.co.uk

praveen_nagarajan

Message from Praveen Nagarajan <praveen@supaisystems.com> via eLink

Hi Atiya,

SELECT c.name AS 'User Name', c.mailaddress, b.name AS 'Group Name'

FROM kuafchildren a, kuaf b, kuaf c

WHERE a.childid

IN ( SELECT id FROM kuaf WHERE deleted = 0 AND type = 0 )

AND a.id = b.id

AND a.childid = c.id

I hope the above query may fetch you the desired result for the first case. Let me know your feedback.

Thanks & Regards

Praveen

On Thu, Jan 28, 2010 at 8:37 PM, eLink Discussion: Open Text Live Reports Discussion <livereportsdiscussion@elinkkc.opentext.com> wrote:

Thanks for the reply Tim. Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2010/01/28 10:06
In reply to: RE No.of active users per department Posted by timothy.hunter.ctr@wpafb.af.mil (Hunter, Tim ) on 2010/01/28 09:34

Thanks for the reply Tim.

1. I get the department name as DefaultGroup. Please see attached screen shot. And I need names of all active users in all departments.

2. The second SQL query doesn't return any result. We have auditing enabled in Livelink.

Many Thanks,
Atiya.
[To reply to this thread, use your normal E-mail reply function.]
Topic: No.of active users per department
Discussion: Open Text Live Reports Discussion
Livelink Server: knowledge-wlweb01
To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Tim_Hunter

Atiya,The department name will match the user settings for department name. You must have department name set as DefaultGroup for that user. Go to users and groups, search for that user and hit edit, that is the only group you will see when you ask the system for department name. It sounds like you have a different definition for department.Praveens reply will get you closer to what you want, which I think is all of a users groupsI think there is a miscommunication of what you want to see and what you are asking for.