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

Consider browsing to iTop 3.1 documentation

OQL examples

Relationships

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'

Contacts of a ticket

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

Objects without xxx

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

Unassigned WorkOrders

  • 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
:current_contact_id represent the Person id which is linked to the current user, the one connected and executing the above query. You can also set a criteria using another attribute than the id of the current_contact. The syntax is :current_contact→attribute where attribute is a valid field code within the class Person.

Empty Teams

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
  )

UserRequest without CIs

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

Dates

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

Hierarchies

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

TagSet & EnumSet

FAQs with domains

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'

UNION

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 an organization

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

Searching on History

Changes made by someone

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,…

Persons reactivated

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'

User password renewed

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

Obsolete Servers

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'
If your user running this query, is not allowed to see the User class, you won't get any result. But another more tricky situation can occur, if your user does not have selected View obsolete data in his preferences
  • Note that this query can return Servers which are no more obsolete.

Creator of objects

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"

Returning multiple classes

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

Identifying duplicates

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

Select in Select

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

Persons without Users

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

Badly configured Users

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

OQL user dependent

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.

caller_id filter

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

Example 1 Power users can create Ticket for others

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

Example 2: Only Support Agent can create ticket for others

  • 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)
3_0_0/oql/oql_examples.txt · Last modified: 2022/05/20 12:16 (external edit)
Back to top
Contact us