Rules (XIF)
[RULE: o365_common_fields]
alter
RecordType = to_integer(RecordType)
| alter
xdm.event.id = Id,
xdm.event.type = if(RecordType = 1, "ExchangeAdmin", RecordType = 2, "ExchangeItem", RecordType = 3, "ExchangeItemGroup", RecordType = 4, "SharePoint", RecordType = 6, "SharePointFileOperation", RecordType = 7, "OneDrive", RecordType = 8, "AzureActiveDirectory", RecordType = 9, "AzureActiveDirectoryAccountLogon", RecordType = 10, "DataCenterSecurityCmdlet", RecordType = 11, "ComplianceDLPSharePoint", RecordType = 13, "ComplianceDLPExchange", RecordType = 14, "SharePointSharingOperation", RecordType = 15, "AzureActiveDirectoryStsLogon", RecordType = 16, "SkypeForBusinessPSTNUsage", RecordType = 17, "SkypeForBusinessUsersBlocked", RecordType = 18, "SecurityComplianceCenterEOPCmdlet", RecordType = 19, "ExchangeAggregatedOperation", RecordType = 20, "PowerBIAudit", RecordType = 21, "CRM", RecordType = 22, "Viva Engage", RecordType = 23, "SkypeForBusinessCmdlets", RecordType = 24, "Discovery", RecordType = 25, "MicrosoftTeams", RecordType = 28, "ThreatIntelligence", RecordType = 29, "MailSubmission", RecordType = 30, "MicrosoftFlow", RecordType = 31, "AeD", RecordType = 32, "MicrosoftStream", RecordType = 33, "ComplianceDLPSharePointClassification", RecordType = 34, "ThreatFinder", RecordType = 35, "Project", RecordType = 36, "SharePointListOperation", RecordType = 37, "SharePointCommentOperation", RecordType = 38, "DataGovernance", RecordType = 39, "Kaizala", RecordType = 40, "SecurityComplianceAlerts", RecordType = 41, "ThreatIntelligenceUrl", RecordType = 42, "SecurityComplianceInsights", RecordType = 43, "MIPLabel", RecordType = 44, "VivaInsights", RecordType = 45, "PowerAppsApp", RecordType = 46, "PowerAppsPlan", RecordType = 47, "ThreatIntelligenceAtpContent", RecordType = 48, "LabelContentExplorer", RecordType = 49, "TeamsHealthcare", RecordType = 50, "ExchangeItemAggregated", RecordType = 51, "HygieneEvent", RecordType = 52, "DataInsightsRestApiAudit", RecordType = 53, "InformationBarrierPolicyApplication", RecordType = 54, "SharePointListItemOperation", RecordType = 55, "SharePointContentTypeOperation", RecordType = 56, "SharePointFieldOperation", RecordType = 57, "MicrosoftTeamsAdmin", RecordType = 58, "HRSignal", RecordType = 59, "MicrosoftTeamsDevice", RecordType = 60, "MicrosoftTeamsAnalytics", RecordType = 61, "InformationWorkerProtection", RecordType = 62, "Campaign", RecordType = 63, "DLPEndpoint", RecordType = 64, "AirInvestigation", RecordType = 65, "Quarantine", RecordType = 66, "MicrosoftForms", RecordType = 67, "ApplicationAudit", RecordType = 68, "ComplianceSupervisionExchange", RecordType = 69, "CustomerKeyServiceEncryption", RecordType = 70, "OfficeNative", RecordType = 71, "MipAutoLabelSharePointItem", RecordType = 72, "MipAutoLabelSharePointPolicyLocation", RecordType = 73, "MicrosoftTeamsShifts", RecordType = 75, "MipAutoLabelExchangeItem", RecordType = 76, "CortanaBriefing", RecordType = 78, "WDATPAlerts", RecordType = 79, "PowerAppsResource", RecordType = 82, "SensitivityLabelPolicyMatch", RecordType = 83, "SensitivityLabelAction", RecordType = 84, "SensitivityLabeledFileAction", RecordType = 85, "AttackSim", RecordType = 86, "AirManualInvestigation", RecordType = 87, "SecurityComplianceRBAC", RecordType = 88, "UserTraining", RecordType = 89, "AirAdminActionInvestigation", RecordType = 90, "MSTIC", RecordType = 91, "PhysicalBadgingSignal", RecordType = 92, "TeamsEasyApprovals", RecordType = 93, "AipDiscover", RecordType = 94, "AipSensitivityLabelAction", RecordType = 95, "AipProtectionAction", RecordType = 96, "AipFileDeleted", RecordType = 97, "AipHeartBeat", RecordType = 98, "MCASAlerts", RecordType = 99, "OnPremisesFileShareScannerDlp", RecordType = 100, "OnPremisesSharePointScannerDlp", RecordType = 101, "ExchangeSearch", RecordType = 102, "SharePointSearch", RecordType = 103, "PrivacyInsights", RecordType = 105, "MyAnalyticsSettings", RecordType = 106, "SecurityComplianceUserChange", RecordType = 107, "ComplianceDLPExchangeClassification", RecordType = 109, "MipExactDataMatch", RecordType = 113, "MS365DCustomDetection", RecordType = 147, "CoreReportingSettings", RecordType = 148, "ComplianceConnector", RecordType = 154, "OMEPortal", RecordType = 157, "MipLabelAnalyticsAuditRecord", RecordType = 164, "ScorePlatformGenericAuditRecord", RecordType = 174, "DataShareOperation", RecordType = 181, "EduDataLakeDownloadOperation", RecordType = 183, "MicrosoftGraphDataConnectOperation", RecordType = 186, "PowerPagesSite", RecordType = 187, "PowerPlatformAdminDlp", RecordType = 188, "PlannerPlan", RecordType = 189, "PlannerCopyPlan", RecordType = 190, "PlannerTask", RecordType = 191, "PlannerRoster", RecordType = 192, "PlannerPlanList", RecordType = 193, "PlannerTaskList", RecordType = 194, "PlannerTenantSettings", RecordType = 195, "ProjectForThewebProject", RecordType = 196, "ProjectForThewebTask", RecordType = 197, "ProjectForThewebRoadmap", RecordType = 198, "ProjectForThewebRoadmapItem", RecordType = 199, "ProjectForThewebProjectSettings", RecordType = 200, "ProjectForThewebRoadmapSettings", RecordType = 202, "MicrosoftTodoAudit", RecordType = 206, "MicrosoftTeamsSensitivityLabelAction", RecordType = 216, "Viva Goals", RecordType = 217, "MicrosoftGraphDataConnectConsent", RecordType = 218, "AttackSimAdmin", RecordType = 230, "TeamsUpdates", RecordType = 231, "PlannerRosterSensitivityLabel", RecordType = 235, "MicrosoftDefenderForIdentityAudit", RecordType = 237, "DefenderExpertsforXDRAdmin", RecordType = 251, "VfamCreatePolicy", RecordType = 252, "VfamUpdatePolicy", RecordType = 253, "VfamDeletePolicy", RecordType = 256, "PowerPlatformAdministratorActivity", RecordType = 257, "Windows365CustomerLockbox", RecordType = 261, "CopilotInteraction", RecordType = 265, "VivaLearning", RecordType = 266, "VivaLearningAdmin", RecordType = 269, "PeopleAdminSettings", RecordType = 275, "OWAAuth", RecordType = 277, "SharePointESignature", RecordType = 278, "Dynamics365BusinessCentral", RecordType = 279, "MeshWorlds", RecordType = 280, "VivaPulseResponse", RecordType = 281, "VivaPulseOrganizer", RecordType = 282, "VivaPulseAdmin", RecordType = 283, "VivaPulseReport", RecordType = 284, "AIAppInteraction", RecordType = 285, "ComplianceDLMExchange", RecordType = 286, "ComplianceDLMSharePoint", RecordType = 287, "ProjectForThewebAssignedToMeSettings", RecordType = 288, "CloudPolicyService", RecordType = 291, "SensitiveInfoDiscovered", RecordType = 292, "InsiderRiskScopedUserInsights", RecordType = 293, "MicrosoftTeamsRetentionLabelAction", RecordType = 294, "AadRiskDetection", RecordType = 295, "AuditSearch", RecordType = 296, "AuditRetentionPolicy", RecordType = 297, "AuditConfig", RecordType = 298, "BackupPolicy", RecordType = 299, "RestoreTask", RecordType = 300, "RestoreItem", RecordType = 301, "BackupItem", RecordType = 302, "URBACAssignment", RecordType = 303, "URBACRole", RecordType = 304, "URBACEnableState", RecordType = 306, "PurviewInsiderRiskCases", RecordType = 307, "PurviewInsiderRiskAlerts", RecordType = 308, "InsiderRiskScopedUsers", RecordType = 310, "CreateCopilotPlugin", RecordType = 311, "UpdateCopilotPlugin", RecordType = 312, "DeleteCopilotPlugin", RecordType = 313, "EnableCopilotPlugin", RecordType = 314, "DisableCopilotPlugin", RecordType = 315, "CreateCopilotWorkspace", RecordType = 316, "UpdateCopilotWorkspace", RecordType = 317, "DeleteCopilotWorkspace", RecordType = 318, "EnableCopilotWorkspace", RecordType = 319, "DisableCopilotWorkspace", RecordType = 320, "CreateCopilotPromptBook", RecordType = 321, "UpdateCopilotPromptBook", RecordType = 322, "DeleteCopilotPromptBook", RecordType = 323, "EnableCopilotPromptBook", RecordType = 324, "DisableCopilotPromptBook", RecordType = 325, "UpdateCopilotSettings", RecordType = 328, "ConnectedAIAppInteraction", RecordType = 329, "PrivaPrivacyConsentOperation", RecordType = 330, "PrivaPrivacyAssessmentOperation", RecordType = 331, "DataCatalogAccessRequests", RecordType = 332, "ComplianceSettingsChange", RecordType = 333, "DataSecurityInvestigation", RecordType = 334, "TeamCopilotInteraction", RecordType = 335, "IRMActivityAuditTrail", RecordType = 336, "SharePointContentSecurityPolicy", RecordType = 337, "CloudUpdateProfileConfig", RecordType = 338, "CloudUpdateTenantConfig", RecordType = 339, "CloudUpdateDeviceConfig", RecordType = 341, "DeviceDiscoverySettingsExclusion", RecordType = 342, "DeviceDiscoverySettingsAuthenticatedScans", RecordType = 344, "DeviceDiscoverySettings", RecordType = 345, "USXWorkspaceOnboarding", RecordType = 346, "VivaGlintAdvancedConfiguration", RecordType = 347, "VivaGlintPulseProgram", RecordType = 348, "VivaGlintPulseProgramRespondentRate", RecordType = 349, "VivaGlintQuestion", RecordType = 350, "VivaGlintRole", RecordType = 351, "VivaGlintRubicon", RecordType = 352, "VivaGlintSupportAccess", RecordType = 353, "VivaGlintSystem", RecordType = 354, "VivaGlintUser", RecordType = 355, "VivaGlintUserGroup", RecordType = 356, "VivaGlintFeedbackProgram", RecordType = 357, "FabricAudit", RecordType = 358, "TrainableClassifier", RecordType = 359, "WebContentFiltering", RecordType = 360, "NoisyAlertPolicy", RecordType = 361, "DataScanClassification", RecordType = 362, "AIInteractionsExport", RecordType = 363, "Microsoft365CopilotScheduledPrompt", RecordType = 364, "PlacesDirectory", RecordType = 365, "SentinelNotebookOnLake", RecordType = 366, "SentinelJob", RecordType = 367, "SentinelKQLOnLake", RecordType = 368, "SentinelLakeOnboarding", RecordType = 369, "SentinelLakeDataOnboarding", RecordType = 370, "SentinelAITool", RecordType = 371, "SentinelGraph", RecordType = 372, "CrossTenantAccessPolicy", RecordType = 373, "OutlookCopilotAutomation", RecordType = 374, "VivaEngageNetworkAssociation", RecordType = 375, "AppAdminActivity", RecordType = 376, "AppSettingsAdminActivity", RecordType = 377, "UniversalPrintPrintJob", RecordType = 378, "VivaAmplifyOutlookSensitivityLabel", RecordType = 379, "AIInteractionsSubscription", RecordType = 380, "AIInteractionsChangeNotification", RecordType = 381, "FilteringMailMetadataExtended", RecordType = 382, "OfficeRestrictedModeAction", RecordType = 383, "CopilotForSecurityTrigger", RecordType = 384, "CopilotAgentManagement", RecordType = 385, "P4AIAssessmentFabricScannerRecord", RecordType = 386, "PlannerGoal", RecordType = 387, "PlannerGoalList"),
xdm.observer.unique_identifier = AppAccessContext -> CorrelationId, // An identifier that can be used to correlate a specific user's actions across Microsoft 365 services.
xdm.source.cloud.project_id = OrganizationId;
[MODEL: dataset="msft_o365_general_raw"]
call o365_common_fields
| alter
EnforcementMode = to_integer(EnforcementMode),
SourceWorkload = to_integer(SourceWorkload),
Scope = to_integer(Scope),
FileSize = to_integer(FileSize),
src_ip = coalesce(ClientIP, UserIp, SenderIp)
| alter
src_ip = arraystring(arraymap(split(src_ip, "."), to_string(to_integer("@element"))), "."),
platform_lowercase = lowercase(to_string(Platform)),
user_type_string = to_string(UserType),
translate_EnforcementMode = if(EnforcementMode = 1, "Audit", EnforcementMode = 2, "Warn (Block with override)", EnforcementMode = 3, "Warn and bypass", EnforcementMode = 4, "Block", EnforcementMode = 5, "Allow (Audit without alerts)"),
filename_name = if(FileName ~= "\.", FileName, null),
filename_extension = arrayindex(regextract(FileName, "\.([^.]+)$"), 0), // extract trailing suffix after last period
members_upn = arraystring(arraymap(Members -> [], "@element" -> UPN), ","),
members_displayname = arraystring(arraymap(Members -> [], "@element" -> DisplayName), "|"),
members_role = arraystring(arraymap(Members -> [], "@element" -> Role), "|"),
Scope_name = if(Scope = 0, "Online", Scope = 1, "Onprem"),
object_id_file_name = arrayindex(regextract(ObjectId, ".+[\\\/]([^\\\/]+)$"), 0),
object_id_file_path = arrayindex(regextract(ObjectId, "(.+)[\\\/][^\\\/]+$"), 0)
| alter
src_ipv4_addresses = arraydistinct(regextract(src_ip, "((?:\d{1,3}\.){3}\d{1,3})")),
src_ipv6_addresses = arraydistinct(regextract(src_ip, "((?:[:]{2}[fF]{4}:(?:\d{1,3}\.){3}\d{1,3})|(?:(?:[a-fA-F\d]{0,4}\:){2,7}[a-fA-F\d]{0,4}))")), // ipv6 could also be an IPv4-mapped IPv6 address
src_interface = arrayindex(regextract(src_ip, "%(\d+)"), 0), // extract interface identifier from the client ip address if such exists
src_port = arrayindex(regextract(src_ip, "[^\:]+:(\d{1,5})$"), 0), // extract port from the client ip address if such exists */
object_id_file_extension = arrayindex(regextract(object_id_file_name, "\.([^.]+)$"), 0) // extract trailing suffix after last period
| alter src_ipv4_public_addresses = arrayfilter(src_ipv4_addresses, not incidr("@element", "10.0.0.0/8") and not incidr("@element", "172.16.0.0/12") and not incidr("@element", "192.168.0.0/16") and not incidr("@element", "127.0.0.0/8") and not incidr("@element", "169.254.0.0/16") and not incidr("@element", "100.64.0.0/10"))
| alter
xdm.alert.description = to_string(AppAccessContext),
xdm.alert.name = InvestigationName,
xdm.alert.original_alert_id = coalesce(InvestigationId, ActionId, AlertEntityId, AlertId),
xdm.alert.original_threat_name = replex(ThreatsAndDetectionTech, "[\"\[\]]", ""),
xdm.alert.severity = Severity,
xdm.alert.subcategory = coalesce(Verdict, InvestigationType, Category, Scope_name),
xdm.auth.privilege_level = if(UserRole = "owner", XDM_CONST.PRIVILEGE_LEVEL_USER, UserRole = "admin", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, members_role = "2", XDM_CONST.PRIVILEGE_LEVEL_GUEST, members_role = "1", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, members_role = "0", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "0|1", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "2|3", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, user_type_string ~= "4|5|6|7|8", XDM_CONST.PRIVILEGE_LEVEL_SYSTEM),
xdm.email.message_id = coalesce(NetworkMessageId, to_string(MessageId), InternetMessageId),
xdm.email.recipients = if(arraystring(arraycreate(TargetUserId), ", ") != "", arraycreate(TargetUserId), arraystring(arraycreate(ReleaseTo), ", ") != "", arraycreate(ReleaseTo), arraymap(Recipients -> [], trim("@element", "\""))),
xdm.email.return_path = P1Sender,
xdm.email.sender = P2Sender,
xdm.email.subject = if(Subject != null, Subject, ItemName != null and RecordType in (43, 71, 72, 73, 75), ItemName), // For MIP events, fallback to ItemName (see https://learn.microsoft.com/en-us/office/office-365-management-api/office-365-management-activity-api-schema#mip-label-schema)
xdm.event.description = coalesce(to_string(ModifiedProperties), to_string(Messages), to_string(FileData), `Fields`, ExtraProperties, Details, Detail, PolicyMatchInfo, to_string(Data), to_string(DataExportType)),
xdm.event.operation = if(Operation in("AttachmentAccess", "FileAccessed", "FileAccessedExtended", "FilePreviewed", "FileVisited", "SensitivityLabeledFileOpened"), XDM_CONST.OPERATION_TYPE_FILE_OPEN, Operation in ("FileCreated", "CreateFile"), XDM_CONST.OPERATION_TYPE_FILE_CREATE, Operation in ("FileDeleted", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "DeleteFileOrBlob"), XDM_CONST.OPERATION_TYPE_FILE_REMOVE, Operation in ("FileModified", "FileModifiedExtended"), XDM_CONST.OPERATION_TYPE_FILE_WRITE, Operation in ("FileRenamed", "RenameFileOrDirectory", "SensitivityLabeledFileRenamed"), XDM_CONST.OPERATION_TYPE_FILE_RENAME, Operation in ("FolderCreated", "CreateFolder"), XDM_CONST.OPERATION_TYPE_DIR_CREATE, Operation in ("FolderDeleted", "DeleteFolder", "FolderDeletedFirstStageRecycleBin", "FolderDeletedSecondStageRecycleBin"), XDM_CONST.OPERATION_TYPE_DIR_REMOVE, Operation in ("FolderModified", "UpdateFolder"), XDM_CONST.OPERATION_TYPE_DIR_WRITE, Operation in ("AddFolderPermissions", "UpdateFolderAccess"), XDM_CONST.OPERATION_TYPE_DIR_CHANGE_MODE, Operation in ("FolderRenamed"), XDM_CONST.OPERATION_TYPE_DIR_RENAME, Operation),
xdm.event.operation_sub_type = coalesce(PolicyAction, Message, Operation),
xdm.event.original_event_type = coalesce(DetectionType, EntityType, to_string(RecordType)),
xdm.event.outcome = if(EnforcementMode = 1, XDM_CONST.OUTCOME_UNKNOWN, to_string(EnforcementMode) ~= "2|3", XDM_CONST.OUTCOME_PARTIAL, EnforcementMode = 4, XDM_CONST.OUTCOME_FAILED, EnforcementMode = 5, XDM_CONST.OUTCOME_SUCCESS, lowercase(ResultStatus) = "partiallysucceeded", XDM_CONST.OUTCOME_PARTIAL, lowercase(ResultStatus) ~= "succe", XDM_CONST.OUTCOME_SUCCESS, ResultStatus = "Failed", XDM_CONST.OUTCOME_FAILED, lowercase(ResultStatus) = "true", XDM_CONST.OUTCOME_SUCCESS, lowercase(ResultStatus) = "false", XDM_CONST.OUTCOME_FAILED),
xdm.event.outcome_reason = coalesce(Reason, translate_EnforcementMode, ResultStatus),
xdm.network.http.url = coalesce(EventDeepLink, DeeplinkURL),
xdm.network.rule = Name,
xdm.observer.action = coalesce(to_string(Actions), Status, translate_EnforcementMode),
xdm.observer.type = if(Workload != null, Workload, SourceWorkload = 0, "SharePoint Online", SourceWorkload = 1, "OneDrive for Business", SourceWorkload = 2, "Microsoft Teams", Source),
xdm.source.application.name = SourceApp,
xdm.source.host.device_id = EntityId,
xdm.source.host.hostname = coalesce(EntityName, DeviceName),
xdm.source.host.ipv4_addresses = if(array_length(src_ipv4_addresses) > 0, src_ipv4_addresses),
xdm.source.host.ipv4_public_addresses = if(array_length(src_ipv4_public_addresses) > 0, src_ipv4_public_addresses),
xdm.source.host.ipv6_addresses = if(array_length(src_ipv6_addresses) > 0, src_ipv6_addresses),
xdm.source.host.os_family = if(platform_lowercase ~= "win|microsoft", XDM_CONST.OS_FAMILY_WINDOWS, platform_lowercase ~= "mac|osx", XDM_CONST.OS_FAMILY_MACOS, platform_lowercase ~= "linux", XDM_CONST.OS_FAMILY_LINUX, platform_lowercase ~= "android", XDM_CONST.OS_FAMILY_ANDROID, platform_lowercase ~= "ios", XDM_CONST.OS_FAMILY_IOS, platform_lowercase ~= "ubuntu", XDM_CONST.OS_FAMILY_UBUNTU, platform_lowercase ~= "debian", XDM_CONST.OS_FAMILY_DEBIAN, platform_lowercase ~= "fedora", XDM_CONST.OS_FAMILY_FEDORA, platform_lowercase ~= "centos", XDM_CONST.OS_FAMILY_CENTOS, platform_lowercase ~= "chrome", XDM_CONST.OS_FAMILY_CHROMEOS, platform_lowercase ~= "solaris", XDM_CONST.OS_FAMILY_SOLARIS, platform_lowercase ~= "scada", XDM_CONST.OS_FAMILY_SCADA),
xdm.source.interface = src_interface,
xdm.source.ipv4 = arrayindex(src_ipv4_addresses, 0),
xdm.source.ipv6 = arrayindex(src_ipv6_addresses, 0),
xdm.source.port = to_integer(src_port),
xdm.source.process.name = if(Application ~= "\.[Ee][Xx][Ee]", Application),
xdm.source.user_agent = UserAgent,
xdm.source.user.identifier = coalesce(to_string(ActorYammerUserId), UserKey),
xdm.source.user.identity_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.IDENTITY_TYPE_USER, user_type_string ~= "4|5|6|7|8", XDM_CONST.IDENTITY_TYPE_MACHINE),
xdm.source.user.upn = coalesce(UserId, ActorUserId),
xdm.source.user.user_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.USER_TYPE_REGULAR, user_type_string ~= "4|5|6|7|8", XDM_CONST.USER_TYPE_SERVICE_ACCOUNT),
xdm.source.user.username = coalesce(UserName, members_displayname),
xdm.target.file.extension = coalesce(FileExtension, filename_extension, object_id_file_extension),
xdm.target.file.file_type = FileType,
xdm.target.file.filename = coalesce(filename_name, object_id_file_name),
xdm.target.file.path = object_id_file_path,
xdm.target.file.sha256 = `Sha256`,
xdm.target.file.size = FileSize,
xdm.target.resource.id = ItemId,
xdm.target.resource.name = if(FormName != null, FormName, ObjectId ~= "\S+", rtrim(ltrim(ObjectId, "<"), ">")),
xdm.target.resource.type = ItemType,
xdm.target.url = Url,
xdm.target.user.identifier = to_string(TargetYammerUserId),
xdm.target.user.upn = members_upn;
[MODEL: dataset="msft_o365_exchange_online_raw"]
call o365_common_fields
| alter
AttachmentSizeInBytes = to_integer(AttachmentSizeInBytes),
InternalLogonType = to_integer(InternalLogonType),
LogonType = to_integer(LogonType),
Scope = to_integer(Scope),
object_id_file_name = arrayindex(regextract(ObjectId, ".+[\\\/]([^\\\/]+)$"), 0),
object_id_file_path = arrayindex(regextract(ObjectId, "(.+)[\\\/][^\\\/]+$"), 0)
| alter
client_ip = coalesce(ClientIPAddress, ClientIP),
user_logon_type = to_string(coalesce(LogonType, InternalLogonType)),
user_type_string = to_string(UserType),
object_id_file_extension = arrayindex(regextract(object_id_file_name, "\.([^.]+)$"), 0) // extract trailing suffix after last period
| alter
client_ipv4_addresses = arraydistinct(regextract(client_ip, "((?:\d{1,3}\.){3}\d{1,3})")),
client_ipv6_addresses = arraydistinct(regextract(client_ip, "((?:[:]{2}[fF]{4}:(?:\d{1,3}\.){3}\d{1,3})|(?:(?:[a-fA-F\d]{0,4}\:){2,7}[a-fA-F\d]{0,4}))")),
client_interface = arrayindex(regextract(client_ip, "%(\d+)"), 0), // extract interface identifier from the ClientIP if such exists
client_port = arrayindex(regextract(client_ip, "[^\:]+:(\d{1,5})$"), 0) // extract port from the ClientIP if such exists */
| alter client_ipv4_public_addresses = arrayfilter(client_ipv4_addresses, not incidr("@element", "10.0.0.0/8") and not incidr("@element", "172.16.0.0/12") and not incidr("@element", "192.168.0.0/16") and not incidr("@element", "127.0.0.0/8") and not incidr("@element", "169.254.0.0/16") and not incidr("@element", "100.64.0.0/10"))
| alter
xdm.alert.description = to_string(AppAccessContext),
xdm.alert.subcategory = if(Scope = 0, "Online", Scope = 1, "Onprem"),
xdm.auth.privilege_level = if(user_logon_type ~= "3|4", XDM_CONST.PRIVILEGE_LEVEL_SYSTEM, user_logon_type ~= "1|6", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, user_logon_type ~= "0|2|5", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "0|1", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "2|3", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, user_type_string ~= "4|5|6|7|8", XDM_CONST.PRIVILEGE_LEVEL_SYSTEM),
xdm.email.attachment.size = AttachmentSizeInBytes,
xdm.email.bcc = arraymap(ExchangeMetaData -> BCC[], replex("@element", "\"", "")),
xdm.email.cc = arraymap(ExchangeMetaData -> CC[], replex("@element", "\"", "")),
xdm.email.data = to_string(OperationProperties),
xdm.email.message_id = coalesce(Item -> InternetMessageId, ExchangeMetaData -> MessageID, MailboxGuid),
xdm.email.origination_timestamp = parse_timestamp( "%Y-%m-%dT%H:%M:%S", ExchangeMetaData -> Sent),
xdm.email.recipients = if(array_length(receivers -> []) > 0, arraymap(receivers -> [], trim("@element", "\"")), arraymap(ExchangeMetaData -> To[], trim("@element", "\""))),
xdm.email.sender = coalesce(ExchangeMetaData -> From, Sender),
xdm.email.subject = coalesce(replex(Item -> Subject, "\"", ""), replex(ExchangeMetaData -> Subject, "\"", ""), to_string(arrayindex(AffectedItems -> [], 0)) -> Subject),
xdm.event.description = coalesce(to_string(AffectedItems), to_string(ModifiedProperties)),
xdm.event.operation = if(Operation in("AttachmentAccess", "FileAccessed", "FileAccessedExtended", "FilePreviewed", "FileVisited", "SensitivityLabeledFileOpened"), XDM_CONST.OPERATION_TYPE_FILE_OPEN, Operation in ("FileCreated", "CreateFile"), XDM_CONST.OPERATION_TYPE_FILE_CREATE, Operation in ("FileDeleted", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "DeleteFileOrBlob"), XDM_CONST.OPERATION_TYPE_FILE_REMOVE, Operation in ("FileModified", "FileModifiedExtended"), XDM_CONST.OPERATION_TYPE_FILE_WRITE, Operation in ("FileRenamed", "RenameFileOrDirectory", "SensitivityLabeledFileRenamed"), XDM_CONST.OPERATION_TYPE_FILE_RENAME, Operation in ("FolderCreated", "CreateFolder"), XDM_CONST.OPERATION_TYPE_DIR_CREATE, Operation in ("FolderDeleted", "DeleteFolder", "FolderDeletedFirstStageRecycleBin", "FolderDeletedSecondStageRecycleBin"), XDM_CONST.OPERATION_TYPE_DIR_REMOVE, Operation in ("FolderModified", "UpdateFolder"), XDM_CONST.OPERATION_TYPE_DIR_WRITE, Operation in ("AddFolderPermissions", "UpdateFolderAccess"), XDM_CONST.OPERATION_TYPE_DIR_CHANGE_MODE, Operation in ("FolderRenamed"), XDM_CONST.OPERATION_TYPE_DIR_RENAME, Operation),
xdm.event.operation_sub_type = Operation,
xdm.event.original_event_type = to_string(RecordType),
xdm.event.outcome = if(ResultStatus = "Succeeded", XDM_CONST.OUTCOME_SUCCESS, ResultStatus = "PartiallySucceeded", XDM_CONST.OUTCOME_PARTIAL, ResultStatus = "Failed", XDM_CONST.OUTCOME_FAILED, ResultStatus ~= "[Tt]rue", XDM_CONST.OUTCOME_SUCCESS, ResultStatus ~= "[Ff]alse", XDM_CONST.OUTCOME_FAILED),
xdm.intermediate.host.hostname = arrayindex(regextract(OriginatingServer, "(\S+)"), 0),
xdm.intermediate.user.identifier = MailboxOwnerSid,
xdm.network.rule = to_string(PolicyDetails),
xdm.observer.type = Workload,
xdm.source.application.name = ClientApplication,
xdm.source.cloud.project = OrganizationName,
xdm.source.host.hostname = ClientMachineName,
xdm.source.host.ipv4_addresses = if(array_length(client_ipv4_addresses) > 0, client_ipv4_addresses),
xdm.source.host.ipv4_public_addresses = if(array_length(client_ipv4_public_addresses) > 0, client_ipv4_public_addresses),
xdm.source.host.ipv6_addresses = if(array_length(client_ipv6_addresses) > 0, client_ipv6_addresses),
xdm.source.interface = client_interface,
xdm.source.ipv4 = arrayindex(client_ipv4_addresses, 0),
xdm.source.ipv6 = arrayindex(client_ipv6_addresses, 0),
xdm.source.port = to_integer(client_port),
xdm.source.process.executable.extension = arraystring(regextract(ClientProcessName, "^\S+\.(\S+)"), ""),
xdm.source.process.name = arraystring(regextract(ClientProcessName, "^(\S+)\.\S+"), ""),
xdm.source.user.identifier = coalesce(LogonUserSid, UserKey),
xdm.source.user.identity_type = if(user_logon_type ~= "3|4", XDM_CONST.IDENTITY_TYPE_MACHINE, user_logon_type ~= "0|1|2|5|6", XDM_CONST.IDENTITY_TYPE_USER, user_type_string ~= "0|1|2|3", XDM_CONST.IDENTITY_TYPE_USER, user_type_string ~= "4|5|6|7|8", XDM_CONST.IDENTITY_TYPE_MACHINE),
xdm.source.user.upn = coalesce(UserId, MailboxOwnerUPN),
xdm.source.user.user_type = if(user_logon_type ~= "3|4", XDM_CONST.USER_TYPE_SERVICE_ACCOUNT, user_logon_type ~= "0|1|2|5|6", XDM_CONST.USER_TYPE_REGULAR, user_type_string ~= "0|1|2|3", XDM_CONST.USER_TYPE_REGULAR, user_type_string ~= "4|5|6|7|8", XDM_CONST.USER_TYPE_SERVICE_ACCOUNT),
xdm.source.user.username = LogonUserDisplayName,
xdm.target.file.extension = object_id_file_extension,
xdm.target.file.filename = object_id_file_name,
xdm.target.file.path = coalesce(object_id_file_path, Item -> ParentFolder.Path),
xdm.target.resource.name = if(ModifiedObjectResolvedName != null, ModifiedObjectResolvedName, ObjectId ~= "\S+", rtrim(ltrim(ObjectId, "<"), ">")),
xdm.target.resource.parent_id = Item -> ParentFolder.id;
[MODEL: dataset="msft_o365_sharepoint_online_raw"]
call o365_common_fields
| alter
Scope = to_integer(Scope),
object_id_file_name = arrayindex(regextract(ObjectId, ".+[\\\/]([^\\\/]+)$"), 0),
object_id_file_path = arrayindex(regextract(ObjectId, "(.+)[\\\/][^\\\/]+$"), 0),
extract_filename_from_objectid = arrayindex(regextract(ObjectId ,"(?:.+\/)(.+)\.\w+"), 0) // With extension
| alter
platform_lowercase = lowercase(to_string(Platform)),
user_type_string = to_string(UserType),
SharePointMetaData_FileName_name = SharePointMetaData -> FileName,
SharePointMetaData_FileName_extension = arrayindex(regextract(SharePointMetaData -> FileName, "\.([^.]+)$"), 0), // extract trailing suffix after last period
object_id_file_extension = arrayindex(regextract(object_id_file_name, "\.([^.]+)$"), 0), // extract trailing suffix after last period
client_ipv4_addresses = arraydistinct(regextract(ClientIP, "((?:\d{1,3}\.){3}\d{1,3})")),
client_ipv6_addresses = arraydistinct(regextract(ClientIP, "((?:[:]{2}[fF]{4}:(?:\d{1,3}\.){3}\d{1,3})|(?:(?:[a-fA-F\d]{0,4}\:){2,7}[a-fA-F\d]{0,4}))")),
client_interface = arrayindex(regextract(ClientIP, "%(\d+)"), 0), // extract interface identifier from the ClientIP if such exists
client_port = arrayindex(regextract(ClientIP, "[^\:]+:(\d{1,5})$"), 0), // extract port from the ClientIP if such exists
default_files = coalesce(DestinationFileName, object_id_file_name, SourceFileName, extract_filename_from_objectid)
| alter client_ipv4_public_addresses = arrayfilter(client_ipv4_addresses, not incidr("@element", "10.0.0.0/8") and not incidr("@element", "172.16.0.0/12") and not incidr("@element", "192.168.0.0/16") and not incidr("@element", "127.0.0.0/8") and not incidr("@element", "169.254.0.0/16") and not incidr("@element", "100.64.0.0/10"))
| alter
xdm.alert.description = to_string(AppAccessContext),
xdm.alert.severity = Severity,
xdm.alert.subcategory = if(Scope = 0, "Online", Scope = 1, "Onprem"),
xdm.auth.auth_method = AuthenticationType,
xdm.auth.privilege_level = if(user_type_string ~= "0|1", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "2|3", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, user_type_string ~= "4|5|6|7|8", XDM_CONST.PRIVILEGE_LEVEL_SYSTEM),
xdm.event.description = to_string(ModifiedProperties),
xdm.event.operation = if(Operation in("AttachmentAccess", "FileAccessed", "FileAccessedExtended", "FilePreviewed", "FileVisited", "SensitivityLabeledFileOpened"), XDM_CONST.OPERATION_TYPE_FILE_OPEN, Operation in ("FileCreated", "CreateFile"), XDM_CONST.OPERATION_TYPE_FILE_CREATE, Operation in ("FileDeleted", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "DeleteFileOrBlob"), XDM_CONST.OPERATION_TYPE_FILE_REMOVE, Operation in ("FileModified", "FileModifiedExtended"), XDM_CONST.OPERATION_TYPE_FILE_WRITE, Operation in ("FileRenamed", "RenameFileOrDirectory", "SensitivityLabeledFileRenamed"), XDM_CONST.OPERATION_TYPE_FILE_RENAME, Operation in ("FolderCreated", "CreateFolder"), XDM_CONST.OPERATION_TYPE_DIR_CREATE, Operation in ("FolderDeleted", "DeleteFolder", "FolderDeletedFirstStageRecycleBin", "FolderDeletedSecondStageRecycleBin"), XDM_CONST.OPERATION_TYPE_DIR_REMOVE, Operation in ("FolderModified", "UpdateFolder"), XDM_CONST.OPERATION_TYPE_DIR_WRITE, Operation in ("AddFolderPermissions", "UpdateFolderAccess"), XDM_CONST.OPERATION_TYPE_DIR_CHANGE_MODE, Operation in ("FolderRenamed"), XDM_CONST.OPERATION_TYPE_DIR_RENAME, Operation),
xdm.event.operation_sub_type = Operation,
xdm.event.original_event_type = to_string(RecordType),
xdm.event.outcome = if(ResultStatus = "Succeeded", XDM_CONST.OUTCOME_SUCCESS, ResultStatus = "PartiallySucceeded", XDM_CONST.OUTCOME_PARTIAL, ResultStatus = "Failed", XDM_CONST.OUTCOME_FAILED, ResultStatus ~= "[Tt]rue", XDM_CONST.OUTCOME_SUCCESS, ResultStatus ~= "[Ff]alse", XDM_CONST.OUTCOME_FAILED),
xdm.network.http.browser = if(BrowserName = null, null, BrowserName ~= "^\s*$", null, BrowserName),
xdm.network.rule = to_string(PolicyDetails),
xdm.network.session_id = AppAccessContext -> AADSessionId,
xdm.observer.action = to_string(ExceptionInfo),
xdm.observer.name = EventSource,
xdm.observer.type = coalesce(Workload, Source),
xdm.source.application.name = coalesce(ApplicationDisplayName, AppAccessContext -> ClientAppName),
xdm.source.host.ipv4_addresses = if(array_length(client_ipv4_addresses) > 0, client_ipv4_addresses),
xdm.source.host.ipv4_public_addresses = if(array_length(client_ipv4_public_addresses) > 0, client_ipv4_public_addresses),
xdm.source.host.ipv6_addresses = if(array_length(client_ipv6_addresses) > 0, client_ipv6_addresses),
xdm.source.host.os_family = if(platform_lowercase ~= "win|microsoft", XDM_CONST.OS_FAMILY_WINDOWS, platform_lowercase ~= "mac|osx", XDM_CONST.OS_FAMILY_MACOS, platform_lowercase ~= "linux|wac", XDM_CONST.OS_FAMILY_LINUX, platform_lowercase ~= "android", XDM_CONST.OS_FAMILY_ANDROID, platform_lowercase ~= "ios", XDM_CONST.OS_FAMILY_IOS, platform_lowercase ~= "ubuntu", XDM_CONST.OS_FAMILY_UBUNTU, platform_lowercase ~= "debian", XDM_CONST.OS_FAMILY_DEBIAN, platform_lowercase ~= "fedora", XDM_CONST.OS_FAMILY_FEDORA, platform_lowercase ~= "centos", XDM_CONST.OS_FAMILY_CENTOS, platform_lowercase ~= "chrome", XDM_CONST.OS_FAMILY_CHROMEOS, platform_lowercase ~= "solaris", XDM_CONST.OS_FAMILY_SOLARIS, platform_lowercase ~= "scada", XDM_CONST.OS_FAMILY_SCADA),
xdm.source.interface = client_interface,
xdm.source.ipv4 = arrayindex(client_ipv4_addresses, 0),
xdm.source.ipv6 = arrayindex(client_ipv6_addresses, 0),
xdm.source.port = to_integer(client_port),
xdm.source.user_agent = if(UserAgent = null, null, UserAgent ~= "^\s*$", null, UserAgent),
xdm.source.user.identifier = UserKey,
xdm.source.user.identity_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.IDENTITY_TYPE_USER, user_type_string ~= "4|5|6|7|8", XDM_CONST.IDENTITY_TYPE_MACHINE),
xdm.source.user.upn = coalesce(UserId, SharePointMetaData -> From),
xdm.source.user.user_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.USER_TYPE_REGULAR, user_type_string ~= "4|5|6|7|8", XDM_CONST.USER_TYPE_SERVICE_ACCOUNT),
xdm.target.file.extension = coalesce(SharePointMetaData_FileName_extension, DestinationFileExtension, SourceFileExtension, object_id_file_extension), // extract trailing suffix after last period
xdm.target.file.filename = if(Operation ~= "AnonymousLinkCreated|AnonymousLinkUpdated", SourceRelativeUrl , Operation ~= "AddedToGroup|RemovedFromGroup", TargetUserOrGroupName, Operation ~= "CompanyLinkCreated|SecureLinkCreated|SharingSet|FileCheckedOut|SharingRevoked|FolderDeleted|FolderModified|FolderRecycled|FolderCopied|FolderRenamed|FolderDeletedFirstStageRecycleBin|FolderRestored|FileDeletedFirstStageRecycleBin|FileRecycled|FileTranscriptRequested|AddedToSecureLink|FolderCreated|FileAccessed|FileCheckOutDiscarded|FileAccessedExtended|FileCopied|FileCheckedIn|FileDownloaded|FilePreviewed|FileVersionsAllDeleted|FileRestored|FileMoved|FileRenamed|FileModified|FileModifiedExtended|FileUploaded|FileDeleted", SourceFileName, Operation ~= "ListUpdated|ListColumnCreated|ListColumnUpdated|ListViewUpdated|ListViewed|ListItemCreated|ListRestored|ListDeleted|ListCreated|ListColumnDeleted", ListTitle, Operation ~= "DLPRuleMatch|DLPRuleUndo", SharePointMetaData_FileName_name, default_files),
xdm.target.file.path = coalesce(SharePointMetaData -> FilePathUrl, object_id_file_path),
xdm.target.file.size = to_integer(SharePointMetaData -> FileSize),
xdm.target.resource.id = Site,
xdm.target.resource.name = if(ObjectId ~= "\S+", rtrim(ltrim(ObjectId, "<"), ">")),
xdm.target.resource.type = ItemType,
xdm.target.url = SiteUrl,
xdm.target.user.groups = if(TargetUserOrGroupName !~= "@", arraycreate(TargetUserOrGroupName)),
xdm.target.user.upn = if(TargetUserOrGroupName ~= "@", TargetUserOrGroupName),
xdm.session_context_id = AppAccessContext -> CorrelationId;
[MODEL: dataset="msft_o365_dlp_raw"]
call o365_common_fields
| alter
Scope = to_integer(Scope),
SharePointMetaData_FileName = SharePointMetaData -> FileName,
SharePointMetaData_FilePathUrl = SharePointMetaData -> FilePathUrl,
EndpointMetaData_EnforcementMode = EndpointMetaData -> EnforcementMode,
EndpointMetaData_FileExtension = EndpointMetaData -> FileExtension, // extract trailing suffix after last period
user_type_string = to_string(UserType),
object_id_file_name = arrayindex(regextract(ObjectId, ".+[\\\/]([^\\\/]+)$"), 0),
object_id_file_path = arrayindex(regextract(ObjectId, "(.+)[\\\/][^\\\/]+$"), 0)
| alter
object_id_file_extension = arrayindex(regextract(object_id_file_name, "\.([^.]+)$"), 0), // extract trailing suffix after last period
translate_EnforcementMode = if(EndpointMetaData_EnforcementMode = "1", "Audit", EndpointMetaData_EnforcementMode = "2", "Warn (Block with override)", EndpointMetaData_EnforcementMode = "3", "Warn and bypass", EndpointMetaData_EnforcementMode = "4", "Block", EndpointMetaData_EnforcementMode = "5", "Allow (Audit without alerts)"),
SharePointMetaData_FileName_name = arrayindex(split(SharePointMetaData_FileName, """\\"""), -1),
SharePointMetaData_FileName_extension = arrayindex(regextract(SharePointMetaData_FileName, "\.([^.]+)$"), 0) // extract trailing suffix after last period
| alter
xdm.alert.description = to_string(AppAccessContext),
xdm.alert.subcategory = if(Scope = 0, "Online", Scope = 1, "Onprem"),
xdm.auth.privilege_level = if(user_type_string ~= "0|1", XDM_CONST.PRIVILEGE_LEVEL_USER, user_type_string ~= "2|3", XDM_CONST.PRIVILEGE_LEVEL_ADMIN, user_type_string ~= "4|5|6|7|8", XDM_CONST.PRIVILEGE_LEVEL_SYSTEM),
xdm.email.attachment.size = coalesce(to_integer(ExchangeMetaData -> FileSize), to_integer(SharePointMetaData -> FileSize)),
xdm.email.bcc = arraymap(ExchangeMetaData -> BCC[], replex("@element", "\"", "")),
xdm.email.cc = arraymap(ExchangeMetaData -> CC[], replex("@element", "\"", "")),
xdm.email.message_id = ExchangeMetaData -> MessageID,
xdm.email.origination_timestamp = parse_timestamp( "%Y-%m-%dT%H:%M:%S", ExchangeMetaData -> Sent),
xdm.email.recipients = arraymap(ExchangeMetaData -> To[], replex("@element", "\"", "")),
xdm.email.sender = ExchangeMetaData -> From,
xdm.email.subject = ExchangeMetaData -> Subject,
xdm.event.operation = if(Operation in("AttachmentAccess", "FileAccessed", "FileAccessedExtended", "FilePreviewed", "FileVisited", "SensitivityLabeledFileOpened"), XDM_CONST.OPERATION_TYPE_FILE_OPEN, Operation in ("FileCreated", "CreateFile"), XDM_CONST.OPERATION_TYPE_FILE_CREATE, Operation in ("FileDeleted", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "DeleteFileOrBlob"), XDM_CONST.OPERATION_TYPE_FILE_REMOVE, Operation in ("FileModified", "FileModifiedExtended"), XDM_CONST.OPERATION_TYPE_FILE_WRITE, Operation in ("FileRenamed", "RenameFileOrDirectory", "SensitivityLabeledFileRenamed"), XDM_CONST.OPERATION_TYPE_FILE_RENAME, Operation in ("FolderCreated", "CreateFolder"), XDM_CONST.OPERATION_TYPE_DIR_CREATE, Operation in ("FolderDeleted", "DeleteFolder", "FolderDeletedFirstStageRecycleBin", "FolderDeletedSecondStageRecycleBin"), XDM_CONST.OPERATION_TYPE_DIR_REMOVE, Operation in ("FolderModified", "UpdateFolder"), XDM_CONST.OPERATION_TYPE_DIR_WRITE, Operation in ("AddFolderPermissions", "UpdateFolderAccess"), XDM_CONST.OPERATION_TYPE_DIR_CHANGE_MODE, Operation in ("FolderRenamed"), XDM_CONST.OPERATION_TYPE_DIR_RENAME, Operation),
xdm.event.operation_sub_type = Operation,
xdm.event.original_event_type = to_string(RecordType),
xdm.event.outcome = if(EndpointMetaData_EnforcementMode = "1", XDM_CONST.OUTCOME_UNKNOWN, EndpointMetaData_EnforcementMode ~= "2|3", XDM_CONST.OUTCOME_PARTIAL, EndpointMetaData_EnforcementMode = "4", XDM_CONST.OUTCOME_FAILED, EndpointMetaData_EnforcementMode = "5", XDM_CONST.OUTCOME_SUCCESS, ResultStatus = "Succeeded", XDM_CONST.OUTCOME_SUCCESS, ResultStatus = "PartiallySucceeded", XDM_CONST.OUTCOME_PARTIAL, ResultStatus = "Failed", XDM_CONST.OUTCOME_FAILED, ResultStatus ~= "[Tt]rue", XDM_CONST.OUTCOME_SUCCESS, ResultStatus ~= "[Ff]alse", XDM_CONST.OUTCOME_FAILED),
xdm.event.outcome_reason = coalesce(translate_EnforcementMode, ResultStatus),
xdm.network.http.url = SharePointMetaData -> SiteCollectionUrl,
xdm.network.rule = to_string(PolicyDetails),
xdm.observer.action = coalesce(translate_EnforcementMode, to_string(ExceptionInfo), ResultStatus),
xdm.observer.type = coalesce(Workload, evaluationsource),
xdm.source.user.identifier = UserKey,
xdm.source.user.identity_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.IDENTITY_TYPE_USER, user_type_string ~= "4|5|6|7|8", XDM_CONST.IDENTITY_TYPE_MACHINE),
xdm.source.user.upn = UserId,
xdm.source.user.user_type = if(user_type_string ~= "0|1|2|3", XDM_CONST.USER_TYPE_REGULAR, user_type_string ~= "4|5|6|7|8", XDM_CONST.USER_TYPE_SERVICE_ACCOUNT),
xdm.source.user.username = SharePointMetaData -> From,
xdm.target.file.extension = coalesce(SharePointMetaData_FileName_extension, EndpointMetaData_FileExtension, object_id_file_extension),
xdm.target.file.file_type = EndpointMetaData -> FileType,
xdm.target.file.filename = coalesce(SharePointMetaData_FileName_name, object_id_file_name),
xdm.target.file.path = coalesce(SharePointMetaData_FilePathUrl, object_id_file_path),
xdm.target.host.hostname = EndpointMetaData -> DeviceName,
xdm.target.resource.name = if(ObjectId ~= "\S+", rtrim(ltrim(ObjectId, "<"), ">")),
xdm.target.url = SharePointMetaData -> FilePathUrl;
[MODEL: dataset = "msft_o365_emails_raw"]
alter
is_itemAttachment = if(arrayindex(attachments -> [], 0) -> ["@odata.type"] = "#microsoft.graph.itemAttachment"),
is_itemAttachment_contentType_message = if(arrayindex(attachments -> [], 0) -> contentType ~= "[Mm]essage"),
is_fileAttachment = if(arrayindex(attachments -> [], 0) -> ["@odata.type"] = "#microsoft.graph.fileAttachment"),
get_attachment = arrayindex(attachments -> [], 0),
get_sentDateTime = if(to_string(sentDateTime) ~= "^\d{10}$|^\d{13}$|^\d{16}$", to_integer(to_string(sentDateTime)), null),
get_receivedDateTime = if(to_string(receivedDateTime) ~= "^\d{10}$|^\d{13}$|^\d{16}$", to_integer(to_string(receivedDateTime)), null)
| alter
check_sentDateTime = if(
to_string(get_sentDateTime) ~= "^\d{10}$", to_timestamp(get_sentDateTime, "SECONDS"),
to_string(get_sentDateTime) ~= "^\d{13}$", to_timestamp(get_sentDateTime, "MILLIS"),
to_string(get_sentDateTime) ~= "^\d{16}$", to_timestamp(get_sentDateTime, "MICROS"),
null
),
check_receivedDateTime = if(
to_string(get_receivedDateTime) ~= "^\d{10}$", to_timestamp(get_receivedDateTime, "SECONDS"),
to_string(get_receivedDateTime) ~= "^\d{13}$", to_timestamp(get_receivedDateTime, "MILLIS"),
to_string(get_receivedDateTime) ~= "^\d{16}$", to_timestamp(get_receivedDateTime, "MICROS"),
null
)
| alter
xdm.event.id = id,
xdm.alert.severity = importance,
xdm.event.operation_sub_type = object_create("isRead", isRead, "isDraft", isDraft, "hasAttachments", hasAttachments, "isReadReceiptRequested", isReadReceiptRequested, "isDeliveryReceiptRequested", isDeliveryReceiptRequested),
xdm.email.origination_timestamp = if(get_sentDateTime = null, parse_timestamp("%Y-%m-%dT%H:%M:%SZ", to_string(sentDateTime)), check_sentDateTime),
xdm.email.delivery_timestamp = if(get_receivedDateTime = null, parse_timestamp("%Y-%m-%dT%H:%M:%SZ", to_string(receivedDateTime)), check_receivedDateTime),
xdm.source.user.username = from -> emailAddress.name,
xdm.source.user.upn = from -> emailAddress.address,
xdm.target.user.upn = mailboxOwner,
xdm.email.sender = sender -> emailAddress.address,
xdm.email.return_path = arrayindex(arraymap(replyTo -> [], "@element" -> emailAddress.address), 0),
xdm.observer.unique_identifier = webLink,
xdm.alert.original_alert_id = if(is_itemAttachment and is_itemAttachment_contentType_message, get_attachment -> id),
xdm.alert.name = if(is_itemAttachment and is_itemAttachment_contentType_message, get_attachment -> name),
xdm.email.attachment.file_type = get_attachment -> contentType,
xdm.email.attachment.filename = if(is_fileAttachment, get_attachment -> name),
xdm.email.attachment.size = to_integer(get_attachment -> size),
xdm.email.cc = arraymap(ccRecipients -> [], "@element" -> emailAddress.address),
xdm.email.recipients = arraymap(toRecipients -> [], "@element" -> emailAddress.address),
xdm.email.bcc = arraymap(bccRecipients -> [], "@element" -> emailAddress.address),
xdm.email.message_id = internetMessageId,
xdm.email.data = to_string(internetMessageHeaders),
xdm.email.subject = subject;