KQL Search

Query Details

HomeAI ToolsDevice Query

Analytics Auditor Alerts

Query

// This query can help you to filter certain security alerts depending on the auditor of the events.
// This function will need the list of auditors you want to filter, and the rule ids that generate the security alerts.
//
// Click "Save as function", in Parameters write in the fields:
// "dynamic"  "rule_auditors"      "dynamic([])"
// "dynamic"  "monitored_rule_ids" "dynamic([])"
//
// If you name the function "AuditorAlerts", you can check the function with queries like the following:
//
// AuditorAlerts(dynamic(["SOC", "Security Architecture"]), dynamic(["00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000001"]))
//
//let Function = (rule_auditors:dynamic = dynamic([]), monitored_rule_ids:dynamic = dynamic([])){
    SecurityAlert
    | where AlertType has_any (monitored_rule_ids)
    | project
        AlertName,
        AlertSeverity,
        AlertDescription = Description,
        Tactics,
        Entities,
        ExtractedEvents = set_union(extract_all(@"\'([^\']+)\'", dynamic([1]), tostring(todynamic(ExtendedProperties).Query)), dynamic([]))
    | mv-expand ExtractedEvents to typeof(string)
    | extend BagToUnpack = todynamic(zlib_decompress_from_base64_string(ExtractedEvents))
    | evaluate bag_unpack(BagToUnpack, columnsConflict="replace_source")
    | extend Auditors = column_ifexists("Auditors", "")
    | mv-expand Auditor = split(Auditors, " & ") to typeof(string)
    | where Auditor in (rule_auditors)
    | extend AlertName = replace_string(AlertName, "monitored", strcat(iff(isnotempty(Auditor), strcat(Auditor, " "), ""), "monitored"))
    | project-away ExtractedEvents
    | extend
        TimeGenerated = column_ifexists("TimeGenerated", datetime(null)),
        Activity = column_ifexists("Activity", ""),
        ActorAccount = column_ifexists("ActorAccount", ""),
        ActorSid = column_ifexists("ActorSid", ""),
        ActorAccountSid = column_ifexists("ActorAccountSid", ""),
        ActorAccountType = column_ifexists("ActorAccountType", ""),
        ActorDomainName = column_ifexists("ActorDomainName", ""),
        GroupName = column_ifexists("GroupName", ""),
        GroupSid = column_ifexists("GroupSid", ""),
        MemberAccount = column_ifexists("MemberAccount", ""),
        MemberSid = column_ifexists("MemberSid", ""),
        Computer = column_ifexists("Computer", ""),
        EventData = column_ifexists("EventData", ""),
        AttributeValue_EventData = column_ifexists("AttributeValue_EventData", ""),
        AttributeLDAPDisplayName = column_ifexists("AttributeLDAPDisplayName", ""),
        OperationTypeTranslated = column_ifexists("OperationTypeTranslated", ""),
        ModifiedAttributeValue = column_ifexists("ModifiedAttributeValue", ""),
        UserIdentityAccountName = column_ifexists("UserIdentityAccountName", ""),
        UserIdentityAccountId = column_ifexists("UserIdentityAccountId", ""),
        Identity = column_ifexists("Identity", ""),
        RequestedRole = column_ifexists("RequestedRole", "")
    | project-reorder
        TimeGenerated
//};
//Function(rule_auditors, monitored_rule_ids)

Explanation

This query is designed to filter security alerts based on the auditor of the events. It requires a list of auditors and rule IDs that generate the security alerts.

The function, when saved as "AuditorAlerts", can be used to filter alerts by specifying the auditors and rule IDs.

The query first selects security alerts that match the provided rule IDs. It then extracts various details about the alert such as its name, severity, description, tactics, entities, and extracted events.

It then decompresses the extracted events and unpacks the resulting data. It also creates a new column for auditors and splits any auditors that are listed together.

The query then filters the alerts based on the provided auditors and modifies the alert name to include the auditor's name if it is not empty.

Finally, it ensures that certain columns exist in the data and reorders the columns to place 'TimeGenerated' at the beginning.

In simple terms, this query helps to filter and provide detailed information about security alerts based on specific auditors and rule IDs.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: September 7, 2023

Tables

SecurityAlert

Keywords

SecurityAlerts,Auditor,RuleIDs,Function,AlertType,AlertName,AlertSeverity,AlertDescription,Tactics,Entities,ExtendedProperties,Auditors,TimeGenerated,Activity,ActorAccount,ActorSid,ActorAccountSid,ActorAccountType,ActorDomainName,GroupName,GroupSid,MemberAccount,MemberSid,Computer,EventData,AttributeValueEventData,AttributeLDAPDisplayName,OperationTypeTranslated,ModifiedAttributeValue,UserIdentityAccountName,UserIdentityAccountId,Identity,RequestedRole

Operators

SecurityAlertwherehas_anyprojectAlertNameAlertSeverityDescriptionTacticsEntitiesset_unionextract_alldynamictostringtodynamicExtendedPropertiesQuerymv-expandtypeofextendBagToUnpacktodynamiczlib_decompress_from_base64_stringevaluatebag_unpackcolumnsConflictreplace_sourcecolumn_ifexistssplitinreplace_stringstrcatiffisnotemptyproject-awaydatetimeproject-reorder.

Actions