User Tools

Site Tools

:: Version 2.3.0 ::

2_3_0:oql:oql_examples
Translations of this page:

Examples

The following examples are useful queries for a specific version of iTop.

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

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_3_0/oql/oql_examples.txt · Last modified: 2016/10/10 11:22 (external edit)

";