How to edit my props and transforms to extract the correct time from my sample DNS log from a Syslog encapsulated message?

[jonnim]

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-T1_extractJSON = extract-json 
TRANSFORMS-T2_win_sourcetype = win_dns

[windows_dns]
KV_MODE = JSON
REPORT-EventData = extract_EventData
TIME_PREFIX= /"Message":"/
TIME_FORMAT = %d/%m/%y %H:%M:%S
SHOULD_LINEMERGE = false
FIELDALIAS-Win:DNS-aliases = Computer AS dest_ip EventID AS eventid host AS dvc opcode AS vendor_query_type packetid AS transaction_id

transforms.conf

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

CLEAN_KEYS = 0

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

CLEAN_KEYS = 0

[extract_EventData]
CLEAN_KEYS = 0

SOURCE_KEY = EventData
MV_ADD = 0
FORMAT = 

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: 
 }

As seen from above the the ingested time is different from the syslog message time. I have tried to use TIME_PREFIX etc, but doesn't work as I think the syslog message is being parsed already. The Syslog time is being extracted into the Message_Time as follows:

Message_Time = 3/06/2016 6:01:55 PM

How can I get this to be the source of truth time?

[jkuepker_splunk · Accepted solution]

I believe I see three issues here.

1. Is the TIME_PREFIX is correct? I do not get a match in the UI when using yours. Try changing /"Message":"/ to "Message":"
2. Your TIME_FORMAT hour setting appears incorrect. You are using the 24-hour format and not 12-hour as indicated by the trailing 'PM'.
3. Your TIME_FORMAT year setting appears incorrect. %y is the 2-digit year, so the %Y variable is needed.

@sundareshr The MAX_TIMESTAMP_LOOKAHEAD starts after the TIME_PREFIXif specified and defaults to 150 characters. Since he specified it and the timestamp immediately follows the prefix, the above should work.

I hope this helps.

Example of it working is below.

• Josef

[sundareshr]

Try adding a MAX_TIMESTAMP_LOOKAHEAD in your props.conf. Specifies how far (in characters) into an event Splunk should look for a timestamp. In your above exaple, it appears to be around 1100