topic Re: How to extract fields from an extracted JSON ingested string in Getting Data In

How to extract fields from an extracted JSON ingested string

jonnim — Fri, 27 May 2016 02:49:26 GMT

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.

Re: How to extract fields from an extracted JSON ingested string

jkat54 — Fri, 27 May 2016 12:01:58 GMT

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

Re: How to extract fields from an extracted JSON ingested string

richgalloway — Fri, 27 May 2016 12:26:12 GMT

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

Re: How to extract fields from an extracted JSON ingested string

woodcock — Fri, 27 May 2016 23:11:28 GMT

You need to update props.conf from this:

 TRANSFORMS-extractJSON = extract-json

to this:

 TRANSFORMS-extractJSON = extract-json, extract_EventData

Re: How to extract fields from an extracted JSON ingested string

jonnim — Tue, 29 Sep 2020 09:48:30 GMT

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.