KQL Search

This KQL query is designed to analyze successful sign-in logs from the last 90 days and enrich them with country information. Here's a simple breakdown of what it does:

1. Load Country Codes: It retrieves a list of country codes and names from an external CSV file hosted on GitHub. This file contains mappings of country names and regions.

2. Filter Sign-in Logs: It filters the SigninLogs table to include only records from the past 90 days where the sign-in was successful (indicated by ResultType == 0).

3. Extract Country/Region: It extracts the countryOrRegion information from the LocationDetails of each sign-in log.

4. Join with Country Codes: It performs a left outer join with the country codes data to match the countryOrRegion from the sign-in logs with the corresponding country name.

5. Filter Non-empty Locations: It ensures that only records with non-empty countryOrRegion fields are considered.

6. Create VirusTotal IP Links: For each record, it creates a link to VirusTotal's IP address information page if an IP address is available.

7. Summarize Data: It summarizes the data by counting the number of sign-ins for each combination of country, user principal name, and VirusTotal IP link.

8. Standardize Country Name: It standardizes the country name for the United Kingdom by replacing "United Kingdom of Great Britain and Northern Ireland" with "United Kingdom".

In essence, this query provides a summary of successful sign-ins over the last 90 days, enriched with country information and links to IP address details on VirusTotal.