OQL Statment To Find Duplicates In iTop

[James Smith]

I am needing assistance with writing an OQL statement that will...
- List a person's name and the PC(s) that they are assigned.
- List ONLY the individuals that have greater than 1 PC.
- List ALL of the PCs that that individual has.

Here is the OQL statement I have come up with:

SELECT p, f
FROM Person AS p
JOIN lnkContactToFunctionalCI AS l ON l.contact_id = p.id
JOIN PC AS f ON l.functionalci_id = f.id

The result is that it will give me a list of the Person and the PC that they are assigned. If the individual has multiple PCs, then they will appear in the results twice, but with different computer names.

(Condensed Results Example)

NAME                             PC NAME
 Agatha Christie                  PC_001 
 Stuart Little                    PC_002 
 Stuart Little                    PC_003 
 Jack Frost                       PC_004 
 Jack Frost                       PC_005 
 Mike Tyson                       PC_006 

(Desired Results Example)

 NAME                            PC NAME
 Stuart Little                   PC_002, PC_003
 Jack Frost                      PC_004, PC_005

NOTE: The formatting doesn't have to be exactly that.
It could even just look like:

 Stuart Little                   PC_002
 Stuart Little                   PC_003
 Jack Frost                      PC_004
 Jack Frost                      PC_005

NOTE #2: If the desired results cannot be achieved using OQL within iTop due to it not supporting aggregate functions (GROUP BY, COUNT, etc.), then any suggestions for SQL will be fine as well.

[James Smith]

I have found the answer! Unfortunately, it cannot be done with OQL since iTop does not support the usage of aggregate functions (GROUP BY, ORDER BY, COUNT, etc.).

To get the answer that I was looking for, this has to be done with SQL directly on the database. In my case, I am using PhpMyAdmin.

The the following SQL script produced a result similar to the "Condensed Results Example":

SELECT DISTINCT
    `p`.`id` AS `pid`,
    `p_Contact`.`name` AS `pname`,
    `p_Contact`.`email` AS `pemail`,
    `p`.`first_name` AS `pfirst_name`,
    `p_Contact`.`finalclass` AS `pfinalclass`,
    CAST(CONCAT(COALESCE(`p`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`p_Contact`.`name`, '')) AS CHAR) AS `pfriendlyname`,
    `f`.`id` AS `fid`,
    `f`.`name` AS `fname`,
    `f`.`finalclass` AS `ffinalclass`
FROM person AS p

INNER JOIN(`contact` AS `p_Contact`) ON `p`.`id` = `p_Contact`.`id`
INNER JOIN(`lnkcontacttofunctionalci` AS `l`
           INNER JOIN(`functionalci` AS `f`) ON `l`.`functionalci_id` = `f`.`id`) ON `p`.`id` = `l`.`contact_id`

WHERE (`f`.`finalclass` = 'PC')
ORDER BY `pfriendlyname` ASC;

The following code is what produced a result similar to the "Desired Results Example":

SELECT DISTINCT
    `p`.`id` AS `pid`,
    `p_Contact`.`name` AS `pname`,
    `p_Contact`.`email` AS `pemail`,
    `p`.`first_name` AS `pfirst_name`,
    `p_Contact`.`finalclass` AS `pfinalclass`,
    CAST(CONCAT(COALESCE(`p`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`p_Contact`.`name`, '')) AS CHAR) AS `pfriendlyname`,
    `f`.`id` AS `fid`,
    `f`.`name` AS `fname`,
    `f`.`finalclass` AS `ffinalclass`,
    CAST(GROUP_CONCAT(COALESCE(`f`.`name`, '')) AS CHAR) AS `pdata`
FROM person AS p

INNER JOIN(`contact` AS `p_Contact`) ON `p`.`id` = `p_Contact`.`id`
INNER JOIN(`lnkcontacttofunctionalci` AS `l`
           INNER JOIN(`functionalci` AS `f`) ON `l`.`functionalci_id` = `f`.`id`) ON `p`.`id` = `l`.`contact_id`

WHERE (`f`.`finalclass` = 'PC')
GROUP BY `pfriendlyname`
HAVING COUNT(`pfriendlyname`) > 1
ORDER BY `pfriendlyname` ASC;

The difference between the two is that the second script uses GROUP_CONCAT and then you can group things by the "pfriendlyname" and any instance that has a greater value than 1 is displayed.

I hope this helps other people.