below is DQL which is giving wrong output with respect to SPL:-
fetch logs // scanLimitGBytes: , samplingRatio: 1000
| filter contains(dt.security_context, "ngss")
//| fieldsAdd a=matchesValue(index,"*ngss")
//| filter a=="true"
| fieldsAdd content2 = concat(content, "}")
| parse content2, "JSON:data"
| fieldsAdd InlineResultID = data[`InlineResultID`]
| parse content,"LD 'IPReputationSICategory=' LD:IPReputationSICategory ','"
| parse content,"LD 'AccessControlRuleAction=' LD:AccessControlRuleAction ','"
//| parse content,"LD 'AccessControlRuleAction: '[0-9a-zA-Z-]{1,100}?:AccessControlRuleAction','"
//|filter AccessControlRuleAction == "Block"
| fieldsAdd Status = if(InlineResultID == 5 OR InlineResultID == 0 OR InlineResultID == 2 ,"Detected", else:
if(InlineResultID ==1 OR InlineResultID == 4 OR AccessControlRuleAction == "Block"
OR IPReputationSICategory !=0 , "Blocked", else:"Null"))
| filter isNotNull(Status)
//| filterOut InlineResultID == 2
| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
//| fieldsAdd market = if(contains(market, "cita"), substring(market, from:4), else:market)
| fieldsAdd market = replaceString(market, "cita", "")
| fieldsAdd date = formatTimestamp(timestamp, format:"MM-dd-yyyy")
| summarize {ngssdublin = countIf(market == "ngssdublin"), ngssmilan = countIf(market == "ngssmilan"), ngssrat = countIf(market == "ngssrat")}, by:{Status,date}
| fieldsAdd total = ngssdublin + ngssmilan + ngssrat
//| filter isNotNull(Status)
//| fieldsadd timestamp('yyyy-MM-dd),timezone:'UTC'):datetime
bellow is raw logs for both index :-
*ngss*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
ngss*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}
PFB Splunk details :-
(index=*ngss*sec_intel OR index=ngss*sourcefire_secevents) IPReputationSICategory=* OR AccessControlRuleAction=Block OR (InlineResultID=1 OR InlineResultID=4)
| rex field=index "(?<Local_Market>[^cita]\w.*?)_"
| stats count(Local_Market) as Blocked by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"
| append [search (index=*ngss*sec_intel OR index=ngss*sourcefire_secevents) (InlineResultID=5 OR InlineResultID=0 OR InlineResultID=2)
| rex field=index "(?<Local_Market>\w.*?)_"
| stats count as Detected by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"]
| stats values(*) as * by Local_Market
| transpose 0 header_field=Local_Market column_name=Local_Market
| addinfo | eval date=info_min_time | eval date=strftime(date,"%d-%m-%Y") | fields - info_*
it looks like i am unable to add filter for IPReputationSICategory=* OR AccessControlRuleAction=Block under blocked status in "if else" syntax
Featured Posts
