correlate-travel-alerts-with-vpn-logs

let Unfamiliarsigninproperties = ExportSecurityAlerts_CL 
    | where AlertName_s == "Unfamiliar sign-in properties"
    | extend IPAddress = tostring(parse_json(ExtendedProperties_s).["Client IP Address"])
    | extend Location = tostring(parse_json(ExtendedProperties_s).["Client Location"])
    | extend UID = split(tostring(parse_json(ExtendedProperties_s).["User Account"]),"@")[0]
    | extend AlertDate = split(TimeGenerated_Local_Time__s, ",")[0]
    | extend AlertTime = split(TimeGenerated_Local_Time__s, ",")[1]
    | extend AlertDateTime = todatetime(strcat(AlertDate, AlertTime))
    | project AlertDateTime, AlertName = AlertName_s, IPAddress, Location, UID;
let AtypicalTravel = ExportSecurityAlerts_CL 
    | where AlertName_s == "Atypical travel"
    | extend IPAddress = tostring(parse_json(ExtendedProperties_s).["Current IP Address"])
    | extend Location = tostring(parse_json(ExtendedProperties_s).["Current Location"])
    | extend UID = split(tostring(parse_json(ExtendedProperties_s).["User Account"]),"@")[0]
    | extend AlertDate = split(TimeGenerated_Local_Time__s, ",")[0]
    | extend AlertTime = split(TimeGenerated_Local_Time__s, ",")[1]
    | extend AlertDateTime = todatetime(strcat(AlertDate, AlertTime))
    | project AlertDateTime, AlertName = AlertName_s, IPAddress, Location, UID;
let ImpossibleTravelActivity = ExportSecurityAlerts_CL 
    | where AlertName_s == "Impossible travel activity"
    | where Description_s has "performed an impossible travel activity"
    | parse Description_s with * "The user " UserName_ " performed an " descript0_ " in " Country1_ " and " descript1_ " in " Country2_ " within " descript2_
    | extend Location = Country2_
    | extend IP_Addresses = tostring(parse_json(ExtendedProperties_s).["IP Addresses"])
    | parse IP_Addresses with IPAddress ", " IPAddress2_
    | parse UserName_ with * " (" UserName2_ ")" __
    | extend UID = split(case(
        isempty(UserName2_), UserName_,
        UserName2_),"@")[0]
    | extend AlertDate = split(TimeGenerated_Local_Time__s, ",")[0]
    | extend AlertTime = split(TimeGenerated_Local_Time__s, ",")[1]
    | extend AlertDateTime = todatetime(strcat(AlertDate, AlertTime))
    | project AlertDateTime, AlertName = AlertName_s, IPAddress, Location, UID;
let TravelAlerts = Unfamiliarsigninproperties 
    | union AtypicalTravel, ImpossibleTravelActivity;
let JunosPulseLogs = _GetWatchlist("JunosPulseVPNlogs")
    | extend JunosDateTime = todatetime(strcat(Date, Time))
    | project JunosDateTime, Action, UID;
TravelAlerts
| where TimeGenerated = AlertDateTime > ago(30d)
| extend UID = tostring(UID)
| join kind=leftouter JunosPulseLogs on UID
| summarize arg_max(AlertDateTime, JunosDateTime, *) by UID
| where abs(datetime_diff('minute', AlertDateTime, JunosDateTime)) > 5 or isempty(JunosDateTime)
| project-away JunosDateTime, Action, UID1