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'

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

Limiting notification to Contacts based on the Notification field, is not automatic.
It must be done explicitly in the OQL.

• 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: retrieve all the non closed WorkOrders which are assigned to a Team to which the current user belongs, and not assigned to any agent yet.
• Compatibility: iTop 2.0

SELECT WorkOrder AS w 
  JOIN Team AS t ON w.team_id=t.id 
  JOIN lnkPersonToTeam AS l ON l.team_id=t.id 
WHERE l.person_id =:current_contact_id 
  AND w.status != 'closed'
  AND w.agent_id = 0

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

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

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'

Who has renewed its user password within the last 2 months

SELECT p FROM CMDBChangeOpSetAttributeScalar AS sa
  JOIN CMDBChange AS c ON sa.change = c.id 
  JOIN UserLocal AS u ON sa.objkey =u.id
  JOIN Person AS p ON u.contactid = p.id
WHERE sa.objclass ='UserLocal' 
  AND sa.attcode = 'password_renewed_date' 
  AND c.date > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 MONTH),'%Y-%m-01 00:00:00')

Here is how to get who has obsolete a Server and when:

• Compatibility: 3.x only as before there was no user_id within a CMDBChange
• But a simpler query without the JOIN User… and JOIN Person… has always been possible, the who was then limited to the friendlyname of the user who did it, stored as a string.

SELECT s,sa,p FROM CMDBChangeOpSetAttributeScalar AS sa
  JOIN CMDBChange AS c ON sa.change = c.id 
  JOIN Server AS s ON sa.objkey=s.id
  JOIN User AS u ON c.user_id=u.id
  JOIN Person AS p ON u.contactid = p.id
WHERE sa.objclass ='Server' 
  AND sa.attcode = 'status' 
  AND sa.newvalue = 'obsolete'

• Note that this query can return Servers which are no more obsolete.

Get the Person who created the Ticket

• Compatibility: 3.x only as before there was no user_id within a CMDBChange
• But a simpler query without the JOIN User… and JOIN Person… has always been possible, the who was then limited to the friendlyname of the user who did it, stored as a string.

SELECT co, p FROM CMDBChangeOp AS co
  JOIN CMDBChange AS c ON co.change=c.id
  JOIN Ticket AS t ON co.objkey=t.id
  JOIN USER AS u ON c.user_id=u.id
  JOIN Person AS p ON u.contactid=p.id
WHERE co.finalclass="CMDBChangeOpCreate"

• Audit is becoming pretty slow, have we changed some rules in the past month, which could be the root cause of this degradation

SELECT sa, c FROM CMDBChangeOpSetAttributeText AS sa
JOIN CMDBChange AS c ON sa.change = c.id
WHERE sa.objclass ='AuditRule'
AND sa.attcode = 'query'
AND c.date > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y-%m-01 00:00:00')

Searching for User Request, updated since the beginning of 2024, which have never been reassigned from one team to another team.

SELECT UserRequest AS u WHERE u.last_update > '2024-01-01'
AND id IN (SELECT t FROM CMDBChangeOpSetAttributeScalar AS sa 
  JOIN CMDBChange AS c ON sa.change = c.id
  JOIN UserRequest AS t ON sa.objkey = t.id
WHERE sa.objclass ='UserRequest' 
  AND sa.attcode = 'team_id' 
  AND sa.oldvalue != '0'
  AND t.last_update > '2024-01-01')

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

Brought by iTop 2.7, OQL now supports this syntax, which allows queries which were not possible before

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

In some case you may want the OQL to return a result which would depend on a condition related to the current user. For example if he has a particular profile, then the proposed caller_id of a Ticket would be different.

How to prevent a Portal User with Allowed Organizations to see Tickets belonging to sub-organizations of his Allowed Organizations.

• Out of the box, iTop cascade the access rights on sub-organizations of a User Allowed Organizations, and in 3.1.0 there are no way to prevent this.
• The below OQL scope is a workaround for Portal users to be strictly limited to their Allowed Organizations owned objects.
• Cautious: doing this will prevent a Portal User without Allowed Organizations to see any Tickets

SELECT Ticket AS T
  JOIN Organization AS O ON T.org_id=O.id
  JOIN URP_UserOrg AS AO ON AO.allowed_org_id=O.id
  JOIN USER AS U ON AO.userid = U.id
WHERE U.contactid = :current_contact_id
  AND T.finalclass IN ('UserRequest', 'Incident')

It is possible to set some complex filter tag on the Ticket.caller_id, assuming you have a iTop 2.7.0 or above

• Either: current user does not have the Portal user profile
• Or: current user has Portal power user profile
  • In which case, he can select any person in the Customer organization
• Otherwise he can only select himself as the caller

SELECT Person WHERE 
(org_id=:this->org_id 
AND 
  (:current_contact_id NOT IN (SELECT Person AS p 
                           JOIN USER AS u ON u.contactid=p.id
                           JOIN URP_UserProfile AS uup ON uup.userid=u.id 
                           JOIN URP_Profiles AS up ON uup.profileid=up.id 
                           WHERE up.name='Portal user' AND p.id = :current_contact_id
                           ) 
  OR :current_contact_id IN (SELECT Person AS p 
                           JOIN USER AS u ON u.contactid=p.id
                           JOIN URP_UserProfile AS uup ON uup.userid=u.id 
                           JOIN URP_Profiles AS up ON uup.profileid=up.id 
                           WHERE up.name='Portal power user' AND p.id = :current_contact_id
                           )
  )
)
OR (org_id=:this->org_id AND id=:current_contact_id)

• current user has the Support Agent profile
  • In which case, he can select any person in the Customer organization
• Otherwise he can only select himself as the caller

SELECT Person WHERE 
(org_id=:this->org_id 
AND :current_contact_id IN (SELECT Person AS p 
                           JOIN USER AS u ON u.contactid=p.id
                           JOIN URP_UserProfile AS uup ON uup.userid=u.id 
                           JOIN URP_Profiles AS up ON uup.profileid=up.id 
                           WHERE up.name='Support Agent' AND p.id = :current_contact_id
                           )
)
OR (org_id=:this->org_id AND id=:current_contact_id)