topic Extracting timestamps in custom data in Getting Data In

Extracting timestamps in custom data

Robbie1194 — Thu, 27 Jul 2017 11:23:49 GMT

Hi Guys,

I am trying to use the GUI to index a file that's not in a recognised format and I'm having issues with extracting the timestamp.

I have broken the event up fine but the timestamp is on the line shown below. The first time it finds "12:00:00" is incorrect so I'm looking to extract the incident time as the time section of my date and timestamp.

Date Of Incident: 12/02/2015 12:00:00 AM, Incident Time: 1250

In the timestamp prefix section I have told it to look after Date of Incident: but it only finds 12/02/2015 12:00:00 AM. Does anyone know of a way to tell it to continue looking so I add the correct incident time?

Any help would be appreciated as I am really struggling with this!

Thanks!

Re: Extracting timestamps in custom data

Robbie1194 — Thu, 27 Jul 2017 11:25:46 GMT

So just to make it clean (I didn't explain myself very well), the timestamp I'm looking to extract would be 12/02/2015 12:50:00

Re: Extracting timestamps in custom data

somesoni2 — Thu, 27 Jul 2017 12:19:07 GMT

In the Select sourcetype screen, go to advanced tab on left sidebar and add following attributes

TIME_PREFIX = Date Of Incident:\s+
TIME_FORMAT = %m/%d/%Y %H:%M:%S %p

Re: Extracting timestamps in custom data

Robbie1194 — Thu, 27 Jul 2017 12:27:57 GMT

Hi somesoni2

Unfortunately that doesn't work, it still finds 12/02/2015 12:00:00 instead of 12/02/2015 12:50:00.

I need to take the incident time value and add it to the Date of Incident date.

Re: Extracting timestamps in custom data

mattymo — Thu, 27 Jul 2017 12:44:35 GMT

Can you tell us more about this data source?

How often do you ingest events, is it a file monitor? do you have control of the format?

You may need to live with the date parsing and deal with the time at search time at this point. The data's format is poor and I would look at cleaning it up at the source if at all possible.

Re: Extracting timestamps in custom data

Robbie1194 — Thu, 27 Jul 2017 13:01:48 GMT

It's a file monitor and unfortunately there's no way of me changing the data's format 😞

Re: Extracting timestamps in custom data

gcusello — Thu, 27 Jul 2017 13:22:54 GMT

Hi Robbie1194,
I think that the only way is pre parse your files using a script!
Bye.
Giuseppe

Re: Extracting timestamps in custom data

gcusello — Thu, 27 Jul 2017 13:22:54 GMT

Hi Robbie1194,
I think that the only way is pre parse your files using a script!
Bye.
Giuseppe

Re: Extracting timestamps in custom data

mattymo — Thu, 27 Jul 2017 13:39:54 GMT

Ok, so let's talk use cases.

With the extraction of the "Date of Incident", you can at least be sure that your events indexed and are groupable by day. Then using an extracted field you could extract time. You can then eval a field that stitches them together and you could chart using that data.

What are some of the things you are going to try and achieve with this data...Charting incidents over time? reporting on the number of incidents over time? drilldown to view the incident?

What does a full event look like?

Splunk's power is in the ability to constantly change schema on the fly and to massage data, so there are many ways to go about this, but generally the most critical use case or item you are trying to achieve will dictate the levers to pull.

Re: Extracting timestamps in custom data

niketn — Thu, 27 Jul 2017 13:49:32 GMT

@Robbie1194, if the log always has 12:00:00 AM present for the Date field, you can try the following in your sourcetype:

TIME_FORMAT=%d/%m/%Y 12:00:00 AM, Incident Time: %H%M
TIME_PREFIX=Date Of Incident:\s+
MAX_TIMESTAMP_LOOKAHEAD=43

If it not not always fixed as 12:00:00 AM, then you might have to create your own datetime configuration xml (yourcustomdatetime.xml) instead of relying upon the default datetime.xml.

Here is a blog explaining the same: https://www.splunk.com/blog/2014/04/23/its-that-time-again.html

Re: Extracting timestamps in custom data

somesoni2 — Thu, 27 Jul 2017 13:50:57 GMT

Try with this

TIME_PREFIX = Date Of Incident:\s+
 TIME_FORMAT = %m/%d/%Y 12:00:00 AM, Incident Time: %H%M

Re: Extracting timestamps in custom data

mattymo — Thu, 27 Jul 2017 13:55:01 GMT

worked for me

as long as the pattern stays the same should work.

Re: Extracting timestamps in custom data

mattymo — Thu, 27 Jul 2017 13:55:46 GMT

looks good, like niketnilay said, as long as you can trust that regex will always hit, you are golden