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

let
    Source = AzureDevOps.Views("[Your Organization Name]", "[Your Project Name]", [ApiVersion = 2]),
    View_Table = Source{[Id="[Your Analytics View ID]",Name="[Your Analytics View Name]"],Kind="View"}[Data],

    // Add a unique key for each daily snapshot
    AddSnapshotKey = Table.AddColumn(View_Table, "SnapshotKey", each Text.Combine({Text.From([Work Item Id], "en-US"), Text.From([Date], "en-US")}), type text),
    
    // Lag the state column to detect changes
    AddPreviousState = Table.Buffer(Table.Sort(AddSnapshotKey,{{"Work Item Id", Order.Ascending}, {"Date", Order.Ascending}})),
    ShiftedList = {null} & List.RemoveLastN(AddPreviousState[State],1),
    AddLagColumn = Table.ToColumns(AddPreviousState) & {ShiftedList},
    FinalTable = Table.FromColumns(AddLagColumn, Table.ColumnNames(AddPreviousState) & {"PreviousState"}),
    
    // Filter to rows where the state actually changed or it's the creation event
    FilterStateChanges = Table.SelectRows(FinalTable, each [State] <> [PreviousState] or [PreviousState] = null),

    // --- Event 1: Work Item Created ---
    WorkItemCreated = Table.SelectRows(FilterStateChanges, each [PreviousState] = null),
    AddActivity_Created = Table.AddColumn(WorkItemCreated, "ActivityName", each "Work Item Created"),
    SelectCols_Created = Table.SelectColumns(AddActivity_Created, {"Work Item Id", "ActivityName", "Created Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_Created = Table.RenameColumns(SelectCols_Created,{{"Created Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Infer events from State Changes ---
    StateChangeEvents = Table.SelectRows(FilterStateChanges, each [PreviousState] <> null),
    AddActivity_StateChange = Table.AddColumn(StateChangeEvents, "ActivityName", each 
        if [State] = "Approved" then "Work Item Approved"
        else if [State] = "Committed" or [State] = "Active" or [State] = "In Progress" and [PreviousState] <> "In QA" then "Development Started"
        else if [State] = "Resolved" then "Development Completed"
        else if [State] = "In QA" or [State] = "Testing" then "QA Testing Started"
        else if [State] = "In Progress" and [PreviousState] = "In QA" then "QA Testing Failed"
        else if [State] = "QA Approved" then "QA Testing Completed"
        else if [State] = "In UAT" then "UAT Started"
        else if [State] = "UAT Approved" then "UAT Approved"
        else if [State] = "Deployed" then "Deployed to Production" // Assuming a state reflects deployment
        else if [State] = "Closed" then "Work Item Closed"
        else if [State] = "Done" then "Work Item Closed"
        else if [State] = "Removed" or [State] = "Cancelled" then "Work Item Cancelled"
        else null),
    FilterNullActivities = Table.SelectRows(AddActivity_StateChange, each ([ActivityName] <> null)),
    SelectCols_StateChange = Table.SelectColumns(FilterNullActivities, {"Work Item Id", "ActivityName", "Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_StateChange = Table.RenameColumns(SelectCols_StateChange,{{"Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Combine all event tables ---
    CombinedEvents = Table.Combine({RenameCols_Created, RenameCols_StateChange}),
    
    // --- Placeholder Events Not Captured by State Changes ---
    // These activities require different data sources (OData/API) and are not reliably captured via Work Item history alone.
    // Placeholder logic is omitted as it would be speculative.

    // --- Final Formatting ---
    RenameCaseID = Table.RenameColumns(CombinedEvents,{{"Work Item Id", "DevelopmentItem"}}),
    AddSourceSystem = Table.AddColumn(RenameCaseID, "SourceSystem", each "Azure DevOps"),
    AddLastUpdate = Table.AddColumn(AddSourceSystem, "LastDataUpdate", each DateTime.LocalNow()),
    ChangeTypes = Table.TransformColumnTypes(AddLastUpdate,{{"DevelopmentItem", type text}, {"EventTime", type datetime}, {"LastDataUpdate", type datetime}}),

    // Final column selection and ordering
    SelectFinalColumns = Table.SelectColumns(ChangeTypes, {"DevelopmentItem", "ActivityName", "EventTime", "SourceSystem", "LastDataUpdate", "Assigned To", "Work Item Type", "State", "Priority", "TeamName"})

in
    SelectFinalColumns

# =============================================================================
# Configuration Variables
# =============================================================================
$organizationUrl = "https://dev.azure.com/[Your-Organization-Name]"
$projectName = "[Your-Project-Name]"
$personalAccessToken = "[Your-Personal-Access-Token]"
$startDate = "2023-01-01"
$endDate = "2023-06-30"
$outputCsvPath = "C:\temp\ado_process_mining_log.csv"

# =============================================================================
# Script Body
# =============================================================================

# --- Helper function to create a standardized event object ---
function New-EventLogEntry {
    param (
        $DevelopmentItem, $ActivityName, $EventTime, $SourceSystem, $LastDataUpdate,
        $EndTime, $AssignedTo, $WorkItemType, $State, $Priority, $TeamName, $DevelopmentCycleTime, $IsRework
    )
    return [PSCustomObject]@{ 
        DevelopmentItem = $DevelopmentItem; ActivityName = $ActivityName; EventTime = $EventTime; SourceSystem = $SourceSystem; 
        LastDataUpdate = $LastDataUpdate; EndTime = $EndTime; AssignedTo = $AssignedTo; WorkItemType = $WorkItemType; State = $State; 
        Priority = $Priority; TeamName = $TeamName; DevelopmentCycleTime = $DevelopmentCycleTime; IsRework = $IsRework 
    }
}

# --- Main script execution ---
try {
    $startTime = Get-Date
    Write-Host "Starting Azure DevOps data extraction at $startTime"

    # Base64 encode the PAT for the authorization header
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes((":{0}" -f $personalAccessToken)))
    $headers = @{Authorization = ("Basic {0}" -f $base64AuthInfo)}

    # Timestamp for the extraction run
    $lastDataUpdate = Get-Date -Format 'yyyy-MM-ddTHH:mm:ssZ'
    
    # Initialize list to hold all event log entries
    $eventLog = [System.Collections.Generic.List[object]]::new()

    # Step 1: Query for all work item IDs in the specified date range using WIQL
    Write-Host "Querying for work items between $startDate and $endDate..."
    $wiqlQuery = @{
        query = "SELECT [System.Id] FROM workitems WHERE [System.TeamProject] = '$projectName' AND [System.ChangedDate] >= '$startDate' AND [System.ChangedDate] <= '$endDate'"
    }
    $wiqlUrl = "$organizationUrl/$projectName/_apis/wit/wiql?api-version=6.0"
    $wiqlResponse = Invoke-RestMethod -Uri $wiqlUrl -Method Post -Body (ConvertTo-Json $wiqlQuery) -ContentType "application/json" -Headers $headers
    $workItemIds = $wiqlResponse.workItems.id
    Write-Host ("Found {0} work items to process." -f $workItemIds.Count)

    # Step 2: Iterate through each work item and get its full revision history
    $processedCount = 0
    foreach ($id in $workItemIds) {
        $processedCount++
        Write-Host ("Processing Work Item {0} ({1}/{2})..." -f $id, $processedCount, $workItemIds.Count)

        $revisionsUrl = "$organizationUrl/_apis/wit/workitems/$id/revisions?`$expand=relations&api-version=6.0"
        $revisionsResponse = Invoke-RestMethod -Uri $revisionsUrl -Method Get -Headers $headers
        
        $previousRevision = $null
        $stateHistory = [System.Collections.Generic.List[string]]::new()

        # Loop through each revision to generate events
        foreach ($revision in $revisionsResponse.value) {
            $currentFields = $revision.fields
            $previousFields = $previousRevision.fields
            $eventTime = [datetime]$currentFields.'System.ChangedDate'
            $currentState = $currentFields.'System.State'

            # Common attributes for this revision
            $commonAttribs = @{
                DevelopmentItem = $id
                EventTime = $eventTime.ToString('yyyy-MM-ddTHH:mm:ssZ')
                SourceSystem = 'Azure DevOps'
                LastDataUpdate = $lastDataUpdate
                AssignedTo = $currentFields.'System.AssignedTo'.displayName
                WorkItemType = $currentFields.'System.WorkItemType'
                State = $currentState
                Priority = $currentFields.'Microsoft.VSTS.Common.Priority'
                TeamName = $currentFields.'System.TeamProject'
            }
            
            # --- Activity Detection Logic ---

            # Work Item Created (always the first revision)
            if ($revision.rev -eq 1) {
                $isRework = $false
                $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Work Item Created' -IsRework $isRework))
                $stateHistory.Add($currentState)
            } else {
                $previousState = $previousFields.'System.State'
                
                # Detect rework if we re-enter a state
                $isRework = $stateHistory.Contains($currentState)

                # State Change Activities
                if ($currentState -ne $previousState) {
                    $transition = "$previousState -> $currentState"
                    $activityName = switch ($transition) {
                        'New -> Approved' { 'Work Item Approved' }
                        'Approved -> Active' { 'Development Started' }
                        'Approved -> In Progress' { 'Development Started' }
                        'Active -> Resolved' { 'Development Completed' }
                        'In Progress -> Resolved' { 'Development Completed' }
                        'Resolved -> In QA' { 'QA Testing Started' }
                        'Resolved -> Testing' { 'QA Testing Started' }
                        'In QA -> Active' { 'QA Testing Failed' }
                        'Testing -> In Progress' { 'QA Testing Failed' }
                        'In QA -> Ready for UAT' { 'QA Testing Completed' }
                        'Testing -> UAT Approved' { 'QA Testing Completed' }
                        'Ready for UAT -> In UAT' { 'UAT Started' }
                        'In UAT -> UAT Approved' { 'UAT Approved' }
                        # Handle final states
                        default {
                           if ($currentState -eq 'Closed' -or $currentState -eq 'Done') { 'Work Item Closed' }
                           elseif ($currentState -eq 'Removed' -or $currentState -eq 'Cancelled') { 'Work Item Cancelled' }
                        }
                    }
                    if ($activityName) {
                        $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName $activityName -IsRework $isRework))
                    }
                    $stateHistory.Add($currentState)
                }
            }

            # Artifact Relation Activities (Pull Requests, Builds, Deployments)
            # This checks for newly added relations compared to the previous revision
            $currentRelations = $revision.relations
            $previousRelations = $previousRevision.relations
            if ($currentRelations) {
                $newRelations = Compare-Object -ReferenceObject $previousRelations -DifferenceObject $currentRelations -Property url -PassThru | Where-Object { $_.SideIndicator -eq '=>' }
                foreach ($rel in $newRelations) {
                    # Pull Request Created/Completed
                    if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Pull Request') {
                        # Note: A more robust check would query the PR URL for its actual creation/completion date
                        $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Pull Request Created' -IsRework $false))
                        # Placeholder for completion, real logic would track PR status over time
                        # For simplicity, we assume another event represents completion.
                    }
                    # Build Succeeded
                    if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Build') {
                         $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Build Succeeded' -IsRework $false))
                    }
                    # Deployed to Production
                     if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Integrated in release environment') {
                         if ($rel.url -like '*Production*') { # Simple check for production deployment
                            $eventLog.Add((New-EventLogEntry @commonattribs -ActivityName 'Deployed to Production' -IsRework $false))
                         }
                    }
                }
            }

            $previousRevision = $revision
        }
    }

    # Step 3: Post-processing to sort events and calculate EndTime/CycleTime
    Write-Host "Sorting event log and calculating derived attributes..."
    $sortedEvents = $eventLog | Sort-Object DevelopmentItem, EventTime
    
    $finalLog = for ($i = 0; $i -lt $sortedEvents.Count; $i++) {
        $currentEvent = $sortedEvents[$i]
        $nextEvent = if ($i + 1 -lt $sortedEvents.Count) { $sortedEvents[$i + 1] } else { $null }
        
        # Calculate EndTime
        if ($nextEvent -and $currentEvent.DevelopmentItem -eq $nextEvent.DevelopmentItem) {
            $currentEvent.EndTime = $nextEvent.EventTime
        } else {
            $currentEvent.EndTime = $null
        }
        $currentEvent # Output to the final log
    }

    # Step 4: Export to CSV
    Write-Host ("Exporting {0} events to {1}..." -f $finalLog.Count, $outputCsvPath)
    $finalLog | Export-Csv -Path $outputCsvPath -NoTypeInformation -Encoding UTF8

    $endTime = Get-Date
    Write-Host "Extraction completed successfully at $endTime"
    Write-Host ("Total execution time: {0}" -f ($endTime - $startTime))
}
catch {
    Write-Error "An error occurred during extraction: $_"
    exit 1
}

import os
import pandas as pd
from datetime import datetime, timezone
from azure.devops.connection import Connection
from msrest.authentication import BasicAuthentication

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your values ---
ORGANIZATION_URL = class="hl-string">'https://dev.azure.com/[Your Organization]'
PROJECT_NAME = class="hl-string">'[Your Project Name]'
PERSONAL_ACCESS_TOKEN = class="hl-string">'[Your Personal Access Token]'

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
class=class="hl-string">"hl-comment"># Date range for work items to fetch (based on creation date)
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-12-31'

class=class="hl-string">"hl-comment"># Work item types to include in the extraction
WORK_ITEM_TYPES = [class="hl-string">'User Story', class="hl-string">'Bug', class="hl-string">'Feature']

class=class="hl-string">"hl-comment"># --- State Mapping: Customize this to match your project's workflow ---
class=class="hl-string">"hl-comment"># Maps a work itemclass="hl-string">'s 'State' field to a standardized Activity Name
STATE_MAP = {
    class="hl-string">'New': class="hl-string">'Work Item Created',
    class="hl-string">'Approved': class="hl-string">'Work Item Approved',
    class="hl-string">'Committed': class="hl-string">'Development Started',
    class="hl-string">'Active': class="hl-string">'Development Started',
    class="hl-string">'In Progress': class="hl-string">'Development Started',
    class="hl-string">'Resolved': class="hl-string">'Development Completed',
    class="hl-string">'Ready for Test': class="hl-string">'Development Completed',
    class="hl-string">'In QA': class="hl-string">'QA Testing Started',
    class="hl-string">'Testing': class="hl-string">'QA Testing Started',
    class="hl-string">'QA Approved': class="hl-string">'QA Testing Completed',
    class="hl-string">'Ready for UAT': class="hl-string">'QA Testing Completed',
    class="hl-string">'In UAT': class="hl-string">'UAT Started',
    class="hl-string">'UAT Approved': class="hl-string">'UAT Approved',
    class="hl-string">'Ready for Release': class="hl-string">'UAT Approved',
    class="hl-string">'Done': class="hl-string">'Work Item Closed',
    class="hl-string">'Closed': class="hl-string">'Work Item Closed',
    class="hl-string">'Removed': class="hl-string">'Work Item Cancelled',
    class="hl-string">'Cancelled': class="hl-string">'Work Item Cancelled'
}

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    print(class="hl-string">"Connecting to Azure DevOps...")
    credentials = BasicAuthentication(class="hl-string">'', PERSONAL_ACCESS_TOKEN)
    connection = Connection(base_url=ORGANIZATION_URL, creds=credentials)
    wit_client = connection.clients.get_work_item_tracking_client()
    git_client = connection.clients.get_git_client()
    build_client = connection.clients.get_build_client()

    print(fclass="hl-string">"Fetching work items from {START_DATE} to {END_DATE}...")
    wiql_query = fclass="hl-string">"""
        SELECT [System.Id]
        FROM workitems
        WHERE [System.TeamProject] = '{PROJECT_NAME}'
        AND [System.WorkItemType] IN ({', '.join([f"'{t}'" for t in WORK_ITEM_TYPES])})
        AND [System.CreatedDate] >= '{START_DATE}'
        AND [System.CreatedDate] <= '{END_DATE}'
        ORDER BY [System.Id]
    """

    wiql_results = wit_client.query_by_wiql({class="hl-string">'query': wiql_query}).work_items
    if not wiql_results:
        print(class="hl-string">"No work items found for the specified criteria.")
        return

    work_item_ids = [item.id for item in wiql_results]
    print(fclass="hl-string">"Found {len(work_item_ids)} work items. Fetching details and history...")

    all_events = []
    last_data_update = datetime.now(timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># Process work items in batches to avoid API limits
    batch_size = 200
    for i in range(0, len(work_item_ids), batch_size):
        batch_ids = work_item_ids[i:i + batch_size]
        work_items = wit_client.get_work_items(ids=batch_ids, expand=class="hl-string">'All')
        
        for item in work_items:
            events = process_work_item(item, wit_client, git_client, build_client, last_data_update)
            all_events.extend(events)
        print(fclass="hl-string">"Processed batch {i // batch_size + 1}...")

    if not all_events:
        print(class="hl-string">"No events were generated. Check your state mappings and data.")
        return

    print(class="hl-string">"Creating event log DataFrame...")
    df = pd.DataFrame(all_events)
    df = df.sort_values(by=[class="hl-string">'DevelopmentItem', class="hl-string">'EventTime'])
    
    class=class="hl-string">"hl-comment"># Calculate EndTime (start of next event for the same case)
    df[class="hl-string">'EndTime'] = df.groupby(class="hl-string">'DevelopmentItem')[class="hl-string">'EventTime'].shift(-1)
    
    class=class="hl-string">"hl-comment"># Final column selection and ordering
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Azure DevOps'
    df = df[[class="hl-string">'DevelopmentItem', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EndTime', class="hl-string">'AssignedTo', class="hl-string">'WorkItemType', 
             class="hl-string">'State', class="hl-string">'Priority', class="hl-string">'TeamName', class="hl-string">'IsRework', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate']]

    output_filename = class="hl-string">'azure_devops_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

def process_work_item(item, wit_client, git_client, build_client, last_data_update):
    events = []
    case_id = item.id
    work_item_type = item.fields.get(class="hl-string">'System.WorkItemType')
    team_name = item.fields.get(class="hl-string">'System.AreaPath')
    priority = item.fields.get(class="hl-string">'Microsoft.VSTS.Common.Priority')

    revisions = wit_client.get_revisions(work_item_id=case_id, expand=class="hl-string">'Fields')
    
    class=class="hl-string">"hl-comment"># Track states to detect rework
    seen_states = set()
    
    class=class="hl-string">"hl-comment"># Revision 0 / Initial Creation
    rev0 = revisions[0]
    initial_state = rev0.fields.get(class="hl-string">'System.State')
    events.append({
        class="hl-string">'DevelopmentItem': case_id,
        class="hl-string">'ActivityName': class="hl-string">'Work Item Created',
        class="hl-string">'EventTime': rev0.fields.get(class="hl-string">'System.CreatedDate').isoformat(),
        class="hl-string">'AssignedTo': rev0.fields.get(class="hl-string">'System.AssignedTo', {}).get(class="hl-string">'displayName'),
        class="hl-string">'WorkItemType': work_item_type,
        class="hl-string">'State': initial_state,
        class="hl-string">'Priority': priority,
        class="hl-string">'TeamName': team_name,
        class="hl-string">'IsRework': False,
        class="hl-string">'LastDataUpdate': last_data_update
    })
    seen_states.add(initial_state)
    
    class=class="hl-string">"hl-comment"># Process state changes and other updates
    for i in range(1, len(revisions)):
        prev_rev = revisions[i-1]
        curr_rev = revisions[i]

        prev_state = prev_rev.fields.get(class="hl-string">'System.State')
        curr_state = curr_rev.fields.get(class="hl-string">'System.State')
        event_time = curr_rev.fields.get(class="hl-string">'System.ChangedDate').isoformat()
        assigned_to = curr_rev.fields.get(class="hl-string">'System.AssignedTo', {}).get(class="hl-string">'displayName')

        class=class="hl-string">"hl-comment"># State Change Event
        if prev_state != curr_state and curr_state in STATE_MAP:
            activity = STATE_MAP[curr_state]
            is_rework = curr_state in seen_states
            
            class=class="hl-string">"hl-comment"># Specific logic for QA failure (rework)
            if (prev_state in [class="hl-string">'In QA', class="hl-string">'Testing', class="hl-string">'In UAT'] and 
                curr_state in [class="hl-string">'Active', class="hl-string">'In Progress', class="hl-string">'Committed']):
                activity = class="hl-string">'QA Testing Failed'
                is_rework = True
            
            events.append({
                class="hl-string">'DevelopmentItem': case_id,
                class="hl-string">'ActivityName': activity,
                class="hl-string">'EventTime': event_time,
                class="hl-string">'AssignedTo': assigned_to,
                class="hl-string">'WorkItemType': work_item_type,
                class="hl-string">'State': curr_state,
                class="hl-string">'Priority': priority,
                class="hl-string">'TeamName': team_name,
                class="hl-string">'IsRework': is_rework,
                class="hl-string">'LastDataUpdate': last_data_update
            })
            seen_states.add(curr_state)

    class=class="hl-string">"hl-comment"># Process linked artifacts like Pull Requests from the final item state
    if item.relations:
        for rel in item.relations:
            if rel.attributes.get(class="hl-string">'name') == class="hl-string">'Pull Request':
                try:
                    pr_id = int(rel.url.split(class="hl-string">'/')[-1])
                    pr = git_client.get_pull_request_by_id(pr_id, PROJECT_NAME)
                    events.append({
                        class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Pull Request Created',
                        class="hl-string">'EventTime': pr.creation_date.isoformat(), class="hl-string">'AssignedTo': pr.created_by.display_name,
                        class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority, 
                        class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                    })
                    if pr.status == class="hl-string">'completed' and pr.last_merge_commit is not None:
                        merge_commit = git_client.get_commit(pr.last_merge_commit.commit_id, PROJECT_NAME, pr.repository.id)
                        events.append({
                            class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Pull Request Completed',
                            class="hl-string">'EventTime': merge_commit.committer.date.isoformat(), class="hl-string">'AssignedTo': merge_commit.committer.name,
                            class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority, 
                            class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                        })
                except Exception as e:
                    print(fclass="hl-string">"Warning: Could not process PR link for WI {case_id}: {e}")

            if rel.attributes.get(class="hl-string">'name') == class="hl-string">'Build':
                try:
                    build_id = int(rel.url.split(class="hl-string">'/')[-1])
                    build = build_client.get_build(PROJECT_NAME, build_id)
                    if build.status == class="hl-string">'completed' and build.result == class="hl-string">'succeeded':
                         events.append({
                            class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Build Succeeded',
                            class="hl-string">'EventTime': build.finish_time.isoformat(), class="hl-string">'AssignedTo': None,
                            class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority,
                            class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                        })
                except Exception as e:
                    print(fclass="hl-string">"Warning: Could not process Build link for WI {case_id}: {e}")

    class=class="hl-string">"hl-comment"># Note: class="hl-string">'Deployed to Production' is highly specific to release pipeline setup.
    class=class="hl-string">"hl-comment"># This part requires custom logic to check release pipeline logs linked to the work item.
    class=class="hl-string">"hl-comment"># A placeholder is added here; you would need to implement get_deployment_events.
    class=class="hl-string">"hl-comment"># deployment_events = get_deployment_events(case_id, item)
    class=class="hl-string">"hl-comment"># events.extend(deployment_events)

    return events

if __name__ == class="hl-string">'__main__':
    main()

WITH source_events AS (
    SELECT
        [DevelopmentItem],
        [WorkItemType],
        [AssignedTo],
        [Priority],
        [TeamName],
        [State],
        [PreviousState],
        [EventType],
        [EventTime],
        [EndTime],
        [SourceEventId],
        [LastDataUpdate],
        [PullRequestId],
        [PullRequestStatus],
        [BuildResult],
        [DeploymentEnvironment],
        [DeploymentResult]
    FROM [Your source event table]
    WHERE [EventTime] >= '[Your start date parameter]'
      AND [EventTime] < '[Your end date parameter]'
      AND [ProjectName] = '[Your project filter]'
),
activity_events AS (
    SELECT [DevelopmentItem], 'Work Item Created' AS [ActivityName], [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit) AS [IsRework], [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'WorkItemCreated'

    UNION ALL
    SELECT [DevelopmentItem], 'Work Item Approved', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('approved', 'ready for dev', 'ready for development')

    UNION ALL
    SELECT [DevelopmentItem], 'Development Started', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(CASE WHEN LOWER(COALESCE([PreviousState], '')) IN ('resolved', 'ready for test', 'in qa', 'testing') THEN 1 ELSE 0 END AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('active', 'in progress', 'committed')

    UNION ALL
    SELECT [DevelopmentItem], 'Pull Request Created', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'PullRequestCreated'
       OR ([PullRequestId] IS NOT NULL AND LOWER(COALESCE([PullRequestStatus], '')) IN ('created', 'active'))

    UNION ALL
    SELECT [DevelopmentItem], 'Pull Request Completed', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'PullRequestCompleted'
       OR ([PullRequestId] IS NOT NULL AND LOWER(COALESCE([PullRequestStatus], '')) IN ('completed', 'merged'))

    UNION ALL
    SELECT [DevelopmentItem], 'Build Succeeded', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'BuildCompleted'
      AND LOWER(COALESCE([BuildResult], '')) IN ('succeeded', 'success', 'successful')

    UNION ALL
    SELECT [DevelopmentItem], 'Development Completed', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('resolved', 'ready for test')

    UNION ALL
    SELECT [DevelopmentItem], 'QA Testing Started', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('in qa', 'testing', 'qa testing')

    UNION ALL
    SELECT [DevelopmentItem], 'QA Testing Failed', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(1 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([PreviousState], '')) IN ('in qa', 'testing', 'qa testing')
      AND LOWER(COALESCE([State], '')) IN ('active', 'in progress')

    UNION ALL
    SELECT [DevelopmentItem], 'QA Testing Completed', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('ready for uat', 'qa approved')

    UNION ALL
    SELECT [DevelopmentItem], 'UAT Started', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('in uat', 'uat testing')

    UNION ALL
    SELECT [DevelopmentItem], 'UAT Approved', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([PreviousState], '')) IN ('in uat', 'uat testing')
      AND LOWER(COALESCE([State], '')) IN ('uat approved', 'ready for release')

    UNION ALL
    SELECT [DevelopmentItem], 'Deployed to Production', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] IN ('DeploymentCompleted', 'ReleaseCompleted')
      AND LOWER(COALESCE([DeploymentEnvironment], '')) IN ('production', 'prod')
      AND LOWER(COALESCE([DeploymentResult], '')) IN ('succeeded', 'success', 'successful')

    UNION ALL
    SELECT [DevelopmentItem], 'Work Item Closed', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('closed', 'done')

    UNION ALL
    SELECT [DevelopmentItem], 'Work Item Cancelled', [EventTime], [EndTime], [AssignedTo], [WorkItemType], [State], [Priority], [TeamName], CAST(0 AS bit), [LastDataUpdate], [SourceEventId]
    FROM source_events
    WHERE [EventType] = 'StateChanged'
      AND LOWER(COALESCE([State], '')) IN ('removed', 'cancelled', 'canceled')
),
deduplicated_events AS (
    SELECT
        [DevelopmentItem],
        [ActivityName],
        [EventTime],
        [EndTime],
        [AssignedTo],
        [WorkItemType],
        [State],
        [Priority],
        [TeamName],
        [IsRework],
        'Azure DevOps' AS [SourceSystem],
        [LastDataUpdate],
        ROW_NUMBER() OVER (
            PARTITION BY [DevelopmentItem], [ActivityName], [EventTime], COALESCE([SourceEventId], '')
            ORDER BY [LastDataUpdate] DESC
        ) AS [RowNumber]
    FROM activity_events
)
SELECT
    [DevelopmentItem],
    [ActivityName],
    [EventTime],
    [EndTime],
    [SourceSystem],
    [LastDataUpdate],
    [AssignedTo],
    [WorkItemType],
    [State],
    [Priority],
    [TeamName],
    [IsRework]
FROM deduplicated_events
WHERE [RowNumber] = 1
  AND [DevelopmentItem] IS NOT NULL
  AND [ActivityName] IN (
      'Work Item Created',
      'Work Item Approved',
      'Development Started',
      'Pull Request Created',
      'Pull Request Completed',
      'Build Succeeded',
      'Development Completed',
      'QA Testing Started',
      'QA Testing Failed',
      'QA Testing Completed',
      'UAT Started',
      'UAT Approved',
      'Deployed to Production',
      'Work Item Closed',
      'Work Item Cancelled'
  )
ORDER BY [DevelopmentItem], [EventTime], [ActivityName];