Splunk Search
Highlighted

Dealing with bizarre embedded timestamp

Builder

Hey everyone. Right now I'm dealing with some CSV files that are set up in the following format: line 1: version header line 2: header start message line 3-N: interesting messages line N+1: footer end message

The first element in each line contains a record ID. The record ID is actually composed of numerous fields, including the timestamp, put together to create a unique record ID. Here's the format:

Event Counter number (NOT zero padded), System ID, Date, and Time Zone

Examples:

Event 4, System ID 84732570, Date 12/22/2008 14:34:44.5390, and EST

48473257020081222143444.5390-050000

Event 10, System ID 84732570, Date 12/22/2008 14:35:22.4580, and EST

108473257020081222143522.4580-050000

Issues: I can get the full date/time field with the following regex: (([0-9]{14}).([0-9]{4})) How do I define that and use it to timestamp the events? Any help would be very appreciated.

Highlighted

Re: Dealing with bizarre embedded timestamp

Splunk Employee
Splunk Employee

Check this out: http://www.splunk.com/base/Documentation/4.1.5/admin/ConfigurePositionalTimestampExtraction

Example: If an event looks like:

1989/12/31 16:00:00 ed May 23 15:40:21 2007 ERROR UserManager - Exception thrown Ignoring unsupported search for eventtype: /doc sourcetype="access_combined" NOT eventtypetag=bot

To identify the timestamp: May 23 15:40:21 2007 

[source::/Applications/splunk/var/spool/splunk]
TIME_PREFIX = \d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2} \w+\s 
MAX_TIMESTAMP_LOOKAHEAD = 44

Then for this:

Examples Event 4, System ID 84732570, Date 12/22/2008 14:34:44.5390, and EST 48473257020081222143444.5390-050000

you might want to use

TIME_PREFIX = .*\,\s\Date\s 

which will capture "Examples Event 4, System ID 84732570, Date " as the time prefix and use 12/22/2008 for your event..

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Builder

My apologies, the event 4/system ID stuff was just to denote the upcoming example and explain what each of the items were. All the file has inside of it is the numeric timestamp in this form. I don't think position will work since the event id isn't 0 padded, and therefore can be any length which would destroy using count as an option.

Each row is started with a field like this, no header:
48473257020081222143444.5390-050000

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Splunk Employee
Splunk Employee

48473257020081222143444.5390-050000 and 108473257020081222143522.4580-050000 How many different other events are there, do they all have Eventnumber (4 or 10) followed by the "84732570" numbers? or are these all different? Can you paste a larger sample of these events for example? Reason i ask is you can use same idea of the time_prefix to use this...

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Splunk Employee
Splunk Employee

in other words is the system id always the same? – Genti♦ 0 secs ago

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Builder

The event ID is a counter, so it could be 1, all the way up to infinity (theoretically). It resets to 0 every N minutes (where n is the duration of the logging period for the file). The system ID is the same because these are all coming from one device, however in production there will be several devices (their ID's should remain the same though).

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Builder

Further examples:
13868473257020081222195315.8980-050000 48473257020081222143444.5390-050000 128473257020081222143522.6560-050000 48908473257020081223192836.2420-050000

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Splunk Employee
Splunk Employee

This is quite an ugly timestamp! Who wrote this software? Was it designed to be consumed by androids from the future?

The following should solve your problem. I am assuming that this record string is at the beginning of the event - if not, you will need to remove the carat and perhaps change the lookahead. I am also assuming that the month, day, and time will always be the same number of characters.

$SPLUNK_HOME/etc/system/local/props.conf:

[your_sourcetype]
TIME_PREFIX=^\d+(?=\d{14}\.)
TIME_FORMAT=%Y%m%d%H%M%S.%q%Z
Highlighted

Re: Dealing with bizarre embedded timestamp

Splunk Employee
Splunk Employee

thanks for this TIME_PREFIX=^\d+(?=\d{14}.) , Alex!!

0 Karma
Highlighted

Re: Dealing with bizarre embedded timestamp

Builder

Thanks for your post! The month, day, and time will always be the same number of characters, however they're not at the front of the record. For example in 108473257020081222143522.4580-050000, the date doesn't start until 20081222143522.4580-050000. The records are supposed to look weird, they're being used to act as unique record ID's so they concantenate 4 different fields together. I tried the script as mentioned and am not having a lot of luck 😞 I'll keep working on it tomorrow.

0 Karma