Cryptsus Blog rss-feed  |  We craft cyber security solutions.

Enrich Geolocation IP in Sentinel SIEM

By: Jeroen van Kessel  |  October 31th, 2022 | 10 min read

Sentinel SIEM can ingest data from different sources such as appliances, applications, and cloud platforms. Not all sources are shipped with geographical enrichment of the IP-address within a log message. This can be a shortcoming when you need to create a specific SIEM use case to detect or block activity outside of your organizational trusted geographical boundaries. This blog post elaborates on how to configure Sentinel GeoIP enrichment on a country basis.

Available options

Microsoft Sentinel provides an API to apply GeoIP enrichment. This API can be useful in specific scenarios, but not for big chunks of ingested Syslog data. The API has a limit of 100 calls, per user, per hour. Furthermore, it is rather network-intensive.

AlienVault OTX, Abuse.ch, and VirusTotal integrate with Sentinel and can apply SOAR-based GeoIP lookups. They can enrich IPs when flagged for possible malicious use or being hijacked. Again, these APIs have rate-limits in-place at the third-party vendor side. These API integrations are more practical for specific SIEM use cases and Incident Response.

How to

Instead of using an API, we download a complete database of all IP-ranges per country. This way, we can map every GeoIP country of all log messages (which contain an IP variable). This is somewhat similar of what Microsoft advises us to do with the ipv4_lookup plugin, but without creating an external_data connection to an online database.

There is a whole business model around GeoIP databases. We picked MaxMind, which provides an excellent free database. However, the paid version of the database should be slightly more accurate. The database contains all public IPv4 ranges allocated to countries by the Internet Assigned Numbers Authority (IANA) via the regional Internet registries (RIRs). You must create an account and download the latest version of the GeoLite2-Country-CSV file. Next, apply VLOOKUPs functions in Python or Excel to match the country ID with the English country name. This should result in the following CountryGeoIP.csv file:

Geographical IP database
Figure 1: Country GeoIP database in CSV format

This GeoIP location database is never entirely accurate. However, in most cases, the database is accurate enough to leverage it for KQL queries to identify from where sessions are established, unless IPs are spoofed, lateral movement is applied, or a VPN is used. Please be aware malicious actors nowadays can use (compromised) systems from the same geolocation as your HQ to launch an attack.

Due to MaxMind licensing constraints, we cannot share the final CSV GeoIP database file. We are not sure why MaxMind does not simply make a pre-compiled English version of the GeoIP database available and ready for use. This would save us from performing VLOOKUPS. Please check the license agreement with MaxMind to see whether you can use the paid or free GeoIP database. You have to perform manual actions to add the country names. Alternatively, download this outdated 5-year-old GeoIP county database from GitHub.

Configure the GeoIP database list in Sentinel

First, create an Azure Storage account and container and upload the CountryGeoIP CSV database file. Next, go to Sentinel -> Watchlist -> Add new -> and refer to the CSV file.

sentinel-geographical-database-watchlist
Figure 2: Watchlist Sentinel GeoIP

Now we have a Sentinel Watchlist with all IP ranges correlated to each country.

KQL Example:

Let's say you have the following VPN log entry, which has a source IP-address:

sentinel-log-entry
Figure 3: Regular VPN log entry

This VPN log entry only outputs the public Global routable IP without any geolocation enrichment. This is normal since most data log sources do not apply GeoIP lookups at the source.

Next, we can leverage KQL queries to a VPN IP to a country name. Simple, fast, efficient, and costs effective. Let's apply the KQL evaluate function, together with the ipv4_lookup plugin in a KQL query:

//Load MaxMind GeoIP database in Sentinel
let GeoIPDB = _GetWatchlist("GeoIPCountry");
//Filter on successful VPN sessions
let VPN = PulseConnectSecure
 | where EventResult == "success"
 | distinct Source_IP, User
 | project VPN_IP = Source_IP, User;
//Translate IPv4 addresses to GeoIP-based country names
VPN
 | evaluate ipv4_lookup(GeoIPDB, VPN_IP, network)
 | where country_code != "NL"
 | summarize count() by User, country_code
 | sort by count_

We can also make a list of all the different geographical countries within in the last 7 days based on successful VPN sessions:

//Load MaxMind GeoIP database in Sentinel
let GeoIPDB = _GetWatchlist("GeoIPCountry");
//Filter on successful VPN sessions
let VPN = PulseConnectSecure
 | where EventResult == "success"
 | distinct Source_IP, User
 | project VPN_IP = Source_IP, User;
//Translate IPv4 addresses to GeoIP-based country names
VPN
 | evaluate ipv4_lookup(GeoIPDB, VPN_IP, network)
 | where country_code != "NL"
 | summarize ['Geo_locations']=make_list(country_name), ['Geo_changes']=count() by User
 | where ['Geo_changes'] > 1
 | sort by Geo_changes

compromised-geographical-ip-hacked
Figure 4: Output VPN logs with GeoIP country location

These KQL Kusto queries can also be found on GitHub.

Next, you also use this output data to create a geolocation map in a custom Workbook within Sentinel SIEM to monitor your non-trusted geo-based activities:

hijacked-geographical-map-world-hacked
Figure 5: Map of possible malicious VPN sessions

Second thoughts

Optional: you can automate downloading a new database version with a MaxMind license key (curl -sS), unzip, VLOOKUP (Python3) the country code and name, and finally upload the CSV database to an Azure storage account (API) on a monthly basis. This way, Sentinel will automatically update the database. You can also perform an update manually once in a while.

Furthermore, you can also use the GeoIP City list from MaxMind.

Also, don't forget about the IPv6 address space. Note: a built-in ipv6_lookup plugin is not yet available in Sentinel.

Many other use cases and alerts can be created based on your environment and business risks. Are you missing important alerts? Is your SIEM outdated? Contact us if you want to take your SIEM to the next level.

Discussion and questions