You are browsing the documentation for iTop 2.4 which is not the current version.

Consider browsing to iTop 2.6 documentation

OQL examples

Team members

  • 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"

Current week

  • 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))

Previous week

  • 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))

Previous month

  • 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')

Organization hierarchies and locations

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

Opened tickets

  • 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')
In a UNION query, iTop determines automatically the lowest common ancestor for the selected classes amongst all the queries.

Thus, in the above query, the results will be displayed as objects of the class Ticket.

Contacts of a ticket

  • 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 != ''
2_4_0/oql/oql_examples.txt · Last modified: 2018/12/19 11:40 (external edit)
Back to top
Contact us