Geomap Visualization

# 1. Query Sentinel for data with coordinates
mcp_sentinel-data_query_lake({
  "query": "W3CIISLog | where TimeGenerated > ago(7d) | where scStatus == '401' | summarize value = count(), lat = take_any(RemoteIPLatitude), lon = take_any(RemoteIPLongitude) by ip = cIP | where lat != 0 | project ip, lat, lon, value"
})

# 2. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<query results>],
  "title": "Attack Origins (Last 7 Days)",
  "valueLabel": "Failed Logins",
  "colorScale": "blue-red"
})

{
  "data": [
    {"ip": "101.36.107.228", "lat": 22.25, "lon": 114.15, "value": 44},
    {"ip": "193.142.147.209", "lat": 52.35, "lon": 4.92, "value": 13},
    {"ip": "170.64.158.196", "lat": -33.90, "lon": 151.19, "value": 9}
  ]
}

{
  "enrichment": [
    {
      "ip": "101.36.107.228",
      "city": "Hong Kong",
      "country": "HK",
      "org": "AS135377 UCLOUD INFORMATION TECHNOLOGY",
      "is_vpn": true,
      "is_proxy": false,
      "is_tor": false,
      "abuse_confidence_score": 100,
      "total_reports": 4612,
      "last_reported": "2026-01-29",
      "threat_categories": ["SSH", "Brute-Force", "Web App Attack"]
    }
  ]
}

import json

# Load enrichment from batch operation
with open('temp/ip_enrichment_<timestamp>.json', 'r') as f:
    raw_enrichment = json.load(f)

# Build geomap enrichment array - INCLUDE ALL
enrichment = []
for e in raw_enrichment:
    threat_cats = []
    for c in e.get('recent_comments', [])[:5]:
        threat_cats.extend(c.get('categories', []))
    
    enrichment.append({
        'ip': e['ip'],
        'city': e.get('city', 'Unknown'),
        'country': e.get('country', '??'),
        'org': e.get('org', 'Unknown'),
        'is_vpn': e.get('is_vpn') or e.get('vpnapi_security_vpn', False),
        'is_proxy': e.get('is_proxy') or e.get('vpnapi_security_proxy', False),
        'is_tor': e.get('is_tor') or e.get('vpnapi_security_tor', False),
        'abuse_confidence_score': e.get('abuse_confidence_score', 0),
        'total_reports': e.get('total_reports', 0),
        'last_reported': e.get('recent_comments', [{}])[0].get('date', '')[:10] if e.get('recent_comments') else '',
        'threat_categories': list(set(threat_cats))[:5]
    })

# Verify coverage
print(f"Enrichment entries: {len(enrichment)}")  # Must match data array length

# BAD: Only including first 25 IPs
enrichment = enrichment[:25]  # WRONG

# BAD: Skipping IPs without abuse scores
enrichment = [e for e in enrichment if e['abuse_confidence_score'] > 0]  # WRONG

# GOOD: Include all IPs, even if some fields are empty
enrichment = [transform(e) for e in raw_enrichment]  # All entries

# GOOD: If filtering, filter BOTH data and enrichment consistently
valid_ips = set(e['ip'] for e in enrichment if e.get('city'))
data = [d for d in data if d['ip'] in valid_ips]  # Filter both

W3CIISLog
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude),
    country = take_any(RemoteIPCountry)
    by ip = cIP
| where lat != 0 and lon != 0  // Filter unknown locations
| project ip, lat, lon, value
| order by value desc

CommonSecurityLog
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize 
    value = count(),
    lat = take_any(DeviceGeoLatitude),
    lon = take_any(DeviceGeoLongitude)
    by ip = SourceIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc

<Table>
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize value = count() by ip = <IP_column>
| order by value desc
| take 100

W3CIISLog
| where TimeGenerated > ago(90d)
| where Computer startswith "<honeypot_name>"
| where scStatus == "401"  // Failed auth
| where cIP != "127.0.0.1"
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude),
    country = take_any(RemoteIPCountry)
    by ip = cIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc

W3CIISLog
| where TimeGenerated > ago(30d)
| where tolong(scStatus) >= 400
| where csUriStem has_any ("'", "union", "select", "script", "../", "cmd.exe")
| where cIP != "127.0.0.1"
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude)
    by ip = cIP
| where lat != 0
| project ip, lat, lon, value
| order by value desc
| take 100

CommonSecurityLog
| where TimeGenerated > ago(7d)
| where DeviceAction == "Deny" or Activity has "blocked"
| summarize 
    value = count(),
    lat = take_any(DeviceGeoLatitude),
    lon = take_any(DeviceGeoLongitude)
    by ip = SourceIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc
| take 100

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0  // Failed
| summarize value = count() by ip = IPAddress
| order by value desc
| take 50

python enrich_ips.py <ip1> <ip2> <ip3> ...

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4625
| where LogonType == 10  // RDP
| where IpAddress != "-" and IpAddress != "127.0.0.1"
| summarize value = count() by ip = IpAddress
| order by value desc
| take 50

DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where DeviceName =~ "<device_name>"
| where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted")
| where LocalPort in (3389, 22, 445, 80, 443)
| where RemoteIP !startswith "192.168." and RemoteIP !startswith "10."
| summarize value = count() by ip = RemoteIP
| order by value desc
| take 50

python enrich_ips.py 203.0.113.42 198.51.100.10 192.0.2.1
# Or from file:
python enrich_ips.py --file temp/attack_ips.json

import json

# Load enrichment (now includes latitude/longitude from ipinfo.io)
with open('temp/ip_enrichment_<timestamp>.json', 'r') as f:
    enrichment = json.load(f)

# Build map data
map_data = []
enrichment_out = []

for e in enrichment:
    ip = e['ip']
    lat = e.get('latitude')
    lon = e.get('longitude')
    
    if lat is None or lon is None:
        continue  # Skip IPs without coordinates
    
    # Get value from your KQL results (create a lookup dict)
    value = attack_counts.get(ip, 1)
    
    map_data.append({
        'ip': ip,
        'lat': lat,
        'lon': lon,
        'value': value
    })
    
    # Build enrichment for drill-down
    threat_cats = []
    for c in e.get('recent_comments', [])[:5]:
        threat_cats.extend(c.get('categories', []))
    
    enrichment_out.append({
        'ip': ip,
        'city': e.get('city', 'Unknown'),
        'country': e.get('country', '??'),
        'org': e.get('org', 'Unknown'),
        'is_vpn': e.get('is_vpn') or e.get('vpnapi_security_vpn', False),
        'abuse_confidence_score': e.get('abuse_confidence_score', 0),
        'total_reports': e.get('total_reports', 0),
        'last_reported': e.get('recent_comments', [{}])[0].get('date', '')[:10] if e.get('recent_comments') else '',
        'threat_categories': list(set(threat_cats))[:5]
    })

# 1. Query with native lat/lon from W3CIISLog
mcp_sentinel-data_query_lake({
  "query": "W3CIISLog | where TimeGenerated > ago(90d) | where Computer startswith '<HONEYPOT_SERVER>' | where scStatus == '401' | summarize value = count(), lat = take_any(RemoteIPLatitude), lon = take_any(RemoteIPLongitude), country = take_any(RemoteIPCountry) by ip = cIP | where lat != 0 and lon != 0 | project ip, lat, lon, value | order by value desc"
})

# 2. Enrich top IPs for threat intel drill-down
python enrich_ips.py 101.36.107.228 193.142.147.209 80.190.82.185

# 3. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [
    {"ip": "101.36.107.228", "lat": 22.25, "lon": 114.15, "value": 44},
    {"ip": "80.190.82.185", "lat": 50.97, "lon": 6.83, "value": 44},
    {"ip": "193.142.147.209", "lat": 52.35, "lon": 4.92, "value": 13},
    {"ip": "170.64.158.196", "lat": -33.9, "lon": 151.19, "value": 9}
  ],
  "title": "Honeypot Attack Origins - 90 Day Analysis",
  "valueLabel": "Failed Logins",
  "colorScale": "blue-red",
  "enrichment": [
    {"ip": "101.36.107.228", "city": "Hong Kong", "country": "HK", "org": "AS135377 UCLOUD", "is_vpn": true, "abuse_confidence_score": 100, "total_reports": 4612, "threat_categories": ["SSH", "Brute-Force"]},
    {"ip": "193.142.147.209", "city": "Amsterdam", "country": "NL", "org": "AS213438 ColocaTel", "is_vpn": true, "abuse_confidence_score": 100, "total_reports": 30973, "threat_categories": ["Web App Attack", "Hacking"]}
  ]
})

# 1. Query IPs with failed sign-ins
mcp_sentinel-data_query_lake({
  "query": "SigninLogs | where TimeGenerated > ago(7d) | where ResultType != 0 | summarize value = count() by ip = IPAddress | order by value desc | take 50"
})

# 2. Enrich all IPs (script now captures lat/lon)
python enrich_ips.py <ip1> <ip2> ...

# 3. Load enrichment JSON and build map data
# (See Python code in Enrichment Integration section)

# 4. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<map_data from enrichment>],
  "title": "Failed Sign-In Origins (Last 7 Days)",
  "valueLabel": "Failed Attempts",
  "colorScale": "blue-red",
  "enrichment": [<enrichment_out>]
})

# 1. Query blocked traffic with geo
mcp_sentinel-data_query_lake({
  "query": "CommonSecurityLog | where TimeGenerated > ago(24h) | where DeviceAction == 'Deny' | summarize value = count(), lat = take_any(DeviceGeoLatitude), lon = take_any(DeviceGeoLongitude) by ip = SourceIP | where lat != 0 | project ip, lat, lon, value | order by value desc | take 100"
})

# 2. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<query results>],
  "title": "Blocked Traffic Origins (Last 24h)",
  "valueLabel": "Blocked Connections",
  "colorScale": "blue-red"
})

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>", ...]);

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where RemoteIP in (target_ips)
| summarize 
    ConnectionCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetDevices = make_set(DeviceName, 10),
    TargetPorts = make_set(LocalPort, 20),
    Actions = make_set(ActionType, 5)
    by RemoteIP
| extend Duration = LastSeen - FirstSeen
| order by ConnectionCount desc

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
SecurityEvent
| where TimeGenerated between (start .. end)
| where IpAddress in (target_ips)
| where EventID in (4624, 4625, 4648, 4771, 4776)
| summarize 
    EventCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetComputers = make_set(Computer, 10),
    TargetAccounts = make_set(Account, 20),
    LogonTypes = make_set(LogonType, 5)
    by IpAddress, EventID
| extend EventType = case(
    EventID == 4624, "Successful Logon",
    EventID == 4625, "Failed Logon",
    EventID == 4648, "Explicit Credentials",
    EventID == 4771, "Kerberos Pre-Auth Failed",
    EventID == 4776, "NTLM Auth Attempt",
    "Other")
| project IpAddress, EventType, EventCount, TargetComputers, TargetAccounts, LogonTypes, FirstSeen, LastSeen
| order by EventCount desc