FOCI Client ID Detection
Query
//This query checks sign-in logs for Family of Client IDs (FOCI) applications
//FOCI apps can obtain special family refresh tokens for bearer token redemption
let FOCI = externaldata(ClientID: string, Application: string)[@"https://raw.githubusercontent.com/secureworks/family-of-client-ids-research/main/known-foci-clients.csv"] with (format="csv", ignoreFirstRecord=true);
union SigninLogs,AADNonInteractiveUserSignInLogs
| join kind=leftouter FOCI on $left.AppId == $right.ClientID //Everything from left and only matching from right
| extend isFOCI = iff(isnotempty(ClientID), "1", "0") //yield true if a join was possible between records of the two tables
| project-away ClientIDExplanation
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:
-
Load FOCI Data: It imports a list of known FOCI applications from an external CSV file hosted on GitHub. This file contains two columns:
ClientIDandApplication. -
Combine Logs: It combines data from two sources:
SigninLogsandAADNonInteractiveUserSignInLogs. These sources contain records of user sign-ins. -
Join Data: It performs a left outer join between the combined sign-in logs and the FOCI data based on the
AppIdfrom the sign-in logs and theClientIDfrom the FOCI data. This means it keeps all records from the sign-in logs and adds matching records from the FOCI data where available. -
Identify FOCI Apps: It creates a new column
isFOCIthat indicates whether a sign-in log entry is associated with a FOCI application. If there is a match (i.e., theClientIDis not empty),isFOCIis set to "1"; otherwise, it is "0". -
Remove Redundant Data: It removes the
ClientIDcolumn 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.
Details
Jay Kerai
Released: November 10, 2024
Tables
Keywords
Operators