KQL Search

This KQL query is designed to analyze sign-in logs to identify applications that are part of the Family of Client IDs (FOCI). Here's a simplified breakdown of what the query does:

1. Load FOCI Data: It imports a list of known FOCI applications from an external CSV file hosted on GitHub. This file contains two columns: ClientID and Application.

2. Combine Logs: It combines data from two sources: SigninLogs and AADNonInteractiveUserSignInLogs. These sources contain records of user sign-ins.

3. Join Data: It performs a left outer join between the combined sign-in logs and the FOCI data based on the AppId from the sign-in logs and the ClientID from the FOCI data. This means it keeps all records from the sign-in logs and adds matching records from the FOCI data where available.

4. Identify FOCI Apps: It creates a new column isFOCI that indicates whether a sign-in log entry is associated with a FOCI application. If there is a match (i.e., the ClientID is not empty), isFOCI is set to "1"; otherwise, it is "0".

5. Remove Redundant Data: It removes the ClientID column from the final output, as it is no longer needed after determining whether an application is a FOCI app.

In summary, the query checks sign-in logs to identify which applications are part of the FOCI group by matching them with a known list of FOCI applications and marks them accordingly.