topic Dealing with bizarre embedded timestamp in Splunk Search

Dealing with bizarre embedded timestamp

msarro — Tue, 16 Nov 2010 01:29:40 GMT

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.

Re: Dealing with bizarre embedded timestamp

Genti — Tue, 16 Nov 2010 02:45:25 GMT

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

Re: Dealing with bizarre embedded timestamp

msarro — Tue, 16 Nov 2010 02:52:07 GMT

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

Re: Dealing with bizarre embedded timestamp

Genti — Tue, 16 Nov 2010 04:26:08 GMT

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

Re: Dealing with bizarre embedded timestamp

Genti — Tue, 16 Nov 2010 04:27:08 GMT

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

Re: Dealing with bizarre embedded timestamp

msarro — Tue, 16 Nov 2010 04:50:37 GMT

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

Re: Dealing with bizarre embedded timestamp

msarro — Tue, 16 Nov 2010 04:59:16 GMT

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

Re: Dealing with bizarre embedded timestamp

araitz — Tue, 16 Nov 2010 05:03:54 GMT

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

Re: Dealing with bizarre embedded timestamp

Genti — Tue, 16 Nov 2010 05:35:45 GMT

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

Re: Dealing with bizarre embedded timestamp

msarro — Tue, 16 Nov 2010 06:37:13 GMT

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.

Re: Dealing with bizarre embedded timestamp

araitz — Tue, 16 Nov 2010 08:39:44 GMT

Sorry I meant that I am assuming that the record string is at the beginning of the event, not the timestamp itself. Make sure that you have applied the correct sourcetype and that you have done it on the Splunk instance that is parsing the data.

Re: Dealing with bizarre embedded timestamp

msarro — Thu, 18 Nov 2010 05:11:56 GMT

I ended up resolving this today. I tried to get the prior solution to work however it wasn't quite functioning. After a closer look at the data that was coming in, there was another start time field that I could use. I verified that it matched the time in the event ID. Then I simply created a prefix which skipped x fields in (in this case, x=8). Here's how things look in my props.conf:

[AS_CDR]
TIME_PREFIX=(?i)^(?:[^,]*,){8}(?P<FIELDNAME>[^,]+)
TIME_FORMAT=%Y%m%d%H%M%S.%q%Z