Splunk Search

Dealing with bizarre embedded timestamp

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

1 Solution

msarro
Builder

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

View solution in original post

msarro
Builder

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

araitz
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

araitz
Splunk Employee
Splunk Employee

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.

0 Karma

msarro
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

Genti
Splunk Employee
Splunk Employee

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

0 Karma

Genti
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

msarro
Builder

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

0 Karma

msarro
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

Genti
Splunk Employee
Splunk Employee

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

0 Karma

Genti
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

msarro
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...