JSON field extraction

[maheshnc]

We are ingesting a nested JSON payload in Splunk and want to extract specific fields (like AlertDIsplayName, Description, SenderIP etc) how can I do this as Splunk's Field Extractor is not working in this case.

[PrewinThomas]

Its bit tricky, since you are having nested json, but you can try something below,

|spath | spath input=Data path=Description output=Description
| spath input=Data path="Entities{1}.SenderIP" output=SenderIP
| spath input=Data path="Entities{1}.Recipient" output=Recipient
| spath input=Data path="Entities{2}.Url" output=Url
| table Description SenderIP Recipient Url

Regards,
Prewin
If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[maheshnc]

I need to have index time field extraction for all the field values under Data field, is it feasible?

[ITWhisperer]

Given the likely performance degradations, why do you want (need) index time extractions?

[livehybrid]

Hi @maheshnc 

Its tricky without the full JSON for us to test with but you could try the following in your search?

| eval _raw=json_extract(_raw,"Data") 
| spath

🌟 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

[isoutamo]

1st are you sure that his is valid json?
I have seen e.g. some weird UTF escaped message from MS databricks etc.
Can you check what you have when you are opening event from this > mark and then select "Show Source" from "Event Actions" button.

[maheshnc]

{"CreationTime": "2025-10-02T04:10:15", "Id": "124147e3-6c47-46ca-8f77-6fd0b9aa9e99", "Operation": "AirInvestigationData", "OrganizationId": "5d1aa650-d7e1-4ec2-a6a9-a05372d7b650", "RecordType": 64, "UserKey": "AirInvestigation", "UserType": 4, "Version": 1, "Workload": "AirInvestigation", "ObjectId": "124147e3-6c47-46ca-8f77-6fd0b9aa9e99", "UserId": "AirInvestigation", "Data": "{\"Version\":\"3.0\",\"VendorName\":\"Microsoft\",\"ProviderName\":\"OATP\",\"AlertType\":\"8e6ba277-ef39-404e-aaf1-294f6d9a2b88\",\"StartTimeUtc\":\"2025-10-02T04:08:23Z\",\"EndTimeUtc\":\"2025-10-02T04:08:23Z\",\"TimeGenerated\":\"2025-10-02T04:06:28.27Z\",\"ProcessingEndTime\":\"2025-10-02T04:10:14.0666959Z\",\"Status\":\"InProgress\",\"DetectionTechnology\":\"URLList\",\"Severity\":\"Informational\",\"ConfidenceLevel\":\"Unknown\",\"ConfidenceScore\":1.0,\"IsIncident\":false,\"ProviderAlertId\":\"fcc7e7f8-3630-61d7-be00-08de0168db42\",\"SystemAlertId\":null,\"CorrelationKey\":\"034549ac-35ef-481e-928d-da3d07eed36f\",\"Investigations\":[{\"$id\":\"1\",\"Id\":\"urn:ZappedUrlInvestigation:b7485c2295ebf76b97b2cee80d063211\",\"InvestigationStatus\":\"Running\"}],\"InvestigationIds\":[\"urn:ZappedUrlInvestigation:b7485c2295ebf76b97b2cee80d063211\"],\"Intent\":\"Probing\",\"ResourceIdentifiers\":[{\"$id\":\"2\",\"AadTenantId\":\"5d1aa650-d7e1-4ec2-a6a9-a05372d7b650\",\"Type\":\"AAD\"}],\"AzureResourceId\":null,\"WorkspaceId\":null,\"WorkspaceSubscriptionId\":null,\"WorkspaceResourceGroup\":null,\"AgentId\":null,\"AlertDisplayName\":\"Email messages containing malicious URL removed after delivery\u200b\",\"Description\":\"Emails with malicious URL that were delivered and later removed -V1.0.0.3\",\"ExtendedLinks\":[{\"Href\":\"https://security.microsoft.com/alerts/fafcc7e7f8-3630-61d7-be00-08de0168db42\",\"Category\":null,\"Label\":\"alert\",\"Type\":\"webLink\"}],\"Metadata\":{\"CustomApps\":null,\"GenericInfo\":null},\"Entities\":[{\"$id\":\"3\",\"MailboxPrimaryAddress\":\"aahmed@alittihad.ae\",\"Upn\":\"aahmed@alittihad.ae\",\"AadId\":\"9cd9a955-3f6e-42c9-9e5b-73da88078866\",\"RiskLevel\":\"None\",\"Type\":\"mailbox\",\"Urn\":\"urn:UserEntity:a8c90e3cbe8d52a9d1414f4c11865be6\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"4\",\"Recipient\":\"aahmed@alittihad.ae\",\"Urls\":[\"https://i.comfortcarevetlangley.com/quantum.php\",\"https://click.e.usa.experian.com/open.aspx?ffcb10-fe9211767260007c77-fe22127577600375751d74-fe9613737763057e77-ff001574776701-fdff15737c60077d74167272-fefb1774706503&d=70242&bmt=0\",\"https://image.e.usa.experian.com/lib/fe9613737763057e77/m/1/85d3688a-7218-45ea-a1b7-9600e974a0db.png\"],\"Sender\":\"noreply@act.ac\",\"P1Sender\":\"010f0199a2db62bb-836f5593-9d4e-494d-b0c2-90fc9a020d40-000000@us-east-2.amazonses.com\",\"P1SenderDomain\":\"us-east-2.amazonses.com\",\"SenderIP\":\"23.251.226.55\",\"P2Sender\":\"noreply@act.ac\",\"P2SenderDomain\":\"act.ac\",\"ReceivedDate\":\"2025-10-02T02:58:38Z\",\"NetworkMessageId\":\"39621bc0-bd8d-4bae-0da9-08de015f92df\",\"InternetMessageId\":\"<010f0199a2db62bb-836f5593-9d4e-494d-b0c2-90fc9a020d40-000000@us-east-2.amazonses.com>\",\"Subject\":\"8852 Admedia365 EReview Doc October 01, 2025 07:58 PM\",\"AntispamDirection\":\"Inbound\",\"DeliveryAction\":\"Delivered\",\"Language\":\"en\",\"DeliveryLocation\":\"Inbox\",\"OriginalDeliveryLocation\":\"Inbox\",\"AdditionalActionsAndResults\":[\"OriginalDelivery: [N/A]\"],\"AuthDetails\":[{\"Name\":\"SPF\",\"Value\":\"Pass\"},{\"Name\":\"DKIM\",\"Value\":\"Pass\"},{\"Name\":\"DMARC\",\"Value\":\"Pass\"}],\"SystemOverrides\":[],\"Type\":\"mailMessage\",\"Urn\":\"urn:MailEntity:73fd77bf162599990938f1595fed86d4\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"5\",\"Url\":\"https://i.comfortcarevetlangley.com/quantum.php\",\"Type\":\"url\",\"ClickCount\":11,\"EmailCount\":138,\"Urn\":\"urn:UrlEntity:9310164f200b0089953572b3a2e835e7\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"}],\"LogCreationTime\":\"2025-10-02T04:10:14.0666959Z\",\"MachineName\":\"AU2ARE01BG404\",\"SourceTemplateType\":\"Threat_Single\",\"Category\":\"ThreatManagement\",\"SourceAlertType\":\"System\"}", "DeepLinkUrl": "https://security.microsoft.com/mtp-investigation/urn:ZappedUrlInvestigation:b7485c2295ebf76b97b2cee80d063211", "EndTimeUtc": "0001-01-01T00:00:00", "InvestigationId": "urn:ZappedUrlInvestigation:b7485c2295ebf76b97b2cee80d063211", "InvestigationName": "Mail with malicious urls is zapped - urn:ZappedUrlInvestigation:b7485c2295ebf76b97b2cee80d063211", "InvestigationType": "ZappedUrlInvestigation", "LastUpdateTimeUtc": "2025-10-02T04:07:03", "StartTimeUtc": "2025-10-02T04:10:14", "Status": "Investigation Started"}

{"CreationTime": "2025-10-02T04:10:15", "Id": "a55abd1d-c02a-44d1-b990-bcee7aae4ca2", "Operation": "AirInvestigationData", "OrganizationId": "5d1aa650-d7e1-4ec2-a6a9-a05372d7b650", "RecordType": 64, "UserKey": "AirInvestigation", "UserType": 4, "Version": 1, "Workload": "AirInvestigation", "ObjectId": "a55abd1d-c02a-44d1-b990-bcee7aae4ca2", "UserId": "AirInvestigation", "Data": "{\"Version\":\"3.0\",\"VendorName\":\"Microsoft\",\"ProviderName\":\"OATP\",\"AlertType\":\"a74bb32a-541b-47fb-adfd-f8c62ce3d59b\",\"StartTimeUtc\":\"2025-10-02T04:07:04Z\",\"EndTimeUtc\":\"2025-10-02T04:07:04Z\",\"TimeGenerated\":\"2025-10-02T04:06:49.8033333Z\",\"ProcessingEndTime\":\"2025-10-02T04:10:12.5202208Z\",\"Status\":\"InProgress\",\"Severity\":\"High\",\"ConfidenceLevel\":\"Unknown\",\"ConfidenceScore\":1.0,\"IsIncident\":false,\"ProviderAlertId\":\"0bc5bc5d-1c4b-67d2-be00-08de0168db42\",\"SystemAlertId\":null,\"CorrelationKey\":\"6e7623bc-7a41-4f6e-91a3-c2367804f4a1\",\"Investigations\":[{\"$id\":\"1\",\"Id\":\"urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4\",\"InvestigationStatus\":\"Running\"}],\"InvestigationIds\":[\"urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4\"],\"Intent\":\"Probing\",\"ResourceIdentifiers\":[{\"$id\":\"2\",\"AadTenantId\":\"5d1aa650-d7e1-4ec2-a6a9-a05372d7b650\",\"Type\":\"AAD\"}],\"AzureResourceId\":null,\"WorkspaceId\":null,\"WorkspaceSubscriptionId\":null,\"WorkspaceResourceGroup\":null,\"AgentId\":null,\"AlertDisplayName\":\"A potentially malicious URL click was detected\",\"Description\":\"We have detected that one of your users has recently clicked on a link that was found to be malicious. -V1.0.0.5\",\"ExtendedLinks\":[{\"Href\":\"https://security.microsoft.com/alerts/fa0bc5bc5d-1c4b-67d2-be00-08de0168db42\",\"Category\":null,\"Label\":\"alert\",\"Type\":\"webLink\"}],\"Metadata\":{\"CustomApps\":null,\"GenericInfo\":null},\"Entities\":[{\"$id\":\"3\",\"MailboxPrimaryAddress\":\"scott.williams@admn.ae\",\"Upn\":\"Scott.Williams@admn.ae\",\"AadId\":\"eec44b61-469e-46d6-a72b-c1fcc375c01d\",\"RiskLevel\":\"Low\",\"Type\":\"mailbox\",\"Urn\":\"urn:UserEntity:b9f719512efa348dd7b60bd026c92e29\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"4\",\"Url\":\"https://i.comfortcarevetlangley.com/quantum.php\",\"Type\":\"url\",\"Urn\":\"urn:UrlEntity:0b1c1bfdf1d7ed76331e9f02ee505be4\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"5\",\"Recipient\":\"scott.williams@admn.ae\",\"Urls\":[\"https://i.comfortcarevetlangley.com/quantum.php\",\"https://click.e.usa.experian.com/open.aspx?ffcb10-fe9211767260007c77-fe22127577600375751d74-fe9613737763057e77-ff001574776701-fdff15737c60077d74167272-fefb1774706503&d=70242&bmt=0\",\"https://image.e.usa.experian.com/lib/fe9613737763057e77/m/1/85d3688a-7218-45ea-a1b7-9600e974a0db.png\"],\"Sender\":\"noreply@act.ac\",\"P1Sender\":\"010f0199a2dc906b-4d795411-add7-45a0-a955-5e0c53bc97a2-000000@us-east-2.amazonses.com\",\"P1SenderDomain\":\"us-east-2.amazonses.com\",\"SenderIP\":\"23.251.226.53\",\"P2Sender\":\"noreply@act.ac\",\"P2SenderDomain\":\"act.ac\",\"ReceivedDate\":\"2025-10-02T02:59:54Z\",\"NetworkMessageId\":\"23db9343-0607-4d81-c214-08de015fc0d4\",\"InternetMessageId\":\"<010f0199a2dc906b-4d795411-add7-45a0-a955-5e0c53bc97a2-000000@us-east-2.amazonses.com>\",\"Subject\":\"6882 Admedia365 EReview Doc October 01, 2025 07:59 PM\",\"AntispamDirection\":\"Inbound\",\"DeliveryAction\":\"Delivered\",\"Language\":\"en\",\"DeliveryLocation\":\"Inbox\",\"OriginalDeliveryLocation\":\"Inbox\",\"AdditionalActionsAndResults\":[\"OriginalDelivery: [N/A]\"],\"AuthDetails\":[{\"Name\":\"SPF\",\"Value\":\"Pass\"},{\"Name\":\"DKIM\",\"Value\":\"Pass\"},{\"Name\":\"DMARC\",\"Value\":\"Pass\"}],\"SystemOverrides\":[],\"Type\":\"mailMessage\",\"Urn\":\"urn:MailEntity:2e89684d4c0ba4dd116578d7ccca7cd5\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"}],\"LogCreationTime\":\"2025-10-02T04:10:12.5202208Z\",\"MachineName\":\"AU2ARE01BG404\",\"SourceTemplateType\":\"MaliciousUrlClick_Single\",\"Category\":\"ThreatManagement\",\"SourceAlertType\":\"System\"}", "DeepLinkUrl": "https://security.microsoft.com/mtp-investigation/urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4", "EndTimeUtc": "0001-01-01T00:00:00", "InvestigationId": "urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4", "InvestigationName": "Clicked url Verdict changed to malicious - https://i.comfortcarevetlangley.com/quantum.php", "InvestigationType": "UrlVerdictChangeInvestigation", "LastUpdateTimeUtc": "2025-10-02T04:07:04", "StartTimeUtc": "2025-10-02T04:10:12", "Status": "Investigation Started"}

Basically it is a nested JSON and I want to extract fields from "Data" field which itself forms a JSON object

[ITWhisperer]

Please share your event in raw format using the code block formatting button </>

[maheshnc]

{"CreationTime": "2025-10-02T04:10:15", "Id": "a55abd1d-c02a-44d1-b990-bcee7aae4ca2", "Operation": "AirInvestigationData", "OrganizationId": "5d1aa650-d7e1-4ec2-a6a9-a05372d7b650", "RecordType": 64, "UserKey": "AirInvestigation", "UserType": 4, "Version": 1, "Workload": "AirInvestigation", "ObjectId": "a55abd1d-c02a-44d1-b990-bcee7aae4ca2", "UserId": "AirInvestigation", "Data": "{\"Version\":\"3.0\",\"VendorName\":\"Microsoft\",\"ProviderName\":\"OATP\",\"AlertType\":\"a74bb32a-541b-47fb-adfd-f8c62ce3d59b\",\"StartTimeUtc\":\"2025-10-02T04:07:04Z\",\"EndTimeUtc\":\"2025-10-02T04:07:04Z\",\"TimeGenerated\":\"2025-10-02T04:06:49.8033333Z\",\"ProcessingEndTime\":\"2025-10-02T04:10:12.5202208Z\",\"Status\":\"InProgress\",\"Severity\":\"High\",\"ConfidenceLevel\":\"Unknown\",\"ConfidenceScore\":1.0,\"IsIncident\":false,\"ProviderAlertId\":\"0bc5bc5d-1c4b-67d2-be00-08de0168db42\",\"SystemAlertId\":null,\"CorrelationKey\":\"6e7623bc-7a41-4f6e-91a3-c2367804f4a1\",\"Investigations\":[{\"$id\":\"1\",\"Id\":\"urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4\",\"InvestigationStatus\":\"Running\"}],\"InvestigationIds\":[\"urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4\"],\"Intent\":\"Probing\",\"ResourceIdentifiers\":[{\"$id\":\"2\",\"AadTenantId\":\"5d1aa650-d7e1-4ec2-a6a9-a05372d7b650\",\"Type\":\"AAD\"}],\"AzureResourceId\":null,\"WorkspaceId\":null,\"WorkspaceSubscriptionId\":null,\"WorkspaceResourceGroup\":null,\"AgentId\":null,\"AlertDisplayName\":\"A potentially malicious URL click was detected\",\"Description\":\"We have detected that one of your users has recently clicked on a link that was found to be malicious. -V1.0.0.5\",\"ExtendedLinks\":[{\"Href\":\"https://security.microsoft.com/alerts/fa0bc5bc5d-1c4b-67d2-be00-08de0168db42\",\"Category\":null,\"Label\":\"alert\",\"Type\":\"webLink\"}],\"Metadata\":{\"CustomApps\":null,\"GenericInfo\":null},\"Entities\":[{\"$id\":\"3\",\"MailboxPrimaryAddress\":\"scott.williams@admn.ae\",\"Upn\":\"Scott.Williams@admn.ae\",\"AadId\":\"eec44b61-469e-46d6-a72b-c1fcc375c01d\",\"RiskLevel\":\"Low\",\"Type\":\"mailbox\",\"Urn\":\"urn:UserEntity:b9f719512efa348dd7b60bd026c92e29\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"4\",\"Url\":\"https://i.comfortcarevetlangley.com/quantum.php\",\"Type\":\"url\",\"Urn\":\"urn:UrlEntity:0b1c1bfdf1d7ed76331e9f02ee505be4\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"},{\"$id\":\"5\",\"Recipient\":\"scott.williams@admn.ae\",\"Urls\":[\"https://i.comfortcarevetlangley.com/quantum.php\",\"https://click.e.usa.experian.com/open.aspx?ffcb10-fe9211767260007c77-fe22127577600375751d74-fe9613737763057e77-ff001574776701-fdff15737c60077d74167272-fefb1774706503&d=70242&bmt=0\",\"https://image.e.usa.experian.com/lib/fe9613737763057e77/m/1/85d3688a-7218-45ea-a1b7-9600e974a0db.png\"],\"Sender\":\"noreply@act.ac\",\"P1Sender\":\"010f0199a2dc906b-4d795411-add7-45a0-a955-5e0c53bc97a2-000000@us-east-2.amazonses.com\",\"P1SenderDomain\":\"us-east-2.amazonses.com\",\"SenderIP\":\"23.251.226.53\",\"P2Sender\":\"noreply@act.ac\",\"P2SenderDomain\":\"act.ac\",\"ReceivedDate\":\"2025-10-02T02:59:54Z\",\"NetworkMessageId\":\"23db9343-0607-4d81-c214-08de015fc0d4\",\"InternetMessageId\":\"<010f0199a2dc906b-4d795411-add7-45a0-a955-5e0c53bc97a2-000000@us-east-2.amazonses.com>\",\"Subject\":\"6882 Admedia365 EReview Doc October 01, 2025 07:59 PM\",\"AntispamDirection\":\"Inbound\",\"DeliveryAction\":\"Delivered\",\"Language\":\"en\",\"DeliveryLocation\":\"Inbox\",\"OriginalDeliveryLocation\":\"Inbox\",\"AdditionalActionsAndResults\":[\"OriginalDelivery: [N/A]\"],\"AuthDetails\":[{\"Name\":\"SPF\",\"Value\":\"Pass\"},{\"Name\":\"DKIM\",\"Value\":\"Pass\"},{\"Name\":\"DMARC\",\"Value\":\"Pass\"}],\"SystemOverrides\":[],\"Type\":\"mailMessage\",\"Urn\":\"urn:MailEntity:2e89684d4c0ba4dd116578d7ccca7cd5\",\"Source\":\"OATP\",\"FirstSeen\":\"0001-01-01T00:00:00\"}],\"LogCreationTime\":\"2025-10-02T04:10:12.5202208Z\",\"MachineName\":\"AU2ARE01BG404\",\"SourceTemplateType\":\"MaliciousUrlClick_Single\",\"Category\":\"ThreatManagement\",\"SourceAlertType\":\"System\"}", "DeepLinkUrl": "https://security.microsoft.com/mtp-investigation/urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4", "EndTimeUtc": "0001-01-01T00:00:00", "InvestigationId": "urn:UrlVerdictChangeInvestig:a050537f9fe60b72448a98022810d2f4", "InvestigationName": "Clicked url Verdict changed to malicious - https://i.comfortcarevetlangley.com/quantum.php", "InvestigationType": "UrlVerdictChangeInvestigation", "LastUpdateTimeUtc": "2025-10-02T04:07:04", "StartTimeUtc": "2025-10-02T04:10:12", "Status": "Investigation Started"}

So basically it is a nested JSON and I need to extract fields from "Data" field. 

[ITWhisperer]

| spath Data
| spath input=Data