[MODEL: dataset = menlo_security_ip_raw]
alter
    tmp_request_type = uppercase(to_string(request_type)),
    tmp_action = lowercase(to_string(coalesce(pe_action, action))),
    tmp_risk_score = lowercase(to_string(risk_score)),
    tmp_protocol = uppercase(to_string(protocol)),
    // `dst` is a string column holding either a JSON-array-string of IPs or a single bare IP;
    // regextract handles both forms uniformly.
    tmp_dst_ipv4_list = regextract(to_string(dst), "(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})")
| alter
    xdm.event.type = source_log_type,
    xdm.event.original_event_type = coalesce(name, audit_actions, sub_event_type),
    xdm.event.outcome_reason = coalesce(next_hop_reason, reason),
    xdm.source.ipv4 = coalesce(x_client_ip, src_ip),
    xdm.source.port = to_integer(src_port),
    xdm.source.user.username = coalesce(userid, uid),
    xdm.source.user_agent = user_agent,
    xdm.intermediate.ipv4 = egress_ip,
    xdm.intermediate.is_proxy = if(egress_ip != null and egress_ip != "", true), 
    xdm.intermediate.cloud.region = region,
    xdm.target.ipv4 = coalesce(origin_ip, dst_ip),
    xdm.target.port = to_integer(dst_from_port),
    xdm.target.host.ipv4_addresses = tmp_dst_ipv4_list,
    xdm.target.url = coalesce(url, dst_url),
    xdm.target.domain = domain,
    xdm.target.location.country = origin_country,
    xdm.target.file.filename = filename,
    xdm.target.file.sha256 = sha256,
    xdm.target.file.size = coalesce(to_integer(file_size), to_integer(bytes)),
    xdm.target.file.file_type = coalesce(file_type, document_type),
    xdm.email.sender = coalesce(from, reply_to),
    xdm.email.recipients = arraycreate(coalesce(to, delivered_to)),
    xdm.email.subject = subject,
    xdm.email.message_id = message_id,
    xdm.network.application_protocol = tmp_protocol,
    xdm.network.rule = coalesce(pe_rulename, rule_name),
    xdm.network.http.method = if(tmp_request_type = "GET", XDM_CONST.HTTP_METHOD_GET, tmp_request_type = "POST", XDM_CONST.HTTP_METHOD_POST, tmp_request_type = "PUT", XDM_CONST.HTTP_METHOD_PUT, tmp_request_type = "DELETE", XDM_CONST.HTTP_METHOD_DELETE, tmp_request_type = "PATCH", XDM_CONST.HTTP_METHOD_PATCH, tmp_request_type = "HEAD", XDM_CONST.HTTP_METHOD_HEAD, tmp_request_type = "OPTIONS", XDM_CONST.HTTP_METHOD_OPTIONS, tmp_request_type = "CONNECT", XDM_CONST.HTTP_METHOD_CONNECT, tmp_request_type = "TRACE", XDM_CONST.HTTP_METHOD_TRACE, null),
    xdm.network.http.url = coalesce(url, dst_url),
    xdm.network.http.url_category = categories,
    xdm.network.http.content_type = content_type,
    xdm.network.http.referrer = referer,
    xdm.network.http.response_code = to_string(response_code),
    xdm.observer.action = if(tmp_action = "block", "Block", tmp_action = "allow", "Allow", tmp_action = "isolate", "Redirect", tmp_action = "direct", "Allow", tmp_action = "ssl_exception", "Allow", tmp_action = "log", "Alert", null),
    xdm.intermediate.host.hostname = hostname,
    xdm.observer.version = version,
    xdm.alert.name = threats,
    xdm.alert.category = coalesce(threat_types, ccl_ids),
    xdm.alert.severity = if(tmp_risk_score = "high", "High", tmp_risk_score = "medium", "Medium", tmp_risk_score = "low", "Low", null);