KQL Search

This KQL (Kusto Query Language) query performs the following actions:

1. Load External Data: It loads a CSV file containing country codes and names from a specified URL into a temporary table called CountryCodes. The CSV file is expected to have columns for country and countryOrRegion, and the first record (header) is ignored.

2. Filter Sign-in Logs: It queries the SigninLogs table to retrieve records where:

   • The TimeGenerated is within the last 90 days.
   • The ResultType is 0, indicating successful sign-ins.
   • The countryOrRegion field is not empty.

3. Extend and Join: It converts the LocationDetails.countryOrRegion field to a string and joins the SigninLogs with the CountryCodes table on the countryOrRegion field using a left outer join. This means all records from SigninLogs are kept, and matching records from CountryCodes are added where available.

4. Create VirusTotal IP Links: It creates a new field VT_IP that contains a URL to VirusTotal for each IP address. If the IPAddress is not empty, it constructs a URL using the IP address; otherwise, it leaves the IP address as is.

5. Summarize Data: It summarizes the data by counting the number of sign-ins grouped by country, UserPrincipalName, and VT_IP.

6. Exclude Specific Country: It filters out any records where the country is "United Kingdom of Great Britain and Northern Ireland".

In simple terms, this query analyzes successful sign-in logs from the past 90 days, enriches them with country information, creates links to VirusTotal for each IP address, and summarizes the data by user and country, excluding entries from the UK.