KQL Search

Query Details

HomeAI ToolsDevice Query

Parsing Cisco Umbrella Logs

Query

// This query can help you to parse Cisco Umbrella DNS events received in Cisco_Umbrella_dns_CL table.
Cisco_Umbrella_dns_CL
| parse QueryType_s with DnsQueryType:int " (" DnsQueryTypeName:string ")"
| mv-apply Identities_s_aux = todynamic(Identities_s) to typeof(string), Identity_Types_s_aux = todynamic(Identity_Types_s) to typeof(string) on ( 
    summarize Identities = make_bag(bag_pack(Identity_Types_s_aux, Identities_s_aux))
    )
| project
    EventStartTime = todatetime(column_ifexists('Timestamp_t', column_ifexists('Timestamp_s',''))),
    Identities,
    SrcIpAddr = column_ifexists('InternalIp_s', ''),
    SrcNatIpAddr = column_ifexists('ExternalIp_s', ''),
    DnsQueryType,
    DnsQueryTypeName,
    DnsQuery = trim_end(@'\.', column_ifexists('Domain_s', '')),
    EventResult = iff(ResponseCode_s =~ 'NOERROR', 'Success', 'Failure'),
    EventResultDetails = ResponseCode_s,  // => ResponseCodeNames
    DvcAction = column_ifexists('Action_s', ''),
    UrlCategory = column_ifexists('Categories_s', ''),
    ThreatCategory = column_ifexists('Blocked_Categories_s', ''),
    PolicyIdentityType = column_ifexists('Policy_Identity_Type_s', ''),
    PolicyIdentity = column_ifexists('Policy_Identity_s', ''),
    Dvc = 'CiscoUmbrella',
    TimeGenerated

Explanation

This query is designed to parse and analyze DNS events from the Cisco Umbrella service, which are stored in the Cisco_Umbrella_dns_CL table.

The query first parses the QueryType_s field to extract the DNS query type and its name. It then applies a transformation to the Identities_s and Identity_Types_s fields, converting them to string type and summarizing them into a bag of identities.

Next, the query projects a set of fields, including the event start time, identities, source IP address, source NAT IP address, DNS query type and name, DNS query, event result and details, device action, URL category, threat category, policy identity type and policy identity.

The event start time is extracted from either the 'Timestamp_t' or 'Timestamp_s' field, if they exist. The source IP addresses are extracted from the 'InternalIp_s' and 'ExternalIp_s' fields. The DNS query is trimmed of any trailing periods from the 'Domain_s' field. The event result is determined based on whether the 'ResponseCode_s' field equals 'NOERROR'.

The device action, URL category, threat category, policy identity type and policy identity are extracted from their respective fields, if they exist. The device is set as 'CiscoUmbrella'. The query also includes the time the event was generated.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: September 7, 2023

Tables

Cisco_Umbrella_dns_CL

Keywords

CiscoUmbrella,DNSEvents,QueryType,Identities,EventStartTime,SourceIPAddress,SourceNATIPAddress,DNSQuery,EventResult,EventResultDetails,DeviceAction,URLCategory,ThreatCategory,PolicyIdentityType,PolicyIdentity,Device,TimeGenerated

Operators

parsemv-applytodynamictypeofsummarizemake_bagbag_packprojecttodatetimecolumn_ifexiststrim_endiff

Actions