Examples

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

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

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

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