Getting Data In

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

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

alt text

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?

1 Solution

jkuepker_splunk
Splunk Employee
Splunk Employee

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.

alt text

  • Josef

View solution in original post

jkuepker_splunk
Splunk Employee
Splunk Employee

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.

alt text

  • Josef

sundareshr
Legend

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...