Hi @Clive_Watson
Thanks for the reply!!!! But I want to understand why the counter shows 38 failed login attempts and after checking the events in the "AADNonInteractiveUserSignInLogs" table it does not show 38 events but only one. This search was already done at the AD level looking for the Non interactive sign-in within Azure AD, in the documentation it is explained that these events can be grouped, but I can't find the logic because I can't find the reason why the user has failed 38 times.
I have tried your suggestions, in line 53 I do not understand very well what I want to indicate. I have modified line 80 with what you indicate and it doesn't give me anything new to what I already had. I attach the modified query with what you have indicated.
// The query_now parameter represents the time (in UTC) at which the scheduled analytics rule ran to produce this alert.
set query_now = datetime(2022-04-12T22:27:38.0351487Z);
let timeRange = 1d;
let lookBack = 7d;
let threshold_Failed = 5;
let threshold_FailedwithSingleIP = 20;
let threshold_IPAddressCount = 2;
let isGUID = "[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}";
let aadFunc = (tableName: string) {
let azPortalSignins = materialize(table(tableName)
| where TimeGenerated >= ago(lookBack)
// Azure Portal only
| where AppDisplayName =~ "Azure Portal")
;
let successPortalSignins = azPortalSignins
| where TimeGenerated >= ago(timeRange)
// Azure Portal only and exclude non-failure Result Types
| where ResultType in ("0", "50125", "50140")
// Tagging identities not resolved to friendly names
//| extend Unresolved = iff(Identity matches regex isGUID, true, false)
| distinct TimeGenerated, UserPrincipalName
;
let failPortalSignins = azPortalSignins
| where TimeGenerated >= ago(timeRange)
// Azure Portal only and exclude non-failure Result Types
| where ResultType !in ("0", "50125", "50140")
// Tagging identities not resolved to friendly names
| extend Unresolved = iff(Identity matches regex isGUID, true, false)
;
// Verify there is no success for the same connection attempt after the fail
let failnoSuccess = failPortalSignins
| join kind= leftouter (
successPortalSignins
)
on UserPrincipalName
| where TimeGenerated > TimeGenerated1 or isempty(TimeGenerated1)
| project-away TimeGenerated1, UserPrincipalName1
;
// Lookup up resolved identities from last 7 days
let identityLookup = azPortalSignins
| where TimeGenerated >= ago(lookBack)
| where not(Identity matches regex isGUID)
| summarize
by
UserId,
lu_UserDisplayName = UserDisplayName,
lu_UserPrincipalName = UserPrincipalName;
// Join resolved names to unresolved list from portal signins
let unresolvedNames = failnoSuccess
| where Unresolved == true
| join kind= inner (
identityLookup
)
on UserId
| extend UserDisplayName = lu_UserDisplayName, UserPrincipalName = lu_UserPrincipalName
| project-away lu_UserDisplayName, lu_UserPrincipalName;
// Join Signins that had resolved names with list of unresolved that now have a resolved name
let u_azPortalSignins = failnoSuccess
| where Unresolved == false
| union unresolvedNames;
u_azPortalSignins
| extend
DeviceDetail = todynamic(DeviceDetail),
Status = todynamic(DeviceDetail),
LocationDetails = todynamic(LocationDetails)
| extend
Status = strcat(ResultType, ": ", ResultDescription),
OS = tostring(DeviceDetail.operatingSystem),
Browser = tostring(DeviceDetail.browser)
| extend
State = tostring(LocationDetails.state),
City = tostring(LocationDetails.city),
Region = tostring(LocationDetails.countryOrRegion)
| extend FullLocation = strcat(Region, '|', State, '|', City)
| summarize
TimeGenerated = makelist(TimeGenerated),
Status = makelist(Status),
IPAddresses = makelist(IPAddress),
IPAddressCount = dcount(IPAddress),
FailedLogonCount = countif(ResultType=='50173')
by
UserPrincipalName,
UserId,
UserDisplayName,
AppDisplayName,
Browser,
OS,
FullLocation,
Type
| mv-expand TimeGenerated, IPAddresses, Status
| extend
TimeGenerated = todatetime(tostring(TimeGenerated)),
IPAddress = tostring(IPAddresses),
Status = tostring(Status)
| project-away IPAddresses
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated)
by
UserPrincipalName,
UserId,
UserDisplayName,
Status,
FailedLogonCount,
IPAddress,
IPAddressCount,
AppDisplayName,
Browser,
OS,
FullLocation,
Type
| where (IPAddressCount >= threshold_IPAddressCount and FailedLogonCount >= threshold_Failed) or FailedLogonCount >= threshold_FailedwithSingleIP
| extend
timestamp = StartTime,
AccountCustomEntity = UserPrincipalName,
IPCustomEntity = IPAddress
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt
