Beispiel: Anzeigen hochgeladener Dateien in der Agentforce-Sitzungsverfolgung

WITH
    params AS (
      SELECT                                                                      
        '019f8b59-2e80-77ca-xxxx-xxxxxxxxxxxx' AS session_id
    ),
    interactionsWithMessages AS (
      SELECT
        I.ssot__AiAgentInteractionType__c AS InteractionType,
        I.ssot__StartTimestamp__c AS InteractionStartTime,
        I.ssot__EndTimestamp__c AS InteractionEndTime,
        I.ssot__TopicApiName__c AS TopicName,
        M.ssot__MessageSentTimestamp__c AS MessageSentTime,
        M.ssot__ContentText__c AS ContextText,
        M.ssot__AiAgentInteractionMessageType__c AS InteractionMessageType
      FROM
        ssot__AiAgentSession__dlm AS S
        JOIN ssot__AiAgentInteraction__dlm AS I ON S.ssot__id__c = I.ssot__aiAgentSessionId__c
        JOIN ssot__AiAgentInteractionMessage__dlm AS M ON I.ssot__id__c = M.ssot__aiAgentInteractionId__c
        JOIN params AS p ON S.ssot__Id__c = p.session_id
    ),
    interactionsWithSteps AS (
      SELECT
        I.ssot__AiAgentInteractionType__c AS InteractionType,                
        I.ssot__StartTimestamp__c AS InteractionStartTime,                        
        I.ssot__EndTimestamp__c AS InteractionEndTime,
        I.ssot__TopicApiName__c AS TopicName,
        St.ssot__AiAgentInteractionStepType__c AS InteractionStepType,
        St.ssot__Name__c AS Name,
        St.ssot__InputValueText__c AS InputValueText,
        St.ssot__OutputValueText__c AS OutputValueText,
        St.ssot__StartTimestamp__c AS InteractionStepStartTime,
        St.ssot__PreStepVariableText__c AS PreStepVariableText,
        St.ssot__PostStepVariableText__c AS PostStepVariableText
      FROM
        ssot__AiAgentSession__dlm AS S
        JOIN ssot__AiAgentInteraction__dlm AS I ON S.ssot__id__c = I.ssot__aiAgentSessionId__c
        JOIN ssot__AiAgentInteractionStep__dlm AS St ON I.ssot__id__c = St.ssot__aiAgentInteractionId__c
        JOIN params AS p ON S.ssot__Id__c = p.session_id
    ),
    -- Attachments, joined through their message (attachment.MessageId__c = message.id)
    interactionsWithAttachments AS (
      SELECT                                                                      
        I.ssot__AiAgentInteractionType__c AS InteractionType,
        I.ssot__StartTimestamp__c AS InteractionStartTime,
        I.ssot__EndTimestamp__c AS InteractionEndTime,                            
        I.ssot__TopicApiName__c AS TopicName,
        M.ssot__MessageSentTimestamp__c AS MessageSentTime,                  
        A.SourceName__c AS AttachmentSource,                                      
        A.FileType__c AS AttachmentFileType,
        A.FilePathName__c AS AttachmentFilePath,
        A.ContentDocumentVersionId__c AS AttachmentContentVersionId
      FROM
        ssot__AiAgentSession__dlm AS S
        JOIN ssot__AiAgentInteraction__dlm AS I ON S.ssot__id__c = I.ssot__aiAgentSessionId__c
        JOIN ssot__AiAgentInteractionMessage__dlm AS M ON I.ssot__id__c = M.ssot__aiAgentInteractionId__c
        JOIN AiAgentMessageAttachment_std__dlm AS A ON M.ssot__id__c = A.MessageId__c
        JOIN params AS p ON S.ssot__Id__c = p.session_id
    ),
    unified AS (
      SELECT
        InteractionType, InteractionStartTime, InteractionEndTime, TopicName,
        MessageSentTime, ContextText, InteractionMessageType,
        CAST(NULL AS VARCHAR) AS InteractionStepType,
        CAST(NULL AS VARCHAR) AS Name,
        CAST(NULL AS VARCHAR) AS InputValueText,
        CAST(NULL AS VARCHAR) AS OutputValueText,
        CAST(NULL AS VARCHAR) AS PreStepVariableText,
        CAST(NULL AS VARCHAR) AS PostStepVariableText,
        CAST(NULL AS VARCHAR) AS AttachmentSource,
        CAST(NULL AS VARCHAR) AS AttachmentFileType,
        CAST(NULL AS VARCHAR) AS AttachmentFilePath,
        CAST(NULL AS VARCHAR) AS AttachmentContentVersionId
      FROM interactionsWithMessages
      UNION ALL                                                                   
      SELECT
        InteractionType, InteractionStartTime, InteractionEndTime, TopicName,
        InteractionStepStartTime AS MessageSentTime,
        CAST(NULL AS VARCHAR) AS ContextText,
        CAST(NULL AS VARCHAR) AS InteractionMessageType,
        InteractionStepType, Name, InputValueText, OutputValueText,
        PreStepVariableText, PostStepVariableText,
        CAST(NULL AS VARCHAR) AS AttachmentSource,
        CAST(NULL AS VARCHAR) AS AttachmentFileType,
        CAST(NULL AS VARCHAR) AS AttachmentFilePath,
        CAST(NULL AS VARCHAR) AS AttachmentContentVersionId
      FROM interactionsWithSteps
      UNION ALL
      SELECT
        InteractionType, InteractionStartTime, InteractionEndTime, TopicName,
        MessageSentTime,
        CAST(NULL AS VARCHAR) AS ContextText,
        CAST(NULL AS VARCHAR) AS InteractionMessageType,
        CAST(NULL AS VARCHAR) AS InteractionStepType,
        CAST(NULL AS VARCHAR) AS Name,                                            
        CAST(NULL AS VARCHAR) AS InputValueText,
        CAST(NULL AS VARCHAR) AS OutputValueText,
        CAST(NULL AS VARCHAR) AS PreStepVariableText,
        CAST(NULL AS VARCHAR) AS PostStepVariableText,
        AttachmentSource, AttachmentFileType, AttachmentFilePath, AttachmentContentVersionId
      FROM interactionsWithAttachments
    )
  SELECT
    TopicName, InteractionType, InteractionStartTime, InteractionEndTime,
    MessageSentTime, InteractionMessageType, ContextText,
    InteractionStepType, Name, InputValueText, OutputValueText,
    PreStepVariableText, PostStepVariableText,
    AttachmentSource, AttachmentFileType, AttachmentFilePath, AttachmentContentVersionId
  FROM unified                                                                    
  ORDER BY MessageSentTime ASC