Jira实战 | 常用MySQL

《Jira策略管理实战手册》: 为管理员提供配置、清理和维护Jira的模板。

文末获取Kindle版和印刷版。

 

直接从数据库获取数据通常比使用JQL（JIRA查询语言）或管理UI更快。此外，数据库还提供了管理UI未提供的额外的信息。

这些信息仅适用服务器应用类型的用户。

 

 

由于数据库类型的不同，并且数据库结构在不同版本上也会不同，因此无法提供保证在你的环境中能够执行的查询，而是和你的数据库团队一起合作，为你的指定数据库设置和查询效率需求制定查询。

这里有一些我常用的MySQL查询。

 

配置元素

使用本节的查询用法，在“发布的静态列表”中查看所建议的“问题类型”、“状态”、“解决结果”、“自定义字段”章节，此外在你创建新的查询之前使用它们来检查相似的值。

 

条目:	Issue Types问题类型
用途:	获取所有问题类型的列表。
示例查询:	SELECT id, pname, description FROM issuetype ORDER BY pname ASC

 

条目:	Statuses状态
用途:	获取所有状态的列表。
示例查询:	SELECT id, pname, description FROM issuestatus ORDER BY pname ASC

 

条目:	Resolutions解决结果
用途:	获取所有解决结果的列表。
示例查询:	SELECT id, pname, description FROM resolution ORDER BY pname ASC

 

条目:	Custom Fields自定义字段
用途:	获取所有自定义字段列表。
示例查询:	SELECT id, cfname, description, defaultvalue FROM customfield   ORDER BY cfname ASC

 

项目与问题

在这个章节使用查询来学习项目，项目的使用、使用者和归档项目。

 

条目:	Project Type项目类型
用途:	列出软件、商业和支持类型项目的组合。
示例查询:	SELECT pkey, pname, projecttype FROM project ORDER BY projecttype ASC

  

条目:	Project Category项目类别
用途:	按自定义类别名称列出你的项目。
示例查询:	SELECT p.pkey, p.pname, pc.cname AS "category" FROM   project p, projectcategory pc, nodeassociation na WHERE   na.source_node_entity = 'Project' AND   na.sink_node_entity = 'ProjectCategory' AND   na.source_node_id = p.id AND pc.id = na.sink_node_id
注记:	不会回传未归类的项目。

 

条目:	Project Issue Count项目问题计数
用途:	查找最活跃(或未活跃)的项目，具有静态问题计数的项目可能是用来存档候选项。
示例查询:	SELECT pname, count(*) AS "total issues" FROM jiraissue i, project p WHERE   i.project = p.id GROUP BY p.pname, i.project

 

条目:	Project Last Issue ID项目最近问题ID
用途:	获取项目中问题最大的ID，上一个查询返回当前的问题计数，返回结果会呈现多少问题被删除。
示例查询:	SELECT p.pkey, p.pname, p.lead, p.pcounter AS "last issue id",   MAX(i.UPDATED) AS "last update", MAX(i.CREATED) AS "last create" FROM   jiraissue i, project p WHERE i.project = p.id GROUP BY p.pname ORDER BY   MAX(i.UPDATED) DESC

 

条目:	Project Last Update or Create项目最近的更新或创建
用途:	获取近期问题的更新或创建信息，没有近期更新的或创建的项目可能会是归档的候选项目。
示例查询:	SELECT p.pkey, p.pname, p.lead, MAX(i.UPDATED) AS "last update",   MAX(i.CREATED) AS "last create" FROM jiraissue i, project p WHERE   i.project = p.id GROUP BY p.pname ORDER BY MAX(i.UPDATED) DESC

 

条目:	All Project Details所有项目详情
用途:	列出每一个项目、其中的键值、问题计数、项目负责人和最近一次问题更新时间。
示例查询:	SELECT i.project AS "ID", min(p.pkey) AS "Key",  min(p.pname) AS   "Project", max(p.lead) AS "Lead", min(p.pcounter) AS "Issue Count",    max(i.updated) AS "Last Update" FROM project p INNER JOIN   jiraissue i ON p.id = i.project GROUP BY i.project ORDER BY min(p.pcounter)

 

条目:	All Project Assignees所有项目经办人
用途:	列出项目的主要用户，发现问题指派给未激活用户并且重新分配。
示例查询:	SELECT DISTINCT(assignee) AS "assignee" FROM jiraissue WHERE   project = [project ID] ORDER BY reporter ASC
注记:	从"项目"表中或将鼠标停在项目"管理页面上的项目"编辑"链接上"来获取项目ID。

 

条目:	All Project Reporters所有项目报告人
用途:	列举项目的主要用户，发现未激活报告人并且更新报告人。
示例查询:	SELECT DISTINCT(reporter) AS reporter FROM jiraissue WHERE   project = [project ID] ORDER BY reporter ASC

 

条目:	All Project Reporters - Time Based所有的项目报告人 – 基于特定时间
用途:	列出自特定日期以来项目主要的用户。
示例查询:	SELECT DISTINCT(reporter) AS reporter FROM jiraissue WHERE   project = [project ID] AND created >= "2017-01-01" ORDER BY reporter ASC

 

条目:	Issues Moved to Another Project问题移动到另一个项目
用途:	查看问题移动过的一种方式，在同一个项目进行多次移动可能表示用户培训可能或该项目已经被其它人遗弃了。
示例查询:	SELECT * FROM moved_issue_key
替代方案:	SELECT id, oldstring AS "original id", newstring AS "current id" FROM   changeitem WHERE oldstring LIKE "[PROJECTKEY]-%" AND newstring != ''
注记:	使用null "新字串"的条件来排除尚未移动的问题。

 

用户和组

使用本节的查询来查找用户、获取用户细节、查看组成员和统计活动。

 

条目:	Inactive Users未激活用户
用途:	查找以前用户的帐户详细信息。，请参阅“取消激活用户帐户”部分。
示例查询:	SELECT id, display_name, user_name, email_address, created_date FROM   cwd_user WHERE active = 0

 

条目:	No Login未登录
用途:	查找从未登录过的用户，出于安全的目的禁用其帐户，并保留在许可证层之中。
示例查询:	SELECT id, display_name, user_name, email_address, created_date, active FROM   cwd_user WHERE id NOT IN (SELECT user_id FROM cwd_user_attributes WHERE   attribute_name = 'login.lastLoginMillis') ORDER BY display_name ASC

 

条目:	No Login Attempt没有登录尝试
用途:	查找从未尝试登录的用户。
示例查询:	SELECT id, display_name, user_name, email_address, created_date, active FROM   cwd_user WHERE active = "1" AND id NOT IN (SELECT user_id FROM   cwd_user_attributes WHERE attribute_name = 'login.totalFailedCount') AND   id NOT IN (SELECT user_id FROM cwd_user_attributes WHERE   attribute_name = 'login.lastLoginMillis') ORDER BY display_name ASC

  

条目:	Failed Login失败登录
用途:	查找尚未成功登录的用户。
示例查询:	SELECT id, display_name, user_name, email_address, created_date,   active FROM cwd_user WHERE active = "1" AND id IN   (SELECT user_id FROM cwd_user_attributes WHERE   attribute_name = 'login.totalFailedCount') AND id NOT IN   (SELECT user_id FROM cwd_user_attributes WHERE   attribute_name = 'login.lastLoginMillis') ORDER BY display_name ASC

 

条目:	Login Details登录细节
用途:	查找所有登录的相关细节。
示例查询:	SELECT u.id, u.display_name, u.user_name, u.email_address, u.created_date, u.active, a.attribute_name, a.attribute_value FROM   cwd_user u, cwd_user_attributes a WHERE   u.id = a.user_id ORDER BY display_name ASC
注记:	在WHERE句型中将结果限制在一个用户。(示例: AND a.user_id = [user id])，Last login意思是指用户在他们的浏览器上选中"记得我"。在这个场景里，只有"last login"（上次登录）属性会更新。Authentication（身份验证）表示输入了用户名称和密码，在这个场景里，"last  login"（上次登录）和"last authentication"（上次身份认证）都会更新。

 

条目:	Users per Role per Project每一项目中每一角色的用户
用途:	衡量角色删除和重命名的影响，了解角色的活跃度，为离开公司的用户发现旧权限。
示例查询:	SELECT project.pkey, projectrole.name AS "role", roletypeparameter AS   "user" FROM project, projectrole, projectroleactor WHERE   project.id = projectroleactor.pid AND   projectroleactor.projectroleid = projectrole.id AND   roletype = 'atlassian-user-role-actor' ORDER BY pkey, role ASC

  

条目:	Most Active Users最活跃的用户
用途:	查看执行最多操作的用户。(示例:  评论)
示例查询:	SELECT COUNT(id) AS "actions", author FROM jiraaction GROUP BY   author ORDER BY id ASC

 

条目:	Group List组列表
用途:	查看所有的组，找出重复、类似以及遗留的组进行清除。
示例查询:	SELECT id, group_name, active, created_date, description FROM   cwd_group ORDER BY group_name ASC

 

条目:	Users in Groups组中的用户
用途:	查看每一个组的用户列表。
示例查询:	SELECT parent_name AS "group", child_name AS "user" FROM   cwd_membership ORDER BY parent_name, child_name ASC

 

条目:	Users in Specific Groups特定组的用户
用途:	查看特定组的用户列表，审核应用管理权限组作为维护活动的一部分。                                                                  详情请参阅"定期维护" 章节。
示例查询:	SELECT child_name AS "username", directory_id FROM cwd_membership WHERE   parent_name = "[jira-administrators]" ORDER BY username ASC

 

条目:	Group Membership Count组成员计数
用途:	计数特定组的活跃度。
示例查询:	SELECT COUNT(*) FROM cwd_membership WHERE   parent_name = "[jira-software-users]"

  

条目:	Inactive Users in Groups组中未激活用户
用途:	查找未激活但是仍旧组成员的用户，针对这些用户进行清理。
示例查询:	SELECT u.id, u.user_name, u.directory_id, d.directory_name, m.id,   m.parent_name AS "group" FROM cwd_user u, cwd_membership m, cwd_directory d   WHERE u.active = 0 AND u.user_name = m.child_name ORDER BY u.user_name,   m.parent_name, d.directory_name ASC

 

筛选器和仪表板

使用本节中的查询来管理筛选器和仪表盘中的记录数。

 

条目:	User Filters用户筛选器
用途:	查找每个用户保存的筛选器，移除已经离开公司的用户的筛选器，在移除筛选器或自定义字段之前先统计其活跃度。
示例查询:	SELECT id, filtername, description, reqcontent, fav_count FROM   searchrequest WHERE username = "[username]"

 

条目:	Similar Filters相同的筛选器
用途:	查找重复的筛选器，鼓励用户共享。
示例查询:	SELECT id, filtername, authorname, description, reqcontent, fav_count FROM   searchrequest WHERE filtername LIKE "%[keyword]%"

 

条目:	Filter Subscriptions筛选器订阅
用途:	查找重复项以及由已经离开公司的用户发送的结果。 Find duplicates and  results sent by users who've left the company.
示例查询:	SELECT * FROM filtersubscription ORDER BY username ASC

  

条目:	Filter Content筛选器内容
用途:	删除特定或常用的查询，了解用户报告的数据。示例:有多少用户正在查询字段X？
示例查询:	SELECT * FROM searchrequest WHERE reqcontent LIKE "%[keyword]%"

 

条目:	Dashboards仪表盘
用途:	统计仪表盘的活跃度，查找并删除(或是更改所有权)已离开公司用户的仪表盘。
示例查询:	SELECT id, pagename, description, username, fav_count FROM   portalpage ORDER BY pagename ASC

 

工作流

使用这节的查询来获取工作流额外的详细信息。

 

条目:	Find Workflows with Embedded Rules查找有嵌入规则的工作流
用途:	当无法手动检查在工作流中每一个单独转换的规则时。
示例查询:	SELECT id, workflowname FROM jiraworkflows WHERE   descriptor LIKE "%<arg name=\"group\">%" OR descriptor LIKE   "%<arg name=\"hidRolesList\">%" OR descriptor LIKE   "%<arg name=\"hidGroupsList\">%" ORDER BY workflowname ASC
注记:	更进一步地说明请参阅"查找嵌入式的工作流行为" 章节。

 

条目:	Workflow Schemes by Project项目的工作流方案
用途:	统计方案的活跃度，请参阅项目与工作流方案的映射。
示例查询:	SELECT p.pname AS "project", w.name AS "workflow scheme" FROM   project p LEFT OUTER JOIN nodeassociation n ON p.id=n.SOURCE_NODE_ID AND   n.sink_node_entity = 'WorkflowScheme' LEFT OUTER JOIN workflowscheme w ON   n.sink_node_id = w.id ORDER BY w.name ASC

 

插件

使用本节中的查询来审核和比较你的插件列表。请参阅在"合并应用程序或重新开始"章节的"插件跟踪"小节 。

 

条目:	Add-ons and Plugins插件
用途:	查找管理UI中未提供的额外插件数据。
示例查询:	SELECT * FROM pluginversion ORDER BY pluginname ASC

 

数据库规格

使用本节的查询来获取数据库和数据库用户的基本信息。

 

条目:	Database Tables数据库表
用途:	了解数据库的存储方式，查找在管理UI未提供的信息，删除插件创建的新表。
示例查询:	SELECT table_name FROM information_schema.tables WHERE   table_schema="[database name]"

 

条目:	Database User Privileges数据库用户权限
用途:	查看每个数据库用户的权限，将审查数据库用户作为维护活动的一部分。                        详细内容请参阅 "定期维护"章节。
示例查询:	SELECT * FROM [database name]

 

条目:	Database Details数据库细节
用途:	查看数据库所有的设置。
示例查询:	SHOW VARIABLES

 

条目:	Database Version数据库版本
用途:	在升级活动之前，验证数据库版本的兼容性。
示例查询:	SHOW VARIABLES LIKE "%version%"

 

查询资源

同样地，上述的查询可能无法适配的数据库或JIRA版本，这些可提供其它方法来帮助你管理你的应用。

 

有关数据库和查询文档，请查看以下链接:

·      jirastrategy.com/link/database-schema

·      jirastrategy.com/link/sample-queries

·      jirastrategy.com/link/sample-queries-crowd

关于《JIRA策略管理实战手册》

本文节选自《JIRA策略管理实战手册》，为Jira管理员提供配置、清理和维护Jira的模板。

 

此实战手册包含：

• 152条建议- 帮助您设置、清理和维护Jira

• 50个工作表，以及其他相关模板、代码片段和示例

• 33个需避免的反面真实案例

• 每个管理区域的最佳实践和注意事项

• 作者作为管理员犯下的十大错误

 

关于翻译，编辑和审稿人

 

	钟冠智 Kerwin   现任Atlassian 大中华区负责人 前CSDN Atlassian Agile DevOps谘询顾问 大中华区首位ACP-100认证专家 Atlassian 中文社区发起人
	Ollie Guan   携程集团 PMO Atlassian Community Champion 2018 Community Perfect 10 awards 2019 Community awards winner 2020 Top Community Answerers
	周琦   优秀的项目管理者及流程改善者曾先后服务于IBM研究院、阿尔卡特朗讯、奇安信（原360企业安全）集团等世界500强企业。
	马亮 现任某大型保险健康管理中心高级运营经理， 负责健康管理平台规划建.设与推广。先后服务于HITACHI集团、NTTDATA集团、奇安信（原360企业安全）集团等世界500强以及独角兽企业。敏捷实践倡导者，Atlassian产品粉丝。2018规模化敏捷峰会组委会成员。 中科院工程师、系统集成高级PM、PMP、CSM、CAL、Prince2、SAFe SA 、CMMI ATM。
	Xudong Liu 刘旭东   中国移动研究院信息技术及安全管理中心高级项目经理，负责企业研发相关工具建设及培训工作。   有近5年的Jira及Confluence的建设及培训经验，在中国移动内部构件多套基于Atlassian产品的研发工具链产品，帮助多个团队实现基于Atlassian产品的研发管理工作。
	闫锦维 长期混迹信息安全圈。曾就职于某高校，作为项目管理负责人承担多项国家密码专项，以项目管理主管身份参与到科研项目管理中。 现就职于奇安信（原360企业安全）集团，担任研发学院运营负责人。
	马畅 现任某大型金融集团高级敏捷教练，为集团子公司提供敏捷转型咨询和实施服务。 曾多年供职于海外，有近十年的敏捷实践经验，精通精益敏捷方法论，熟悉规模化敏捷框架SAFe，认证SAFe Program Consultant。 在跨文化沟通与分布式团队管理方面具备丰富的经验，曾辅导过欧洲，美国和中国等多个团队和项目。 擅长使用以Atlassian产品为核心的DevOps工具链落地组织级敏捷转型，Atlassian中国用户社区创始人和Leader。

 

---