topic Re: parsing issue in DQL

parsing issue

sharmas2 — Fri, 16 May 2025 08:50:48 GMT

I am looking for an ouput as below .

market	Total	albania	czech	ghana	greece	ireland	itfixednw	netherlands	nlziggo	portugal	spain	uk	uklm	voisind
Blocked	9001175	10836	56008	1416856	432310	1519	5290800	501189	45	11028	221512	1054954	3645	473
Detected	1474991	2158	1152029	19880	763	3282	35419	47660	971	1099	6314		205394	22

Below query is not giving the required output ..

fetch logs // scanLimitGBytes: , samplingRatio: 1000
| filter dt.system.bucket=="custom_group_security"
| filter contains(dt.security_context, "_sec_intel")
| filter contains(dt.security_context, "_sourcefire_secevents")
| filterOut contains(dt.security_context ,"ngss")
| filterOut contains(dt.security_context ,"gdc")
| filterOut contains(dt.security_context ,"sspp")
| parse content,"LD 'URLSICategory:' LD:URLSICategory ','"
| parse content,"LD 'URLSICategory :' LD:URLSICategory2 ','"
| parse content,"LD 'URLSICategory=' LD:URLSICategory3 ','"
| fieldsAdd URLSICategory = coalesce(URLSICategory, URLSICategory2)
| fieldsAdd URLSICategory = coalesce(URLSICategory, URLSICategory3)
| parse content, """LD 'DNSSICategory=' [^,]{1,100}?:DNSSICategory "," """
| parse content,"""LD 'InlineResultID":' LONG:InlineResultID "," """
| parse content, """LD 'IPReputationSICategory=' [^,]{1,100}?:IPReputationSICategory "," """
| parse content,"LD 'AccessControlRuleAction: '[0-9a-zA-Z-]{1,100}?:AccessControlRuleAction','"
| fieldsAdd Status = if( (InlineResultID == 5 OR InlineResultID == 0 OR InlineResultID == 2) ,"Detected",else:
if( (InlineResultID ==1 OR InlineResultID == 4 OR RuleAction == "Block" OR isnull(URLSICategory) OR isnull(DNSSICategory) AND isnotnull(IPReputationSICategory)), "Blocked", else:null))
| filter isnotnull(Status)
| summarize count(),by:{Status,market}
//| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
//| fieldsAdd market = replaceString(market, "cita", "")
//| fieldsAdd date = formatTimestamp(timestamp, format:"MM-dd-yyyy")
//| summarize co{market}, by:{date,Status}
//| fieldsSummary market ,Status

Please find the below raw data as well :-

| filter contains(dt.security_context, "_sec_intel")

====================================
May 08 2025 14:48:27 VCIMilanSecIntel : %FTD-1-430002: EventPriority=High, DeviceUUID=3fca3c60-fcae-11de-91ea-975343494af9, InstanceID=9, FirstPacketSecond=2025-05-08T14:48:27Z, ConnectionID=10078, AccessControlRuleAction=Block, AccessControlRuleReason=IP Block, SrcIP=83.222.191.170, DstIP=37.25.168.243, SrcPort=40227, DstPort=7356, Protocol=tcp, IngressInterface=DPB1-IN, EgressInterface=DPB1-OUT, IngressZone=CITADEL-Milan-DPB1, EgressZone=CITADEL-Milan-DPB1, ACPolicy=NGSS2-Milan, Prefilter Policy=NGSS2-Milan-Prefilter, InitiatorPackets=1, ResponderPackets=0, InitiatorBytes=58, ResponderBytes=0, SecIntMatchingIP=Source, IPReputationSICategory=Custom-Blocklist-Global-IP_FS, ClientAppDetector=AppID, HostName=IT1IPS03BIN1-1_2

| filter contains(dt.security_context, "_sourcefire_secevents")

==========================================

{"EventType":"IntrusionEvent","EventSecond":1746715592,"EventMicrosecond":157111,"DeviceUUID":"3fca3c60-fcae-11de-91ea-975343494af9","InstanceID":18,"FirstPacketSecond":1746715591,"ConnectionID":37483,"InitiatorIP":"157.72.65.130","ResponderIP":"195.232.147.125","InitiatorPort":11768,"ResponderPort":123,"Protocol":"udp","IngressInterface":"DPB1-IN","EgressInterface":"DPB1-OUT","IngressZone":"CITADEL-Milan-DPB1","EgressZone":"CITADEL-Milan-DPB1","PriorityID":2,"GeneratorID":3,"SignatureID":39878,"SignatureRevision":4,"Impact":2,"IntrusionRuleMessage":"SERVER-OTHER Cisco IOS truncated NTP packet processing denial of service attempt","Classification":"Inappropriate content was detected","IntrusionPolicy":"Milan-Blocking-Policy_S3","FirewallPolicy":"NGSS2-Milan","FirewallRule":"To Legacy applications","NAP_Policy":"NGSS2-Milan-NAP-Snort3","InlineResult":"Block","VLAN_ID":3101,"Device":"IT1IPS03BIN1-1_2","DeviceIP":"198.19.40.21","DeviceSerialNumber":"FLM2443067L","EgressInterfaceUUID":"47a1da92-a6ac-11eb-b4ad-fb6eab37a80a","EgressZoneUUID":"47b3a240-a6ac-11eb-b4ad-fb6eab37a80a","EventID":1127183,"FirewallPolicyUUID":"00000000-0000-0000-0000-0000681c487b","FirewallRuleID":268436578,"Hostname":"IE1FMCATS1-A1","ImpactFlag":7,"IngressInterfaceUUID":"4759ef8e-a6ac-11eb-b4ad-fb6eab37a80a","IngressZoneUUID":"47b3a240-a6ac-11eb-b4ad-fb6eab37a80a","InitiatorContinent":"Asia","InitiatorContinentCode":"as","InitiatorCountry":"Japan","InitiatorCountryCode":"jpn","InitiatorCountryID":392,"InlineResultID":4,"IntrusionPolicyRevUUID":"82e629b0-6543-11ef-b36c-117b0c432ecb","IntrusionPolicyUUID":"a488735a-d4ea-0ed3-0000-326418261953","NAP_PolicyUUID":"cf0ab078-437d-11ef-ab6b-97ee0b432ecb","ProtocolID":17,"RealmID":0,"RealmName":"Invalid ID","ResponderContinent":"Europe","ResponderContinentCode":"eu","ResponderCountry":"Germany","ResponderCountryCode":"deu","ResponderCountryID":276,"SensorID":2,"SnortVersionID":3,"UserID":9999997}

Re: parsing issue

Akhil-Jayendran — Mon, 19 May 2025 01:23:50 GMT

Hello @sharmas2 ,

It looks like your raw data doesn’t contain all the expected fields, but the data appears to be in JSON format. I believe you can use a simple DQL query like the one below.

fetch logs | filter matchesPhrase(dt.security_context,"_sec_intel") or matchesPhrase(dt.security_context,"_sourcefire_secevents") | fieldsAdd Status = if( (inlineresultid == "5" OR inlineresultid == "0" OR inlineresultid == "2") ,"Detected",else: if( (inlineresultid == "1" OR inlineresultid == "4" OR accesscontrolruleaction == "Block" OR isnull(URLSICategory) OR isnull(DNSSICategory) AND isnotnull(IPReputationSICategory)), "Blocked", else:null)) | fieldsRename market=respondercountry | fieldsKeep Status, inlineresultid,accesscontrolruleaction,market |summarize count(),by:{Status,market}

You might get result like this.

Br,

Akhil

Re: parsing issue

sharmas2 — Mon, 19 May 2025 16:15:11 GMT

no i want result as below ..

Re: parsing issue

Akhil-Jayendran — Mon, 19 May 2025 20:46:08 GMT

Hello @sharmas2 ,

In this case, you may need to modify the DQL to suit your requirements. Here’s an example

fetch logs | filter matchesPhrase(dt.security_context,"_sec_intel") or matchesPhrase(dt.security_context,"_sourcefire_secevents") | fieldsAdd Status = if( (inlineresultid == "5" OR inlineresultid == "0" OR inlineresultid == "2") ,"Detected", else:if( (inlineresultid == "1" OR inlineresultid == "4" OR accesscontrolruleaction == "Block" OR isnull(URLSICategory) OR isnull(DNSSICategory) AND isnotnull(IPReputationSICategory)), "Blocked", else:null )) | fieldsRename market=respondercountry | summarize by:{Status,market}, {count=count()} | summarize {market_details= collectArray(array(toString(market),count ))}, by:{Status} | fieldsAdd market_details=toString(market_details) | parse market_details, "'[' KVP{ '[' DQS:key ', ' INT:value ']' ', '?}:market_details ']'"

You can see the data like this ? hope this will helps!

Br,
Akhil

Re: parsing issue

sharmas2 — Tue, 20 May 2025 08:21:07 GMT

Hi Akhil,

thanks for your efforts but i am getting output as below ..

Re: parsing issue

Akhil-Jayendran — Tue, 20 May 2025 20:47:52 GMT

Hello @sharmas2 ,

You can try this , This is a sample query you should adjust this query according to your needs.

data json:"""[ {"EventType":"IntrusionEvent","EventSecond":1746715592,"EventMicrosecond":157111,"DeviceUUID":"3fca3c60-fcae-11de-91ea-975343494af9","InstanceID":18,"FirstPacketSecond":1746715591,"ConnectionID":37483,"InitiatorIP":"157.72.65.130","ResponderIP":"195.232.147.125","InitiatorPort":11768,"ResponderPort":123,"Protocol":"udp","IngressInterface":"DPB1-IN","EgressInterface":"DPB1-OUT","IngressZone":"CITADEL-Milan-DPB1","EgressZone":"CITADEL-Milan-DPB1","PriorityID":2,"GeneratorID":3,"SignatureID":39878,"SignatureRevision":4,"Impact":2,"IntrusionRuleMessage":"SERVER-OTHER Cisco IOS truncated NTP packet processing denial of service attempt","Classification":"Inappropriate content was detected","IntrusionPolicy":"Milan-Blocking-Policy_S3","FirewallPolicy":"NGSS2-Milan","FirewallRule":"To Legacy applications","NAP_Policy":"NGSS2-Milan-NAP-Snort3","InlineResult":"Block","VLAN_ID":3101,"Device":"IT1IPS03BIN1-1_2","DeviceIP":"198.19.40.21","DeviceSerialNumber":"FLM2443067L","EgressInterfaceUUID":"47a1da92-a6ac-11eb-b4ad-fb6eab37a80a","EgressZoneUUID":"47b3a240-a6ac-11eb-b4ad-fb6eab37a80a","EventID":1127183,"FirewallPolicyUUID":"00000000-0000-0000-0000-0000681c487b","FirewallRuleID":268436578,"Hostname":"IE1FMCATS1-A1","ImpactFlag":7,"IngressInterfaceUUID":"4759ef8e-a6ac-11eb-b4ad-fb6eab37a80a","IngressZoneUUID":"47b3a240-a6ac-11eb-b4ad-fb6eab37a80a","InitiatorContinent":"Asia","InitiatorContinentCode":"as","InitiatorCountry":"Japan","InitiatorCountryCode":"jpn","InitiatorCountryID":392,"InlineResultID":4,"IntrusionPolicyRevUUID":"82e629b0-6543-11ef-b36c-117b0c432ecb","IntrusionPolicyUUID":"a488735a-d4ea-0ed3-0000-326418261953","NAP_PolicyUUID":"cf0ab078-437d-11ef-ab6b-97ee0b432ecb","ProtocolID":17,"RealmID":0,"RealmName":"Invalid ID","ResponderContinent":"Europe","ResponderContinentCode":"eu","ResponderCountry":"Germany","ResponderCountryCode":"deu","ResponderCountryID":276,"SensorID":2,"SnortVersionID":3,"UserID":9999997,"dt.security_context": "_sec_intel"} ]""" | filter matchesPhrase(dt.security_context,"_sec_intel") or matchesPhrase(dt.security_context,"_sourcefire_secevents") | fieldsAdd Status = if( (InlineResultID == "5" OR InlineResultID == "0" OR InlineResultID == "2") ,"Detected", else:if( ((InlineResultID == "1" OR InlineResultID == "4" OR accesscontrolruleaction == "Block" OR isnull(URLSICategory) OR isnull(DNSSICategory))), "Blocked", else:null )) | fieldsRename market=ResponderCountry | summarize by:{Status,market}, {count=count()} | summarize {market_details= collectArray(array(toString(market),count ))}, by:{Status} | fieldsAdd market_details=toString(market_details) | parse market_details, "'[' KVP{ '[' DQS:key ', ' INT:value ']' ', '?}:market_details ']'"