How to get data from raw array of json from sendgrid webhook events

[amitrinx]

I am currently working with data from SendGrid Event API that is being ingested into Splunk. The data includes multiple email events (e.g., delivered, processed) wrapped into a single event, and this wrapping seems to happen randomly.Here is a sample of the data structure:

[
  {
    "email": "[email protected]",
    "event": "delivered",
    "ip": "XXX.XXX.XXX.XX",
    "response": "250 mail saved",
    "sg_event_id": "XXXX",
    "sg_message_id": "XXXX",
    "sg_template_id": "XXXX",
    "sg_template_name": "en",
    "smtp-id": "XXXX",
    "timestamp": "XXXX",
    "tls": 1,
    "twilio:verify": "XXXX"
  },
  {
    "email": "[email protected]",
    "event": "processed",
    "send_at": 0,
    "sg_event_id": "XXXX",
    "sg_message_id": "XXXX",
    "sg_template_id": "XXXX",
    "sg_template_name": "en",
    "smtp-id": "XXXX",
    "timestamp": "XXXX",
    "twilio:verify": "XXXX"
  }
]

I am looking for a query that can help me extract the email, event, and response (reason) fields from this data, even when multiple events are wrapped into a single event entry.Could anyone please provide guidance on the appropriate Splunk query to achieve this?

[livehybrid]

Hi @amitrinx 

You can use the following to split them into single events:

| eval events=json_array_to_mv(_raw)
| mvexpand events
| rename events as _raw

Full example with sample data:

| windbag 
| head 1 
| eval _raw="[ { \"email\": \"[email protected]\", \"event\": \"delivered\", \"ip\": \"XXX.XXX.XXX.XX\", \"response\": \"250 mail saved\", \"sg_event_id\": \"XXXX\", \"sg_message_id\": \"XXXX\", \"sg_template_id\": \"XXXX\", \"sg_template_name\": \"en\", \"smtp-id\": \"XXXX\", \"timestamp\": \"XXXX\", \"tls\": 1, \"twilio:verify\": \"XXXX\" }, { \"email\": \"[email protected]\", \"event\": \"processed\", \"send_at\": 0, \"sg_event_id\": \"XXXX\", \"sg_message_id\": \"XXXX\", \"sg_template_id\": \"XXXX\", \"sg_template_name\": \"en\", \"smtp-id\": \"XXXX\", \"timestamp\": \"XXXX\", \"twilio:verify\": \"XXXX\" } ]" 
| eval events=json_array_to_mv(_raw) 
| mvexpand events 
| rename events as _raw

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[VatsalJagani]

@livehybrid  json_array_to_mv - that's sounds interesting.  🙂

[kiran_panchavat]

@amitrinx 

Pls check this, I used makeresults command for dummydata. 

| makeresults 
| eval raw_json="[
{\"user\":\"[email protected]\",\"status\":\"sent\",\"ip_address\":\"192.168.1.10\",\"reply\":\"Message accepted\",\"event_id\":\"EVT001\",\"message_id\":\"MSG001\",\"template_id\":\"TPL001\",\"template_name\":\"welcome\",\"smtp_code\":\"250\",\"time\":\"2025-04-23T10:00:00Z\",\"encryption\":true,\"service\":\"email_service\"},
{\"user\":\"[email protected]\",\"status\":\"queued\",\"ip_address\":\"192.168.1.20\",\"reply\":\"Queued for delivery\",\"event_id\":\"EVT002\",\"message_id\":\"MSG002\",\"template_id\":\"TPL002\",\"template_name\":\"reset_password\",\"smtp_code\":\"451\",\"time\":\"2025-04-23T10:05:00Z\",\"encryption\":false,\"service\":\"notification_service\"},
{\"user\":\"[email protected]\",\"status\":\"failed\",\"ip_address\":\"192.168.1.30\",\"reply\":\"Mailbox not found\",\"event_id\":\"EVT003\",\"message_id\":\"MSG003\",\"template_id\":\"TPL003\",\"template_name\":\"alert\",\"smtp_code\":\"550\",\"time\":\"2025-04-23T10:10:00Z\",\"encryption\":true,\"service\":\"security_service\"},
{\"user\":\"[email protected]\",\"status\":\"opened\",\"ip_address\":\"192.168.1.40\",\"reply\":\"Email opened\",\"event_id\":\"EVT004\",\"message_id\":\"MSG004\",\"template_id\":\"TPL004\",\"template_name\":\"newsletter\",\"smtp_code\":\"200\",\"time\":\"2025-04-23T10:15:00Z\",\"encryption\":true,\"service\":\"marketing_service\"}
]"
| spath input=raw_json path={} output=event
| mvexpand event
| spath input=event
| table user status reply service