• Purpose: retrieve all the members of a given Team (where the Team is specified by its name).
• Compatibility: iTop 2.0

SELECT Person AS p 
  JOIN lnkPersonToTeam AS l1 ON l1.person_id=p.id 
  JOIN Team AS t ON l1.team_id=t.id
  WHERE t.name = 'My team'

• An ExternalKey is never NULL, it is set to 0 when undefined,
• An ExternalField can be NULL.

Those 2 queries will return the same results:

SELECT PhysicalDevice WHERE location_id=0
SELECT PhysicalDevice WHERE ISNULL(location_name)

• Purpose: Get tickets created within the current week (monday to the current day)
• Compatibility: iTop 2.0

SELECT UserRequest
WHERE start_date > 
   DATE(DATE_SUB(NOW(), INTERVAL IF(DATE_FORMAT(NOW(),'%w')=0,6,DATE_FORMAT(NOW(),'%w')-1)DAY))

• Purpose: Get tickets from the previous week (monday to sunday)
• Compatibility: iTop 2.0

SELECT Ticket
WHERE start_date < 
  DATE(DATE_SUB(NOW(), INTERVAL IF(DATE_FORMAT(NOW(),'%w')=0,6,DATE_FORMAT(NOW(),'%w')-1)DAY))
AND start_date > 
  DATE(DATE_SUB(NOW(), INTERVAL IF(DATE_FORMAT(NOW(),'%w')=0,6,DATE_FORMAT(NOW(),'%w')-1)+7 DAY))

• Purpose: Get tickets from the previous month
• Compatibility: iTop 2.0

SELECT Ticket
WHERE
   start_date < DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00')
AND 
   start_date > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y-%m-01 00:00:00')

• Purpose: Find out locations within a hierarchy of organizations
• Compatibility: iTop 2.0

SELECT Location AS s
JOIN Organization AS root ON s.org_id=root.id
JOIN Organization AS child ON child.parent_id BELOW root.id
WHERE child.name = 'TheWorldCompany'

Note that this previously supported syntax is no more compatible, due to query optimization algorithm:

SELECT s FROM Organization AS child
JOIN Organization AS root ON child.parent_id BELOW root.id
JOIN Location AS s ON s.org_id=root.id
WHERE child.name = 'TheWorldCompany'

It fails with “Allowed organizations” and in “Enhanced portal”:

Assuming you have 2 domains with code agent and enduser, and some FAQs with those tags:
You can search for FAQs having either one or the other of those 2 tags:

  SELECT FAQ WHERE domains MATCHES 'agent enduser'

You can search for FAQs having both tags:

  SELECT FAQ WHERE domains MATCHES 'agent' AND domains MATCHES 'enduser'

• Purpose: Find out all opened Tickets. This is not feasible in one simple SELECT because the attribute “status” is not defined in the class Ticket, but in each derived class. Solution: use an UNION query.
• Compatibility: iTop 2.2.0

SELECT UserRequest WHERE status NOT IN ('rejected', 'resolved', 'closed')
UNION
SELECT Change WHERE status NOT IN ('rejected', 'closed')

• Purpose: Find out the contacts of the provider (hierarchy) and the persons of the customer (top level only)
• Compatibility: iTop 2.2.0

SELECT Contact AS c
  JOIN Organization AS child ON c.org_id = child.id
  JOIN Organization AS root ON child.parent_id BELOW root.id
  WHERE root.friendlyname = 'TheProvider'
UNION
SELECT Person AS p
  JOIN Organization AS o ON p.org_id = o.id
  WHERE o.friendlyname = 'TheCustomer' AND p.phone != ''

• Purpose: Notify the persons linked to a Ticket, who are not the caller and are willing to be notified.

  SELECT Person AS p 
    JOIN lnkContactToTicket AS l ON l.contact_id=p.id 
  WHERE l.ticket_id = :this->id
    AND p.id != :this->caller_id
    AND notify='yes'

Let's assume you want to retrieve all field modifications made on a given day, on objects of class Person, by a user “Casimir” using interactive CSV import mechanism. Here is the OQL query you can run to do so.

SELECT CMDBChangeOpSetAttribute AS sa 
  JOIN CMDBChange AS c ON sa.change = c.id 
WHERE c.date > '2019-07-16 00:00:00' 
  AND c.date < '2019-07-17 00:00:00' 
  AND c.origin = 'csv-interactive' 
  AND c.userinfo LIKE '%Casimir%' 
  AND sa.objclass = 'Person'

In the resulting list, check the key which is the id of the modified Person.
To get the previous value, you need to know the field type. It's mapped to one of the children class of CMDBChangeOpSetAttribute:

• There is in general one specific subclass per Attribute Type.
• Except CMDBChangeOpSetAttributeScalar which handles multiple attribute types, those that can be treated as a simple mono-line string. It includes Integer, Decimal, String, Date,…

Here is how to get all Persons which have been reactivated while they were 'inactive', anytime since you've deployed iTop:

SELECT CMDBChangeOpSetAttributeScalar AS sa 
  JOIN CMDBChange AS c ON sa.change = c.id 
WHERE sa.objclass ='Person' 
  AND sa.attcode = 'status' 
  AND sa.oldvalue = 'inactive' 
  AND sa.newvalue = 'active'

When your search result should contains fields from multiple class, use the output specification.

For example the below query will only return Contact class fields :

SELECT Contact AS c 
JOIN lnkContactToFunctionalCI AS l ON l.contact_id = c.id
JOIN FunctionalCI AS f ON l.functionalci_id = f.id

The little change below will allow to output fields from both Contact and FunctionalCI :

SELECT c, f FROM
Contact AS c 
JOIN lnkContactToFunctionalCI AS l ON l.contact_id = c.id
JOIN FunctionalCI AS f ON l.functionalci_id = f.id

Lets assume you want to identify duplicates on employee number within the same organization:

SELECT Person AS p 
 JOIN Organization AS o ON p.org_id = o.id 
 JOIN Person AS p2 ON p2.org_id = o.id 
 WHERE p2.employee_number = p.employee_number AND p.id != p2.id

Search for Teams which have no members

SELECT Team WHERE id NOT IN 
  (
    SELECT Team AS t JOIN lnkPersonToTeam AS l ON l.team_id=t.id
  )

Search for UserRequest which do not have any 'production' PhysicalDevice linked to it

SELECT UserRequest 
  WHERE id NOT IN 
  (
    SELECT UserRequest AS u 
    JOIN lnkFunctionalCIToTicket AS l ON l.ticket_id=u.id 
    JOIN PhysicalDevice AS f ON l.functionalci_id=f.id
    WHERE f.status='production'
  )

Search for UserRequest which do not have 'production' PhysicalDevice neither 'active' ApplicationSolution linked to it

SELECT UserRequest 
  WHERE id NOT IN 
  (
    SELECT UserRequest AS u 
    JOIN lnkFunctionalCIToTicket AS l ON l.ticket_id=u.id 
    JOIN PhysicalDevice AS f ON l.functionalci_id=f.id
    WHERE f.status='production'
 
    UNION
 
    SELECT UserRequest AS u 
    JOIN lnkFunctionalCIToTicket AS l ON l.ticket_id=u.id 
    JOIN ApplicationSolution AS f ON l.functionalci_id=f.id
    WHERE f.status='active'    
  )

Search for active Persons with no enabled User associated

SELECT Person WHERE status='active' 
  AND id NOT IN 
  (
    SELECT Person AS p
      JOIN User AS u ON u.contactid=p.id 
      WHERE u.status='enabled'
  )

Search for active User not allowed on their own organization

SELECT User AS U JOIN Person AS P ON U.contactid=P.id 
  WHERE U.status='enabled' AND U.id NOT IN 
  (
    SELECT User AS U 
      JOIN Person AS P ON U.contactid=P.id 
      JOIN URP_UserOrg AS L ON L.userid = U.id 
      WHERE U.status='enabled' AND L.allowed_org_id = P.org_id
  UNION 
    SELECT User AS U 
      WHERE U.status='enabled' AND U.id NOT IN
      (
        SELECT User AS U 
        JOIN URP_UserOrg AS L ON L.userid = U.id
        WHERE U.status='enabled'
      )
  )

This request search for active Users with a Person associated, and the Person belongs to an organization which is not in the Allowed Organizations of the User and that Allowed Organizations list is not empty (remember an empty list means all organizations are allowed).