Getting Data In

How to extract fields from an extracted JSON ingested string

jonnim
Explorer

I have DNS log format as follows:

<14>May 25 23:59:19 COL02 Windows: {"Level":"4","Channel":"DNS Server","Version":"","Computer":"DC01.ntadmin.local","EventID":"55555","ExecutionThreadID":"","Keywords":"0x80000000000000","ProviderName":"DNS Server","Message":"25/05/2016 11:58:26 PM 0820 PACKET  0000000002F797A0 UDP Snd 172.30.235.30   697d R Q [8385 A DR NXDOMAIN] A      (4)wpad(7)ntadmin(5)local(0)","Opcode":"","TimeCreated":"2016-05-25T13:58:50.000000000Z","EventData":"25/05/2016 11:58:26 PM 0820 PACKET  0000000002F797A0 UDP Snd 172.30.235.30   697d R Q [8385 A DR NXDOMAIN] A      (4)wpad(7)ntadmin(5)local(0)","ExecutionProcessID":"","Task":"0","SecurityUserID":"","EventRecordID":"86253"}

I use the following in props.conf and transforms.conf:

props.conf

[windows]
KV_MODE = JSON
TRANSFORMS-extractJSON = extract-json
TRANSFORMS-win_sourcetype = windows_dns

transforms.conf

[extract-json]
SOURCE_KEY = _raw
DEST_KEY = _raw
REGEX = ^([^{]+)({.+})$
FORMAT = $2

[windows_dns]
DEST_KEY = MetaData:Sourcetype
REGEX = 55555
FORMAT = sourcetype::windows_dns

to extract the JSON string to get the following:

5/26/16
1:25:40.000 PM  
{ [-] 
    Channel:  DNS Server 
    Computer:  DC01.ntadmin.local 
    EventData:  25/05/2016 11:58:26 PM 0820 PACKET 0000000002F797A0 UDP Snd 172.30.235.30 697d R Q [8385 A DR NXDOMAIN] A (4)wpad(7)ntadmin(5)local(0)
    EventID:  55555 
    EventRecordID:  86253 
    ExecutionProcessID: 
    ExecutionThreadID: 
    Keywords:  0x80000000000000 
    Level:  4 
    Message:  25/05/2016 11:58:26 PM 0820 PACKET 0000000002F797A0 UDP Snd 172.30.235.30 697d R Q [8385 A DR NXDOMAIN] A (4)wpad(7)ntadmin(5)local(0)
    Opcode: 
    ProviderName:  DNS Server 
    SecurityUserID: 
    Task:  0 
    TimeCreated:  2016-05-26T03:21:09.000000000Z 
    Version: 
}

which extracts the relevant fields:

Channel
Computer
EventData 
EventID
EventRecordID
ExecutionProcessID
ExecutionThreadID
Keywords
Level
Message
Opcode
ProviderName
SecurityUserID
Task
TimeCreated
Version

I now want to further extract fields from the EventData field using the following transform:

transforms.conf

[extract_EventData]
CLEAN_KEYS = 0
REGEX = ^(?\d\d\/\d\d\/\d\d\d\d)\s(?\d+:\d\d:\d\d\s\w\w)\s(?\d+)\s(?\w+)\s+(?\S+)\s(?\w+)\s(?\w+)\s(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\s+(?\S+)\s(?\w*)\s(?\w*)\s\[(?.\S+)\s(?[\w\s]{1,4})\s(?\S+)]\s(?\S+)\s+\(\d+\)(?\S+)$
SOURCE_KEY = EventData

It doesn't work. I have tested the REGEX using SPL :

sourcetype=windows_dns  | rex field=EventData "^(?\d\d\/\d\d\/\d\d\d\d)\s(?\d+:\d\d:\d\d\s\w\w)\s(?\d+)\s(?\w+)\s+(?\S+)\s(?\w+)\s(?\w+)\s(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\s+(?\S+)\s(?\w*)\s(?\w*)\s\[(?.\S+)\s(?[\w\s]{1,4})\s(?\S+)]\s(?\S+)\s+\(\d+\)(?\S+)"

That extracts the relevant EventData fields. I cannot get this to work automatically.

0 Karma

woodcock
Esteemed Legend

You need to update props.conf from this:

 TRANSFORMS-extractJSON = extract-json

to this:

 TRANSFORMS-extractJSON = extract-json, extract_EventData
0 Karma

jonnim
Explorer

Just a typo left out the filed names as I was trying something else. Here is the updated SPL.

rex field=EventData "^(?\d\d\/\d\d\/\d\d\d\d)\s(?\d+:\d\d:\d\d\s\w\w)\s(?\d+)\s(?\w+)\s+(?\S+)\s(?\w+)\s(?\w+)\s(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\s+(?\S+)\s(?\w*)\s(?\w*)\s[(?.\S+)\s(?[\w\s]{1,4})\s(?\S+)]\s(?\S+)\s+(\d+)(?\S+)$"

Woodcock- the

TRANSFORMS-extractJSON = extract-json, extract_EventData

Doesn't work - It does not seem to find the EventData filed to extract .. As mentioned before it works during a search but does nit auto extract.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Perhaps it's just a typo, but the REGEX string doesn't match the regex used in the SPL query.

---
If this reply helps you, Karma would be appreciated.
0 Karma

jkat54
SplunkTrust
SplunkTrust

I know its a bit difficult, but I recommend ingesting json data as json and then following steps outlined in the spath documentation:

http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Spath

See this answer where I recently learned how to do this on json events for a real world example:

https://answers.splunk.com/answers/401942/how-to-parse-my-json-data-with-spath-and-table-the.html

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...