AzureNetworkAnalytics_CL
| where SubType_s == 'FlowLog'
    and (FASchemaVersion_s == '1' or FASchemaVersion_s == '2')
    and FlowStartTime_t between (datetime('2022-06-03T04:44:29.994Z') .. datetime('2022-06-04T04:44:29.994Z'))
| where FlowType_s == 'MaliciousFlow' and (Region1_s == 'eastus2' or Region2_s == 'eastus2')
| extend
    AllowedInboundBytesAtSrc = iff(FlowStatus_s == 'A' and FlowDirection_s == 'O', tolong(InboundBytes_d), 0),
    BlockedInboundBytesAtSrc = iff(FlowStatus_s == 'D' and FlowDirection_s == 'O', tolong(InboundBytes_d), 0),
    AllowedOutboundBytesAtSrc = iff(FlowStatus_s == 'A' and FlowDirection_s == 'O', tolong(OutboundBytes_d), 0),
    BlockedOutboundBytesAtSrc = iff(FlowStatus_s == 'D' and FlowDirection_s == 'O', tolong(OutboundBytes_d), 0),
    AllowedInboundBytesAtDest = iff(FlowStatus_s == 'A' and FlowDirection_s == 'I', tolong(InboundBytes_d), 0),
    BlockedInboundBytesAtDest = iff(FlowStatus_s == 'D' and FlowDirection_s == 'I', tolong(InboundBytes_d), 0),
    AllowedOutboundBytesAtDest = iff(FlowStatus_s == 'A' and FlowDirection_s == 'I', tolong(OutboundBytes_d), 0),
    BlockedOutboundBytesAtDest = iff(FlowStatus_s == 'D' and FlowDirection_s == 'I', tolong(OutboundBytes_d), 0)
| extend
    AllowedInboundPacketsAtSrc = iff(FlowStatus_s == 'A' and FlowDirection_s == 'O', tolong(InboundPackets_d), 0),
    BlockedInboundPacketsAtSrc = iff(FlowStatus_s == 'D' and FlowDirection_s == 'O', tolong(InboundPackets_d), 0),
    AllowedOutboundPacketsAtSrc = iff(FlowStatus_s == 'A' and FlowDirection_s == 'O', tolong(OutboundPackets_d), 0),
    BlockedOutboundPacketsAtSrc = iff(FlowStatus_s == 'D' and FlowDirection_s == 'O', tolong(OutboundPackets_d), 0),
    AllowedInboundPacketsAtDest = iff(FlowStatus_s == 'A' and FlowDirection_s == 'I', tolong(InboundPackets_d), 0),
    BlockedInboundPacketsAtDest = iff(FlowStatus_s == 'D' and FlowDirection_s == 'I', tolong(InboundPackets_d), 0),
    AllowedOutboundPacketsAtDest = iff(FlowStatus_s == 'A' and FlowDirection_s == 'I', tolong(OutboundPackets_d), 0),
    BlockedOutboundPacketsAtDest = iff(FlowStatus_s == 'D' and FlowDirection_s == 'I', tolong(OutboundPackets_d), 0)
| extend AllowedOutbound_inferred = max_of(AllowedOutFlows_d, AllowedInFlows_d + DeniedInFlows_d)
| extend
    Outbound = AllowedOutbound_inferred + DeniedOutFlows_d,
    Inbound = AllowedOutbound_inferred
| extend FlowCount = max_of(Inbound, Outbound)
| extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest)
| extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc)
| extend
    OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc,
    InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred
| extend
    OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest,
    InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred
| extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc)
| extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest)
| extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc)
| extend
    OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc,
    InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred
| extend
    OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest,
    InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred
| extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)
| extend MaliciousIp = iif(isnotempty(VM2_s), SrcIP_s, DestIP_s)
| summarize
    TotalFlow = sum(FlowCount),
    TotalBytes = sum(BytesCount),
    TotalPackets = sum(PacketsCount),
    MaliciousAllowedInbound = sum(AllowedInFlows_d),
    MaliciousAllowedOutbound = sum(AllowedOutFlows_d)
    by
    MaliciousIp,
    DestPort = DestPort_d,
    Country = Country_s,
    L7Protocol = L7Protocol_s
| where MaliciousAllowedInbound > 0 or MaliciousAllowedOutbound > 0
| sort by TotalFlow desc
| render table