Queus to group to group members with permissions mapping

[sevensins]

Hi!,

I am trying to find an sql query to show queue --> Group --> Group members --> permissions

any help would be much appreciated

Rgds


OTRS 3.2.3 :: ITSM 3.2.2 Running on CentOS 5.7

[sevensins]

though i am not a software engg nor a DBA, just went through the otrs database with navicat tool, came up with the following query and it does provide the required result to some extent, would love to have the permissions come as one horizontal rather than a listing but it works

Code: Select all

SELECT DISTINCT
queue.`name`,
users.first_name,
users.last_name,
users.login,
groups.`name`,
group_user.permission_key
FROM
users ,
group_user ,
groups ,
queue
WHERE
users.id = group_user.user_id AND
group_user.group_id = groups.id AND
queue.group_id = groups.id
ORDER BY
queue.`name` ASC

[crythias]

I'd be using JOINs instead of WHERE, but you probably have what you need.

If you want better, the Admin Gui is there to tell you the same thing.

[sevensins]

thankyou, actually i have over 50+ master queues and numerous sub queues in them with over 200agents with interlacing permissions, just wanted to have a map of what where and who

would much appreciate if you could show me how to use the JOIN as I am a novice in sql currently im using the below

Code: Select all

SELECT DISTINCT
queue.`name`,
groups.`name`,
users.first_name,
users.last_name,
users.login,
group_user.permission_key
FROM
users ,
group_user ,
groups ,
queue
WHERE
users.id = group_user.user_id AND
group_user.group_id = groups.id AND
queue.group_id = groups.id
ORDER BY
queue.`name` ASC,
groups.`name` ASC,
users.login ASC

[crythias]

Code: Select all

SELECT q.name AS queue, u.first_name AS first_name, u.last_name AS last_name, u.login AS login, g.name AS  'group', gu.permission_key AS permision
FROM queue q
LEFT JOIN  `groups` g ON ( q.group_id = g.id ) 
LEFT JOIN group_user gu ON ( gu.group_id = g.id ) 
LEFT JOIN users u ON ( u.id = gu.user_id ) 
ORDER BY queue ASC 

[sevensins]

much appreciated

[sevensins]

As I am a novice, would request patience from the gurus

It would be very helpful to have all the users permission in additional columns in the report, the above query does show results but each permission is in a new row for the same user, would much appreciate if anyone could help me out in this.

[crythias]

Read this: http://dev.mysql.com/tech-resources/art ... page4.html
*a* way to do this is:

Code: Select all

SELECT q.name AS queue, u.first_name AS first_name, u.last_name AS last_name, u.login AS login, g.name AS  'group', SUM(IF(gu.permission_key = "priority", 1,0)) AS `Priority`
, SUM(IF(gu.permission_key = "owner", 1,0)) AS `Owner`
, SUM(IF(gu.permission_key = "create", 1,0)) AS `Create`
, SUM(IF(gu.permission_key = "move_into", 1,0)) AS `MoveInto`
, SUM(IF(gu.permission_key = "ro", 1,0)) AS `RO`
, SUM(IF(gu.permission_key = "note", 1,0)) AS `Note`
, SUM(IF(gu.permission_key = "rw", 1,0)) AS `RW`
FROM queue q
LEFT JOIN  `groups` g ON ( q.group_id = g.id ) 
LEFT JOIN group_user gu ON ( gu.group_id = g.id ) 
LEFT JOIN users u ON ( u.id = gu.user_id ) 
group by queue,login
ORDER BY queue ASC

Note I can't tell you if I missed a privilege within this. If I did, just add it in this list.

IF you don't like 1s or 0s, you can use if

Code: Select all

IF(SUM(IF(gu.permission_key = "rw", 1,0))>0, "rw", "")

I was just too lazy to wrap it.

[sevensins]

excellent, support help much appreciated..