Who rated this post

@WilliamHolloway @TDoerr 
This probably answer half of your question, the query below is calculating the number of authentication failures per user/endpoint with the failure reason.
I figure you could start from here.

dataset = xdr_data // Using the xdr dataset

//Query against Windows Security Event ID 4625 - Authentication Failure
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4625
| alter Workstation_Name = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.WorkstationName" ))
| alter Target_UserName = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.TargetUserName" ))
| alter Target_DomainName = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.TargetDomainName" ))
| alter Workstation_IP = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.IpAddress" ))
| alter Status = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.Status" ))
| alter SubStatus = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.SubStatus" ))
| alter LogonType = lowercase(json_extract_scalar(to_json_string(action_evtlog_data_fields), "$.LogonType" ))
| filter Target_UserName not contains "$"
| comp count(Status) as EventCount by Workstation_Name, Target_UserName, Target_DomainName , Status, SubStatus

// If the status/substatus description is empty, See https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55
| alter Status_Description = if(to_string(Status) = "0xC000006d", replace(Status, "0xC000006d", "This is either due to a bad username or authentication information"))
| alter Status_Description = if(to_string(Status) = "0xC0000234", replace(Status, "0xC0000234", "User is currently locked out"), Status_Description )
| alter Status_Description = if(to_string(Status) = "0xC000006e", replace(Status, "0xC000006e", "Valid authentication, but restricted."), Status_Description )

| alter SubStatus_Description = if(to_string(SubStatus) = "0xC000006a", replace(SubStatus, "0xC000006a", "User name is correct but the password is wrong"))
| alter SubStatus_Description = if(to_string(SubStatus) = "0xC0000064", replace(SubStatus, "0xC0000064", "User name does not exist"), SubStatus_Description )
| alter SubStatus_Description = if(to_string(SubStatus) = "0xC0000071", replace(SubStatus, "0xC0000071", "Expired password"), SubStatus_Description )
| alter SubStatus_Description = if(to_string(SubStatus) = "0xC0000072", replace(SubStatus, "0xC0000072", "Account is currently disabled"), SubStatus_Description )
| alter SubStatus_Description = if(to_string(SubStatus) = "0xC0000193", replace(SubStatus, "0xC0000193", "Account expiration"), SubStatus_Description )

| sort desc Workstation_Name 
| fields Workstation_Name as Hostname , Target_UserName as Username, Target_DomainName as Domain, EventCount, Status, Status_Description , SubStatus , SubStatus_Description 

 hope this help.