KQL Search

let _NetskopeEmpty = datatable(TimeGenerated:datetime, action_s:string, category_s:string, severity_s:string, malware_name_s:string, malware_type_s:string, threat_name_s:string, user_s:string, domain_s:string, dstip_s:string, srcip_s:string, bytes_uploaded_d:real, bytes_downloaded_d:real, app_s:string, url_s:string, dlp_rule_s:string, dlp_profile_s:string, activity_s:string, file_type_s:string, object_s:string, dst_country_s:string, src_country_s:string, ccl_s:string, access_method_s:string, traffic_type_s:string)[];
let RiskyCCL = dynamic(["poor", "low", "unknown"]);
let ExfilCategories = dynamic([
    "Cloud Storage", "File Sharing", "Online Storage and Backup",
    "Personal Sites & Blogs", "Webmail"]);
union isfuzzy=true _NetskopeEmpty, NetskopeWebTx_CL
| where TimeGenerated > ago(1d)
| where isnotempty(user_s) and isnotempty(app_s)
| where action_s !in ("block", "Block", "blocked", "Blocked")
| where ccl_s in (RiskyCCL) or isempty(ccl_s)
| where category_s in (ExfilCategories) or activity_s has_any ("Upload", "Share", "Post")
| summarize
    UploadCount       = count(),
    TotalMBUploaded   = round(sum(todouble(bytes_uploaded_d)) / 1048576, 2),
    UniqueFiles       = dcount(object_s),
    FileNames         = make_set(object_s, 10),
    FileTypes         = make_set(file_type_s, 10),
    Apps              = make_set(app_s, 10),
    Domains           = make_set(domain_s, 10),
    Categories        = make_set(category_s, 5),
    CCLValues         = make_set(ccl_s, 5),
    SourceIPs         = make_set(srcip_s, 5),
    FirstSeen         = min(TimeGenerated),
    LastSeen          = max(TimeGenerated)
  by user_s
| where TotalMBUploaded > 100 or UploadCount > 50
| order by TotalMBUploaded desc, UploadCount desc