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

Consider browsing to iTop 3.2 documentation

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 Ticket
WHERE
   start_date > DATE(DATE_SUB(NOW(), INTERVAL 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 DATE_FORMAT(NOW(), '%w') - 1 DAY))
AND 
   start_date > DATE(DATE_SUB(NOW(), INTERVAL 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'
2_0_3/oql/oql_examples.txt ยท Last modified: 2018/12/19 11:40 (external edit)
Back to top
Contact us