OQL 示例


团队成员


目的: 检索指定团队的所有成员 (团队是通过它的名称指定的)。

  • 兼容性: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"

查找外键为空的对象


  • 外键不能为空,定义时被设置为0,

  • 外键字段的名称 ExternalField 可以为空。

这两个查询将返回同样的结果:

SELECT PhysicalDevice WHERE location_id=0
SELECT PhysicalDevice WHERE ISNULL(location_name)

本周


  • 目的:在本周创建的工单 (星期一到当天)

  • 兼容性: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))

上周


  • 目的:上周的工单 (星期一到当天)

  • 兼容性: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))

上月


  • 目的:上月的工单
  • 兼容性: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'

组织层级和位置


  • 目的:查找一个层级组织的位置

  • 兼容性: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'

注意,由于查询优化算法,这个以前支持的语法不再兼容:

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'

“允许的组织”和在“增强的门户”中失败。

FAQs 域 (标签)


假设你有两个域代码:代理和终端用户,并且一些FAQs有这些标签:

你可以搜索这两个标签的任何一个的FAQs:

  SELECT FAQ WHERE domains MATCHES 'agent enduser'

你可以搜索两个标签都有的FAQs:

  SELECT FAQ WHERE domains MATCHES 'agent' AND domains MATCHES 'enduser'

打开的工单


  • 目的:查找所有打开的工单。在一个简单的SELECT中是不可行的,因为属性“状态”不是在工单类中定义的,而是在每个派生类中定义的。解决方案:使用UNION查询。

  • 兼容性:iTop 2.2.0

 UserRequest     ('rejected', 'resolved', 'closed')

      ('rejected', 'closed')
在UNION查询中,iTop自动确定所有查询中所选类的最低公共祖先。
因此,在查询中,结果以工单类的对象的方式显示。

工单联系人


  • 目的:查找提供商(层次结构)的所有联系人,还有客户的人员(仅顶层)

  • 兼容性: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 != ''

显示多个对象字段

在你的搜索结果时,应包含来自多个类的字段,使用输出规范。

例如,以下查询将只返回Contact类字段:

 Contact  c 
   lnkContactToFunctionalCI  l  l.contact_id = c.id
   FunctionalCI  f  l.functionalci_id = f.id

The little change below will allow to output fields from both Contact and FunctionalCI :

 c, f  Contact  c 
   lnkContactToFunctionalCI  l  l.contact_id = c.id
   FunctionalCI  f  l.functionalci_id = f.id

原文:https://www.itophub.io/wiki/page?id=2_6_0%3Aoql%3Aoql_examples


OQL examples

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"

Find objects with an empty external key

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

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

FAQs with domains (TagSet)

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'

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

 UserRequest     ('rejected', 'resolved', 'closed')

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

Displaying multiple objects fields

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 :

 Contact  c 
   lnkContactToFunctionalCI  l  l.contact_id = c.id
   FunctionalCI  f  l.functionalci_id = f.id

The little change below will allow to output fields from both Contact and FunctionalCI :

 c, f  Contact  c 
   lnkContactToFunctionalCI  l  l.contact_id = c.id
   FunctionalCI  f  l.functionalci_id = f.id
标签:
由 superadmin 在 2020/08/25, 16:24 创建
    

需要帮助?

如果您需要有关XWiki的帮助,可以联系:

深圳市艾拓先锋企业管理咨询有限公司