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

Sidebar