KQL Search

Query Details

HomeAI ToolsDevice Query

Security Event User Principal Name Assigned To User Account

Query

SecurityEvent
| where EventID == 5136 and EventData has_all ("servicePrincipalName", "user")
| project TimeGenerated, Computer, Account, Activity, SubjectLogonId, OperationType, EventData
| mv-apply Auxiliar = parse_xml(EventData)["EventData"]["Data"] on (
    summarize BagToUnpack = make_bag(pack(tostring(Auxiliar["@Name"]), tostring(Auxiliar["#text"])))
    )
| evaluate bag_unpack(BagToUnpack, columnsConflict="keep_source"): (TimeGenerated: datetime, Computer: string, Account: string, Activity: string, SubjectLogonId: string, OperationType: string, EventData: string, OpCorrelationID: string, DSName:string, ObjectDN: string, ObjectGUID: string, ObjectClass: string, AttributeLDAPDisplayName: string, AttributeValue: string)
| where AttributeLDAPDisplayName =~ "servicePrincipalName" and ObjectClass =~ "user"
| extend
    TargetUserName = extract(@"(?i:CN\=)([^,]+)", 1, ObjectDN),
    ObjectGUID = toupper(ObjectGUID),
    TargetDomain = DSName
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    ValueAdded_TimeGenerated = maxif(TimeGenerated, OperationType == "%%14674"),
    ValueDeleted_TimeGenerated = maxif(TimeGenerated, OperationType == "%%14675"),
    SubjectLogonIds = make_set(SubjectLogonId),
    Computers = make_set(Computer),
    take_any(Activity, TargetUserName, AttributeLDAPDisplayName)
    by Account, TargetDomain, ObjectGUID, AttributeValue
| extend AddedOrDeleted = case(
        isnotempty(ValueAdded_TimeGenerated) and isempty(ValueDeleted_TimeGenerated), "Added",
        isnotempty(ValueAdded_TimeGenerated) and isnotempty(ValueDeleted_TimeGenerated) and ValueAdded_TimeGenerated > ValueDeleted_TimeGenerated, "Added",
        isempty(ValueAdded_TimeGenerated) and isnotempty(ValueDeleted_TimeGenerated), "Deleted",
        isnotempty(ValueAdded_TimeGenerated) and isnotempty(ValueDeleted_TimeGenerated) and ValueAdded_TimeGenerated < ValueDeleted_TimeGenerated,  "Deleted",
        ""
    )
| summarize
    StartTime = min(StartTime),
    EndTime = max(EndTime),
    ValueAdded = array_sort_asc(make_set_if(AttributeValue, AddedOrDeleted == "Added")),
    ValueDeleted = array_sort_asc(make_set_if(AttributeValue, AddedOrDeleted == "Deleted")),
    SubjectLogonIds = make_set(SubjectLogonIds),
    Computers = array_sort_asc(make_set(Computers)),
    take_any(Activity, TargetUserName, AttributeLDAPDisplayName)
    by Account, TargetDomain, ObjectGUID
| project
    StartTime,
    EndTime,
    Computers,
    Account,
    Activity,
    TargetDomain,
    TargetUserName,
    AttributeLDAPDisplayName,
    ValueAdded,
    ValueDeleted,
    ObjectGUID,
    SubjectLogonIds

Explanation

This query is analyzing security events with an ID of 5136 that contain both "servicePrincipalName" and "user" in their event data. It then extracts and organizes various pieces of information from these events, such as the time they were generated, the computer they occurred on, the account involved, the type of operation, and more.

The query then filters this data to only include events where the LDAP display name is "servicePrincipalName" and the object class is "user". It also extracts the target user name, converts the object GUID to uppercase, and identifies the target domain.

Next, the query summarizes the data by the account, target domain, object GUID, and attribute value. It identifies the start and end times of the events, the time when a value was added or deleted, the subject logon IDs, and the computers involved.

The query then determines whether a value was added or deleted based on the times these operations occurred. It further summarizes the data by the account, target domain, and object GUID, sorting the added and deleted values, subject logon IDs, and computers in ascending order.

Finally, the query projects the start and end times, computers, account, activity, target domain, target user name, LDAP display name, added and deleted values, object GUID, and subject logon IDs.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: May 3, 2023

Tables

SecurityEvent

Keywords

SecurityEvent,EventID,EventData,ServicePrincipalName,User,TimeGenerated,Computer,Account,Activity,SubjectLogonId,OperationType,Auxiliar,BagToUnpack,OpCorrelationID,DSName,ObjectDN,ObjectGUID,ObjectClass,AttributeLDAPDisplayName,AttributeValue,TargetUserName,TargetDomain,StartTime,EndTime,ValueAdded_TimeGenerated,ValueDeleted_TimeGenerated,SubjectLogonIds,Computers,AddedOrDeleted,ValueAdded,ValueDeleted

Operators

SecurityEventwhereEventIDEventDatahas_allprojectTimeGeneratedComputerAccountActivitySubjectLogonIdOperationTypemv-applyAuxiliarparse_xmlsummarizeBagToUnpackmake_bagpacktostringevaluatebag_unpackcolumnsConflictextendTargetUserNameextractObjectGUIDtoupperTargetDomainDSNameminmaxmaxifmake_settake_anybycaseisnotemptyisemptyarray_sort_ascmake_set_ifproject.

Actions