[MODEL: dataset=msft_graph_security_alerts_raw] filter _reporting_device_name in ("https://graph.microsoft.com/beta/security/alerts_v2", "https://graph.microsoft.com/v1.0/security/alerts_v2") | alter alertEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "alertEvidence"), 0), analyzedMessageEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "analyzedMessageEvidence"), 0), analyzedMessageEvidence2_json = arrayfilter(evidence -> [], "@element" ~= "analyzedMessageEvidence"), cloudApplicationEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "cloudApplicationEvidence"), 0), deviceEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "deviceEvidence"), 0), fileEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "fileEvidence"), 0), ipEvidence_json = arrayfilter(evidence -> [], "@element" ~= "ipEvidence"), mailboxEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "mailboxEvidence"), 0), registryKeyEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "registryKeyEvidence"), 0), registryValueEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "registryValueEvidence"), 0), urlEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "urlEvidence"), 0), userEvidence_json = arrayindex(arrayfilter(evidence -> [], "@element" ~= "userEvidence"), 0), // processEvidence: prefer evidence tagged with an attacker-side role when present processEvidence_json = coalesce( arrayindex(arrayfilter(arrayfilter(evidence -> [], "@element" ~= "processEvidence"), arraystring(json_extract_array("@element", "$.roles"), ",") ~= "Source|Initiated|Likely"), 0), arrayindex(arrayfilter(evidence -> [], "@element" ~= "processEvidence"), 0) ) | alter // userEvidence Fields userEvidence_userAccount_accountName = json_extract_scalar(userEvidence_json, "$.userAccount.accountName"), userEvidence_userAccount_domainName = json_extract_scalar(userEvidence_json, "$.userAccount.domainName"), userEvidence_userAccount_userSid = json_extract_scalar(userEvidence_json, "$.userAccount.userSid"), userEvidence_userAccount_azureAdUserId = json_extract_scalar(userEvidence_json, "$.userAccount.azureAdUserId"), userEvidence_userAccount_userPrincipalName = json_extract_scalar(userEvidence_json, "$.userAccount.userPrincipalName"), userEvidence_userAccount_displayName = json_extract_scalar(userEvidence_json, "$.userAccount.displayName"), // alertEvidence Fields alertEvidence_tags = json_extract_scalar(alertEvidence_json, "$.tags"), // analyzedMessageEvidence Fields analyzedMessageEvidence_tags = json_extract_scalar(analyzedMessageEvidence_json, "$.tags"), analyzedMessageEvidence_threats = json_extract_scalar(analyzedMessageEvidence_json, "$.threats"), analyzedMessageEvidence_internetMessageId = json_extract_scalar(analyzedMessageEvidence_json, "$.internetMessageId"), analyzedMessageEvidence_receivedDateTime = json_extract_scalar(analyzedMessageEvidence_json, "$.receivedDateTime"), analyzedMessageEvidence_subject = json_extract_scalar(analyzedMessageEvidence_json, "$.subject"), analyzedMessageEvidence_recipientEmailAddress = arraymap(analyzedMessageEvidence2_json, json_extract_scalar("@element", "$.recipientEmailAddress")), // cloudApplicationEvidence Fields cloudApplicationEvidence_tags = json_extract_scalar(cloudApplicationEvidence_json, "$.tags"), cloudApplicationEvidence_displayName = json_extract_scalar(cloudApplicationEvidence_json, "$.displayName"), cloudApplicationEvidence_instanceName = json_extract_scalar(cloudApplicationEvidence_json, "$.instanceName"), // deviceEvidence Fields deviceEvidence_tags = json_extract_scalar(deviceEvidence_json, "$.tags"), deviceEvidence_riskScore = json_extract_scalar(deviceEvidence_json, "$.riskScore"), deviceEvidence_deviceDnsName = json_extract_scalar(deviceEvidence_json, "$.deviceDnsName"), deviceEvidence_hostName = json_extract_scalar(deviceEvidence_json, "$.hostName"), deviceEvidence_mdeDeviceId = json_extract_scalar(deviceEvidence_json, "$.mdeDeviceId"), deviceEvidence_azureAdDeviceId = json_extract_scalar(deviceEvidence_json, "$.azureAdDeviceId"), deviceEvidence_osPlatform = json_extract_scalar(deviceEvidence_json, "$.osPlatform"), deviceEvidence_version = json_extract_scalar(deviceEvidence_json, "$.version"), // fileEvidence Fields fileEvidence_tags = json_extract_scalar(fileEvidence_json, "$.tags"), fileEvidence_detectionStatus = json_extract_scalar(fileEvidence_json, "$.detectionStatus"), fileEvidence_fileDetails_fileName = json_extract_scalar(fileEvidence_json, "$.fileDetails.fileName"), fileEvidence_fileDetails_filePath = json_extract_scalar(fileEvidence_json, "$.fileDetails.filePath"), fileEvidence_fileDetails_fileSize = json_extract_scalar(fileEvidence_json, "$.fileDetails.fileSize"), fileEvidence_fileDetails_sha256 = json_extract_scalar(fileEvidence_json, "$.fileDetails.sha256"), fileEvidence_fileDetails_signer = json_extract_scalar(fileEvidence_json, "$.fileDetails.signer"), // ipEvidence Fields ipEvidence_tags = arraystring(arraymap(ipEvidence_json, json_extract_scalar("@element", "$.tags")), " "), ipEvidence_ipAddress = arraymap(ipEvidence_json, json_extract_scalar("@element", "$.ipAddress")), // mailboxEvidence Fields mailboxEvidence_tags = json_extract_scalar(mailboxEvidence_json, "$.tags"), mailboxEvidence_displayName = json_extract_scalar(mailboxEvidence_json, "$.displayName"), mailboxEvidence_primaryAddress = json_extract_scalar(mailboxEvidence_json, "$.primaryAddress"), mailboxEvidence_userAccount_accountName = json_extract_scalar(mailboxEvidence_json, "$.userAccount.accountName"), mailboxEvidence_userAccount_userPrincipalName = json_extract_scalar(mailboxEvidence_json, "$.userAccount.userPrincipalName"), mailboxEvidence_userAccount_domainName = json_extract_scalar(mailboxEvidence_json, "$.userAccount.domainName"), mailboxEvidence_userAccount_userSid = json_extract_scalar(mailboxEvidence_json, "$.userAccount.userSid"), // processEvidence Fields processEvidence_tags = json_extract_scalar(processEvidence_json, "$.tags"), processEvidence_detectionStatus = json_extract_scalar(processEvidence_json, "$.detectionStatus"), processEvidence_userAccount_accountName = json_extract_scalar(processEvidence_json, "$.userAccount.accountName"), processEvidence_userAccount_userPrincipalName = json_extract_scalar(processEvidence_json, "$.userAccount.userPrincipalName"), processEvidence_userAccount_domainName = json_extract_scalar(processEvidence_json, "$.userAccount.domainName"), processEvidence_userAccount_userSid = json_extract_scalar(processEvidence_json, "$.userAccount.userSid"), processEvidence_imageFile_fileName = json_extract_scalar(processEvidence_json, "$.imageFile.fileName"), processEvidence_imageFile_filePath = json_extract_scalar(processEvidence_json, "$.imageFile.filePath"), processEvidence_imageFile_fileSize = json_extract_scalar(processEvidence_json, "$.imageFile.fileSize"), processEvidence_imageFile_sha256 = json_extract_scalar(processEvidence_json, "$.imageFile.sha256"), processEvidence_imageFile_signer = json_extract_scalar(processEvidence_json, "$.imageFile.signer"), processEvidence_parentProcessId = json_extract_scalar(processEvidence_json, "$.parentProcessId"), processEvidence_processCommandLine = json_extract_scalar(processEvidence_json, "$.processCommandLine"), processEvidence_processId = json_extract_scalar(processEvidence_json, "$.processId"), // registryKeyEvidence Fields registryKeyEvidence_tags = json_extract_scalar(registryKeyEvidence_json, "$.tags"), registryKeyEvidence_registryKey = json_extract_scalar(registryKeyEvidence_json, "$.registryKey"), // registryKeyEvidence Fields registryValueEvidence_tags = json_extract_scalar(registryValueEvidence_json, "$.tags"), registryValueEvidence_registryKey = json_extract_scalar(registryValueEvidence_json, "$.registryKey"), registryValueEvidence_registryValue = json_extract_scalar(registryValueEvidence_json, "$.registryValue"), registryValueEvidence_registryValueName = json_extract_scalar(registryValueEvidence_json, "$.registryValueName"), registryValueEvidence_registryValueType = json_extract_scalar(registryValueEvidence_json, "$.registryValueType"), // urlEvidence Fields urlEvidence_tags = json_extract_scalar(urlEvidence_json, "$.tags"), urlEvidence_url = json_extract_scalar(urlEvidence_json, "$.url") | alter check_resolved = if(to_string(status) ~= "resolved", to_boolean("TRUE"), to_boolean("FALSE")), unified_os_version = arraystring(arraycreate(deviceEvidence_osPlatform, deviceEvidence_version), " "), lowercase_os_platform = lowercase(deviceEvidence_osPlatform), lowercase_registry_type = lowercase(registryValueEvidence_registryValueType), get_email_timestamp = arraystring(regextract(analyzedMessageEvidence_receivedDateTime, "\d+\/\d+\/\d+ \d+:\d+:\d+ [A-Z]{2}"), ""), get_email_zone = replex(arraystring(regextract(analyzedMessageEvidence_receivedDateTime, "[+|-]\d+:\d+"), ""), ":", ""), check_ipv4 = arrayfilter(ipEvidence_ipAddress, "@element" ~= "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"), check_ipv6 = arrayfilter(ipEvidence_ipAddress, "@element" ~= "[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}[a-fA-F0-9\:]{1,5}"), outcome_check = coalesce(fileEvidence_detectionStatus, processEvidence_detectionStatus, to_string(classification)) | alter xdm.observer.unique_identifier = id, xdm.event.description = title, xdm.event.is_completed = check_resolved, xdm.alert.subcategory = category, xdm.event.outcome = if(fileEvidence_detectionStatus = "blocked", XDM_CONST.OUTCOME_SUCCESS, fileEvidence_detectionStatus = "detected", XDM_CONST.OUTCOME_FAILED, fileEvidence_detectionStatus = "prevented", XDM_CONST.OUTCOME_PARTIAL, processEvidence_detectionStatus = "blocked", XDM_CONST.OUTCOME_SUCCESS, processEvidence_detectionStatus = "detected", XDM_CONST.OUTCOME_FAILED, processEvidence_detectionStatus = "prevented", XDM_CONST.OUTCOME_PARTIAL, classification = "falsePositive", XDM_CONST.OUTCOME_SUCCESS, classification = "truePositive", XDM_CONST.OUTCOME_FAILED, classification = "benignPositive", XDM_CONST.OUTCOME_PARTIAL, outcome_check = null, null, to_string(outcome_check)), xdm.event.tags = arraycreate(alertEvidence_tags, analyzedMessageEvidence_tags, cloudApplicationEvidence_tags, deviceEvidence_tags, fileEvidence_tags, ipEvidence_tags, mailboxEvidence_tags, processEvidence_tags, registryKeyEvidence_tags, registryValueEvidence_tags, urlEvidence_tags), xdm.alert.severity = coalesce(to_string(severity), deviceEvidence_riskScore), xdm.source.host.device_id = coalesce(deviceEvidence_mdeDeviceId, deviceEvidence_azureAdDeviceId), xdm.alert.description = description, xdm.intermediate.agent.identifier = detectorId, xdm.event.id = coalesce(to_string(incidentId), to_string(providerAlertId)), xdm.intermediate.host.fqdn = coalesce(to_string(alertWebUrl), to_string(incidentWebUrl)), xdm.alert.original_threat_name = coalesce(to_string(determination), analyzedMessageEvidence_threats), xdm.observer.type = if(serviceSource != "unknownFutureValue", serviceSource, productName), xdm.intermediate.host.hostname = detectionSource, xdm.email.message_id = analyzedMessageEvidence_internetMessageId, xdm.email.delivery_timestamp = parse_timestamp("%m/%d/%Y %H:%M:%S %p %z", arraystring(arraycreate(get_email_timestamp, get_email_zone), " ")), xdm.email.recipients = analyzedMessageEvidence_recipientEmailAddress, xdm.email.subject = analyzedMessageEvidence_subject, xdm.source.application.name = cloudApplicationEvidence_displayName, xdm.source.cloud.project = cloudApplicationEvidence_instanceName, xdm.source.host.fqdn = deviceEvidence_deviceDnsName, xdm.source.host.hostname = coalesce(deviceEvidence_hostName, deviceEvidence_deviceDnsName), xdm.source.host.os = unified_os_version, xdm.source.agent.identifier = userEvidence_userAccount_azureAdUserId, xdm.source.host.os_family = if(lowercase_os_platform ~= "win|windows", XDM_CONST.OS_FAMILY_WINDOWS, lowercase_os_platform ~= "mac|macos", XDM_CONST.OS_FAMILY_MACOS, lowercase_os_platform ~= "linux", XDM_CONST.OS_FAMILY_LINUX, lowercase_os_platform ~= "android", XDM_CONST.OS_FAMILY_ANDROID, lowercase_os_platform ~= "ios", XDM_CONST.OS_FAMILY_IOS, lowercase_os_platform ~= "ubuntu", XDM_CONST.OS_FAMILY_UBUNTU, lowercase_os_platform ~= "debian", XDM_CONST.OS_FAMILY_DEBIAN, lowercase_os_platform ~= "fedora", XDM_CONST.OS_FAMILY_FEDORA, lowercase_os_platform ~= "centos", XDM_CONST.OS_FAMILY_CENTOS, lowercase_os_platform ~= "chromeos", XDM_CONST.OS_FAMILY_CHROMEOS, lowercase_os_platform ~= "solaris", XDM_CONST.OS_FAMILY_SOLARIS, lowercase_os_platform ~= "scada", XDM_CONST.OS_FAMILY_SCADA), xdm.target.file.filename = fileEvidence_fileDetails_fileName, xdm.target.file.path = fileEvidence_fileDetails_filePath, xdm.target.file.size = to_integer(fileEvidence_fileDetails_fileSize), xdm.target.file.sha256 = fileEvidence_fileDetails_sha256, xdm.target.file.signer = fileEvidence_fileDetails_signer, xdm.alert.mitre_techniques = arraymap(json_extract_array(mitreTechniques, "$"), replex("@element", "\"", "")), xdm.source.host.ipv4_addresses = check_ipv4, xdm.source.host.ipv6_addresses = check_ipv6, xdm.source.user.username = coalesce(mailboxEvidence_displayName, mailboxEvidence_primaryAddress, mailboxEvidence_userAccount_accountName, mailboxEvidence_userAccount_userPrincipalName, processEvidence_userAccount_accountName, processEvidence_userAccount_userPrincipalName, userEvidence_userAccount_accountName, userEvidence_userAccount_userPrincipalName, userEvidence_userAccount_displayName), xdm.source.user.upn = coalesce(userEvidence_userAccount_userPrincipalName, mailboxEvidence_userAccount_userPrincipalName, processEvidence_userAccount_userPrincipalName), xdm.source.user.domain = coalesce(mailboxEvidence_userAccount_domainName, processEvidence_userAccount_domainName, userEvidence_userAccount_domainName), xdm.source.user.identifier = coalesce(mailboxEvidence_userAccount_userSid, processEvidence_userAccount_userSid, userEvidence_userAccount_userSid), xdm.source.process.executable.filename = processEvidence_imageFile_fileName, xdm.source.process.executable.path = processEvidence_imageFile_filePath, xdm.source.process.executable.size = to_integer(processEvidence_imageFile_fileSize), xdm.source.process.executable.sha256 = processEvidence_imageFile_sha256, xdm.source.process.executable.signer = processEvidence_imageFile_signer, xdm.source.process.parent_id = processEvidence_parentProcessId, xdm.source.process.command_line = processEvidence_processCommandLine, xdm.source.process.pid = to_integer(processEvidence_processId), xdm.target.registry.key = coalesce(registryKeyEvidence_registryKey, registryValueEvidence_registryKey), xdm.target.registry.data = registryValueEvidence_registryValue, xdm.target.registry.value = registryValueEvidence_registryValueName, xdm.target.registry.value_type = if(lowercase_registry_type ~= "binary", XDM_CONST.REGISTRY_VALUE_TYPE_REG_BINARY, lowercase_registry_type ~= "dword$", XDM_CONST.REGISTRY_VALUE_TYPE_REG_DWORD, lowercase_registry_type ~= "dword_little", XDM_CONST.REGISTRY_VALUE_TYPE_REG_DWORD_LITTLE_ENDIAN, lowercase_registry_type ~= "dword_big", XDM_CONST.REGISTRY_VALUE_TYPE_REG_DWORD_BIG_ENDIAN, lowercase_registry_type ~= "expand", XDM_CONST.REGISTRY_VALUE_TYPE_REG_EXPAND_SZ, lowercase_registry_type ~= "link", XDM_CONST.REGISTRY_VALUE_TYPE_REG_LINK, lowercase_registry_type ~= "multi", XDM_CONST.REGISTRY_VALUE_TYPE_REG_MULTI_SZ, lowercase_registry_type ~= "none", XDM_CONST.REGISTRY_VALUE_TYPE_REG_NONE, lowercase_registry_type ~= "qword$", XDM_CONST.REGISTRY_VALUE_TYPE_REG_QWORD, lowercase_registry_type ~= "qword_little", XDM_CONST.REGISTRY_VALUE_TYPE_REG_QWORD_LITTLE_ENDIAN, lowercase_registry_type ~= "reg_sz", XDM_CONST.REGISTRY_VALUE_TYPE_REG_SZ, lowercase_registry_type = null, null, to_string(lowercase_registry_type)), xdm.target.url = urlEvidence_url;