null output in DQL parsing

[sharmas2]

Hi,

"I have the following Splunk query, and I'm trying to generate the corresponding output in Dynatrace."

splunk query :-

index=ngss*_sourcefire_secevents | rex field=index "(?<Local_Market>\w.*?)_"
| eval BlockedStatus =
case(Like(src_ip,"64.39.106.%") AND InlineResultID=4 ," Qualys Blocked",
Like(src_ip,"154.59.121.%") AND InlineResultID=4," Qualys Blocked",
Like(src_ip,"64.39.106.%") AND InlineResultID=0," Qualys Not Blocked",
Like(src_ip,"154.59.121.%") AND InlineResultID=0," Qualys Not Blocked",
NOT Like(src_ip,"64.39.106.%") AND InlineResultID=4,"Non Qualys Blocked",
NOT Like(src_ip,"154.59.121.%") AND InlineResultID=4,"Non Qualys Blocked",
NOT Like(src_ip,"64.39.106.%") AND InlineResultID=0,"Non Qualys Not Blocked",
NOT Like(src_ip,"154.59.121.%") AND InlineResultID=0,"Non Qualys Not Blocked")
| stats count by Local_Market BlockedStatus | rename eventtype as "Local Market",count as "Total Critical Events"

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

corresponding DQL is as below , where i am getting Null value ..
Please not that in DQL Src_ip is consider as "InitiatorIP".

 DQL query is as below :-

fetch logs // scanLimitGBytes: , samplingRatio: 1000

| filter contains(dt.security_context,"ngss")
| parse content,"""LD 'InlineResultID":' string:InlineResultID "," """
//| parse content, """LD 'InitiatorIP'[^,]{1,100}?:"InitiatorIP','""""
| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
| parse content, """ LD 'InitiatorIP\":\"' IPADDR:InitiatorIP """
| parse content, """ LD 'InitiatorIP=' IPADDR:InitiatorIP """
| fieldsadd QualysBlocked=if((like(InitiatorIP,"154.59.121%") or like(InitiatorIP,"64.39.106.%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),QualysBlocked)
| fieldsadd QualysNotBlocked=if((like(InitiatorIP,"64.39.106%") OR like(InitiatorIP,"154.59.121.%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),QualysNotBlocked)
| fieldsadd NonQualysBlocked=if((like(InitiatorIP,"64.39.106%") or like(InitiatorIP,"154.59.121%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),NonQualysBlocked)
| fieldsadd NonQualysNotBlocked=if((like(InitiatorIP,"64.39.106%") or like(InitiatorIP,"154.59.121.%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),NonQualysNotBlocked)
| fieldsADD BlockedStatus = coalesce(QualysBlocked,QualysnotBlocked,NonQualysBlocked,NonQualysNotBlocked)
//| fieldsADD Blockedstatus = coalesce(QualysBlocked,QualysnonBlocked)
| summarize count() ,by:{market,BlockedStatus}

Raw data is as below :-

{"EventType":"IntrusionEvent","EventSecond":1748251156,"EventMicrosecond":409890,"DeviceUUID":"4888b6cc-5037-11eb-a0cd-bef67487e4a0","InstanceID":21,"FirstPacketSecond":1748246651,"ConnectionID":45963,"InitiatorIP":"198.18.76.185","ResponderIP":"47.73.55.140","InitiatorPort":54904,"ResponderPort":16154,"Protocol":"tcp","IngressInterface":"DPB1-IN","EgressInterface":"DPB1-OUT","IngressZone":"Citadel-Dublin-DPB1","EgressZone":"Citadel-Dublin-DPB1","PriorityID":2,"GeneratorID":3,"SignatureID":56275,"SignatureRevision":1,"Impact":3,"IntrusionRuleMessage":"SERVER-OTHER TRUFFLEHUNTER TALOS-2020-1185 attack attempt","Classification":"Inappropriate content was detected","ClientApplication":"Splunk","Application":"TPNCP","IntrusionPolicy":"Dublin-Non-Blocking-Policy_S3","FirewallPolicy":"NGSS2-Dublin","FirewallRule":"To Management Systems Zone","NAP_Policy":"NGSS2-Dublin-NAP-Snort3","InlineResult":"Would block","InlineResultReason":"Intrusion Policy in \"Detection\" Inspection

[marco_irmer]

Hi there. I am noticing that you are using two different field names in your fieldsAdd statements. Additionally, your 'if' statement is not assigning string values. I recommend you use the same field name across the board and just assign a different value using the if statements. It would look something like this:

fetch logs // scanLimitGBytes: , samplingRatio: 1000
| filter contains(dt.security_context,"ngss")
| parse content,"""LD 'InlineResultID":' string:InlineResultID "," """
//| parse content, """LD 'InitiatorIP'[^,]{1,100}?:"InitiatorIP','""""
| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
| parse content, """ LD 'InitiatorIP\":\"' IPADDR:InitiatorIP """
| parse content, """ LD 'InitiatorIP=' IPADDR:InitiatorIP """
| fieldsadd BlockedStatus=if((like(InitiatorIP,"154.59.121%") or like(InitiatorIP,"64.39.106.%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),"QualysBlocked")
| fieldsadd BlockedStatus=if((like(InitiatorIP,"64.39.106%") OR like(InitiatorIP,"154.59.121.%") AND contains(InlineResultID,"0") or contains(InlineResultID,"4")),"QualysNotBlocked")
// remainder of revised fieldsAdd statements go here

I was not able to test the whole query for you, but I hope this points you in the right direction.

[sharmas2]

@marco_irmer  i am getting error as "BlockedStatus overrides the existing field.."

[marco_irmer]

I believe that's a warning only and should not affect the outcome of the query..

[sharmas2]

@marco_irmer  still null value coming..

tried below DQL as well ..

===================
fetch logs // scanLimitGBytes: , samplingRatio: 1000
| filter dt.system.bucket == "custom_group_security"

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

| filter contains(content, "SrcIP", caseSensitive: false)
| parse content, """ LD 'InitiatorIP\":\"' IPADDR:InitiatorIP """
| parse content, """ LD 'SrcIP=' IPADDR:SrcIP """
| parse content,"""LD 'InlineResultID":' string:InlineResultID "," """
| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
| fieldsAdd market = replaceString(market, "cita", "")

| fieldsAdd IP_of_interest = coalesce(SrcIP, InitiatorIP, "Not Found")
| fieldsAdd IP_of_interest = toString(IP_of_interest)

| fieldsadd QualysBlocked=if((like(IP_of_interest, "154.59.121%") or like(IP_of_interest, "64.39.106.%")) and contains(InlineResultID,"4") ,"QualysBlocked",
| fieldsadd QualysNotBlocked=if(((like(IP_of_interest, "64.39.106.%") or (like(IP_of_interest, "154.59.121.%")) AND contains(InlineResultID,"0"),"QualysNotBlocked")
| fieldsadd NonQualysBlocked=if(((NOT like(IP_of_interest,"64.39.106%") AND contains(InlineResultID,"4")) or (Not like(IP_of_interest, "154.59.121.%")AND contains(InlineResultID,"4"))),"NonQualysBlocked")
| fieldsadd NonQualysNotBlocked=if(((NOT like(IP_of_interest,"64.39.106%") AND contains(InlineResultID,"0")) or (Not like(IP_of_interest, "154.59.121.%")AND contains(InlineResultID,"0"))),"NonQualysNotBlocked")
| fieldsADD BlockedStatus = coalesce(QualysBlocked,QualysNotBlocked,NonQualysBlocked,NonQualysNotBlocked, "Sorry Ip not found")

| summarize count() ,by:{market,BlockedStatus}