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"

Find objects with an empty external key

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)

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

FAQs with domains (TagSet)

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'

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 != ''

Displaying multiple objects fields

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

Sidebar