OQL examples

• 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”:

• 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 c
FROM
   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 p
FROM
   Person AS p
JOIN
   Organization AS o ON p.org_id = o.id
WHERE
   o.friendlyname = 'TheCustomer' AND p.phone != ''