ソフトウェア開発ライフサイクルデータ``テンプレート

/*
This extraction method uses the ServiceNow report builder UI. The following sections describe the configuration for each report that must be created and exported.
The exported data must then be manually combined and transformed into a single event log file.
*/

---
-- REPORT 1: Item Creation Events
---
Report_Name: ProcessMind - Item Creation Events
Source_Table: rm_story
Report_Type: List
Columns:
  - Number                    (maps to DevelopmentItem)
  - sys_created_on            (maps to EventTime)
  - 'Development Item Created' (create a formula or static column for ActivityName)
  - Assigned to               (maps to AssignedDeveloper)
  - Priority                  (maps to DevelopmentItemPriority)
  - State                     (maps to DevelopmentItemState)
  - cmdb_ci                   (maps to ModuleComponentAffected)
  - Type                      (maps to DevelopmentItemType)
  - Assignment group          (maps to AssignmentGroup)
Filters:
  - sys_created_on ON Last 6 months

---
-- REPORT 2: Inferred State Change Events
---
Report_Name: ProcessMind - State Change Events
Source_Table: sys_audit
Report_Type: List
Columns:
  - documentkey               (maps to DevelopmentItem)
  - sys_created_on            (maps to EventTime)
  - newvalue                  (maps to ActivityName, requires translation)
  - user                      (maps to AssignedDeveloper)
ActivityName_Mapping_Logic (Example):
  - WHEN newvalue IS '[Your Design State]' THEN 'Design Started'
  - WHEN newvalue IS '[Your In Progress State]' THEN 'Development Started'
  - WHEN newvalue IS '[Your QA State]' THEN 'QA Testing Started'
  - WHEN oldvalue IS '[Your QA State]' AND newvalue IS '[Your In Progress State]' THEN 'Rework Identified'
  - WHEN oldvalue IS '[Your QA State]' AND newvalue IS '[Your UAT State]' THEN 'QA Testing Completed'
  - WHEN newvalue IS '[Your UAT State]' THEN 'UAT Started'
  - WHEN newvalue IS '[Your UAT Approved State]' THEN 'UAT Approved'
  - WHEN newvalue IS '[Your Release Ready State]' THEN 'Prepared For Release'
  - WHEN newvalue IS '[Your Cancelled State]' THEN 'Development Item Cancelled'
Filters:
  - tablename = 'rm_story'
  - fieldname = 'state'
  - sys_created_on ON Last 6 months

---
-- REPORT 3: Code Commit Events
---
Report_Name: ProcessMind - Commit Events
Source_Table: sn_devops_commit
Report_Type: List
Columns:
  - work_item.number          (maps to DevelopmentItem)
  - commit_time               (maps to EventTime)
  - 'Code Committed'          (create a formula or static column for ActivityName)
  - author.name               (maps to AssignedDeveloper)
Filters:
  - commit_time ON Last 6 months

---
-- REPORT 4: Build Events
---
Report_Name: ProcessMind - Build Events
Source_Table: sn_devops_build
Report_Type: List
Columns:
  - work_item.number          (maps to DevelopmentItem)
  - start_time                (maps to EventTime)
  - 'Build Triggered'         (create a formula or static column for ActivityName)
Filters:
  - start_time ON Last 6 months

---
-- REPORT 5: Deployment Events
---
Report_Name: ProcessMind - Deployment Events
Source_Table: sn_devops_deployment
Report_Type: List
Columns:
  - work_item.number          (maps to DevelopmentItem)
  - start_time                (maps to EventTime for 'Started' activities)
  - end_time                  (maps to EventTime for 'Completed' or 'Failed' activities)
  - state                     (maps to ActivityName, requires translation)
ActivityName_Mapping_Logic:
  - WHEN state IS 'in_progress' THEN 'Deployment to Production Started'
  - WHEN state IS 'successful' THEN 'Deployed to Production'
  - WHEN state IS 'failed' THEN 'Deployment Failed'
Filters:
  - start_time ON Last 6 months
  - [Filter for production deployments based on your environment configuration]

---
-- Additional events like 'Code Review Performed' may require a separate report
-- on a table like `sn_devops_pull_request` if available and configured.
---

WITH DevItems AS (
  -- This CTE selects the base set of development items to analyze.
  -- Add other tables like rm_defect or rm_enhancement here using UNION ALL if needed.
  SELECT
    s.sys_id,
    s.number,
    s.sys_created_on,
    s.sys_updated_on,
    s.assigned_to,
    s.priority,
    s.state,
    s.cmdb_ci, -- Module/Component Affected
    s.sys_class_name, -- Development Item Type
    s.assignment_group,
    DATEDIFF(second, s.sys_created_on, s.closed_at) AS cycle_time_seconds
  FROM rm_story s
  WHERE s.sys_created_on >= '2023-01-01' -- *** Placeholder: Set your desired start date ***
),
StateChanges AS (
  -- This CTE unnests the audit trail for state changes, which are used for inferred activities.
  SELECT
    a.documentkey AS item_sys_id,
    a.sys_created_on AS change_time,
    a.oldvalue,
    a.newvalue,
    -- *** Placeholder: Define the numeric order of your states to detect rework. Adjust values and names. ***
    CASE a.oldvalue
      WHEN '1' THEN 1 -- Open
      WHEN '[Your 'Design' State Value]' THEN 2
      WHEN '[Your 'In Progress' State Value]' THEN 3
      WHEN '[Your 'In QA' State Value]' THEN 4
      WHEN '[Your 'In UAT' State Value]' THEN 5
      ELSE 0
    END AS old_state_order,
    CASE a.newvalue
      WHEN '1' THEN 1 -- Open
      WHEN '[Your 'Design' State Value]' THEN 2
      WHEN '[Your 'In Progress' State Value]' THEN 3
      WHEN '[Your 'In QA' State Value]' THEN 4
      WHEN '[Your 'In UAT' State Value]' THEN 5
      ELSE 0
    END AS new_state_order
  FROM sys_audit a
  WHERE a.tablename = 'rm_story' AND a.fieldname = 'state'
)
-- 1. Development Item Created
SELECT
  i.number AS DevelopmentItem,
  'Development Item Created' AS ActivityName,
  i.sys_created_on AS EventTime,
  'ServiceNow DevOps' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  us.name AS AssignedDeveloper,
  i.priority AS DevelopmentItemPriority,
  i.state AS DevelopmentItemState,
  ci.name AS ModuleComponentAffected,
  i.sys_class_name AS DevelopmentItemType,
  grp.name AS AssignmentGroup,
  i.cycle_time_seconds AS DevelopmentItemCycleTime,
  CAST(0 AS BIT) AS IsRework
FROM DevItems i
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id
LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id
LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id

UNION ALL

-- 2. Design Started
SELECT i.number, 'Design Started', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''Design'' State Value]' -- *** Placeholder: Adjust state value ***

UNION ALL

-- 3. Development Started
SELECT i.number, 'Development Started', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''In Progress'' State Value]' AND sc.old_state_order < 3 -- *** Placeholder: Adjust state value and order ***

UNION ALL

-- 4. Code Committed
SELECT i.number, 'Code Committed', c.committed_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_commit c JOIN DevItems i ON c.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id

UNION ALL

-- 5. Build Triggered
SELECT i.number, 'Build Triggered', b.start_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_build b JOIN sn_devops_commit_build cb ON b.sys_id = cb.build JOIN sn_devops_commit c ON cb.commit = c.sys_id JOIN DevItems i ON c.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id

UNION ALL

-- 6. Code Review Performed
SELECT i.number, 'Code Review Performed', pr.closed_at, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_pull_request pr JOIN DevItems i ON pr.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE pr.state = 'merged' -- Or 'closed', depending on process

UNION ALL

-- 7. QA Testing Started
SELECT i.number, 'QA Testing Started', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''In QA'' State Value]' -- *** Placeholder: Adjust state value ***

UNION ALL

-- 8. Rework Identified
SELECT i.number, 'Rework Identified', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(1 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.new_state_order < sc.old_state_order AND sc.new_state_order > 1 -- Moved to an earlier state

UNION ALL

-- 9. QA Testing Completed
SELECT i.number, 'QA Testing Completed', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.oldvalue = '[Your ''In QA'' State Value]' AND sc.new_state_order > sc.old_state_order -- *** Placeholder: Adjust state value ***

UNION ALL

-- 10. UAT Started
SELECT i.number, 'UAT Started', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''In UAT'' State Value]' -- *** Placeholder: Adjust state value ***

UNION ALL

-- 11. UAT Approved
SELECT i.number, 'UAT Approved', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.oldvalue = '[Your ''In UAT'' State Value]' AND sc.new_state_order > sc.old_state_order -- *** Placeholder: Adjust state value ***

UNION ALL

-- 12. Prepared For Release
SELECT i.number, 'Prepared For Release', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''Ready for Release'' State Value]' -- *** Placeholder: Adjust state value ***

UNION ALL

-- 13. Deployment to Production Started
SELECT i.number, 'Deployment to Production Started', se.start_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_step_execution se JOIN sn_devops_artifact_build sab ON se.deployable = sab.sys_id JOIN sn_devops_commit_build cb ON sab.build = cb.build JOIN sn_devops_commit c ON cb.commit = c.sys_id JOIN DevItems i ON c.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE se.stage_name = 'Production' -- *** Placeholder: Adjust stage name ***

UNION ALL

-- 14. Deployed to Production
SELECT i.number, 'Deployed to Production', se.end_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_step_execution se JOIN sn_devops_artifact_build sab ON se.deployable = sab.sys_id JOIN sn_devops_commit_build cb ON sab.build = cb.build JOIN sn_devops_commit c ON cb.commit = c.sys_id JOIN DevItems i ON c.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE se.stage_name = 'Production' AND se.result = 'SUCCESS' -- *** Placeholder: Adjust stage name and result value ***

UNION ALL

-- 15. Deployment Failed
SELECT i.number, 'Deployment Failed', se.end_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, i.state, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM sn_devops_step_execution se JOIN sn_devops_artifact_build sab ON se.deployable = sab.sys_id JOIN sn_devops_commit_build cb ON sab.build = cb.build JOIN sn_devops_commit c ON cb.commit = c.sys_id JOIN DevItems i ON c.work_item = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE se.stage_name = 'Production' AND se.result = 'FAILURE' -- *** Placeholder: Adjust stage name and result value ***

UNION ALL

-- 16. Development Item Cancelled
SELECT i.number, 'Development Item Cancelled', sc.change_time, 'ServiceNow DevOps', GETDATE(), us.name, i.priority, sc.newvalue, ci.name, i.sys_class_name, grp.name, i.cycle_time_seconds, CAST(0 AS BIT) 
FROM StateChanges sc JOIN DevItems i ON sc.item_sys_id = i.sys_id 
LEFT JOIN sys_user us ON i.assigned_to = us.sys_id LEFT JOIN cmdb_ci ci ON i.cmdb_ci = ci.sys_id LEFT JOIN sys_user_group grp ON i.assignment_group = grp.sys_id
WHERE sc.newvalue = '[Your ''Cancelled'' State Value]'; -- *** Placeholder: Adjust state value ***