Need to count/extract nested json arrays.
Below is an example of a json log we get. As you can see this single webhook log will detail multiple events about messages, and each element in the log array is a nested array detailing an individual event that happened to a particular message.
I'm searching for "type:blocked" and I get the relevant logs. Now I need to get the particular event and the json fields for that particular nested array. In the attached example it is array item [0] but it could potentially be any of them from [0] to [n].
How can I get only the nested arrays that contain "type:blocked" so that I can use them in a dashboard showing blocked messages?
[
{
email:"recip0@recipdomain.abc",
envid:"********",
event:"bounce",
ip:"x.x.x.x",
jobid:"********",
origsender:"sender@senddomain.xyz",
reason:"553 5.3.0 alph775 DNSBL:RBL 521< x.x.x.x >_is_blocked.For assistance forward this error to abuse@recipadmin.rbl",
sg_event_id:"********",
sg_message_id:"0",
smtp-id:"<AEf********>",
status:"5.3.0",
timestamp:1644584631,
tls:0,
type:"blocked"
},{
email:"recip1@recipdomain.abc",
envid:"********c39",
event:"processed",
jobid:"********c5a",
origsender:"sender@senddomain.xyz",
send_at:0,
sg_event_id:"********tMA",
sg_message_id:"********9A.0",
smtp-id:"<wVn********>",
timestamp:1644584632
},{
email:"recip1@recipdomain.abc",
envid:"********c39",
event:"delivered",
ip:"x.x.x.x",
jobid:"********c5a",
origsender:"sender@senddomain.xyz",
response:"250 2.0.0 OK ********.357 - gsmtp",
sg_event_id:"********S0w",
sg_message_id:"********23.0",
smtp-id:"<0_X********>",
timestamp:1644584633,
tls:1
}
]
-
Official comment
Hello Mike
Give this query a try:
_sourceCategory=your.source.category
| parse regex field=_raw "(?<message2>\{.+)(?:\,|])" multi
| json field=message2 "type"
| where type="blocked"I put the keyword "blocked" in the scope line of the query to only return messages that include this keyword. Then we use a parse regex with the multi option to split all the nested arrays into their own individual message for ease of handling. Then we use JSON parsing to parse out the field type and only the messages that include it and finally a where statement to return only the message where the field "type" equals "blocked".
You can now continue working with the fields found in the message2 column.Comment actions
Please sign in to leave a comment.
Comments
1 comment