Log files with different Time Formats

[dowdag]

Hello,

I have several log files that use this Date Time format: 2019-06-18 11:34:15,205
However one log file does not: Jun 18, 11:36:08.131667

Should I be using "Field Transforms" to change the TimeStamp to a UNIX style...
Would you do this at the time file is uploaded or Indexed?

I can accomplish is inline but what is 'best practices' ?

| rex field=TimeStamp "(?<Month>\w+)"
| lookup MonthStrToNum MonthAbrv as Month OUTPUT MonthNumber
| rex field=TimeStamp "\w+\s(?<Day>\d+)"
| eval year=strftime(now(), "%Y") 
| rex field=TimeStamp "^.+,\s(?<Time>[\d:.]+)"
| eval DateTimeStr=strftime(strptime (year . "-". MonthNumber . "-" . Day ." " . Time, "%Y-%m-%d %H:%M:%S,%6N"), "%Y-%m-%d %H:%M:%S,%3N")

using _time is not options as I want to look TimeStamps in log file and not the _time it was indexed.

Thanks for any suggestions!

[DavidHourani]

Hi @dowdag,

If you're using the same sourcetype for both files then your approach will work well.

However, if you have the possibility to split into multiple sourcetype that would preferable. You'll then be able to leverage time extraction at index time and it will also make it easier for you to extract and manage fields for each type of data files.

Cheers,
David

[dowdag]

I am using different source types for each type of log file. What I was asking is if I can an inline transformation as the time data is feeding into splunk.
Thanks!

[DavidHourani]

oh, yeah in that case it's totally possible. Your query seems good. You can also apply the extractions on index time if needed, but that won't apply on already existing logs.
If you like go ahead and post the format you have in TimeStamps and what you would like it to look like and I can see if there is any room for improving your query.
Cheers!

[dowdag]

Please see the start of this thread. 🙂

I am stuck with this: Jun 18, 11:36:08.131667
and I feel it will cause issues moving forward.
Regards,
Dave

[DavidHourani]

So if this : Jun 18, 11:36:08.131667 is your TimeStamp field. You can replace the entire logic above with this for inline :

| eval DateTimeStr=strftime(strptime(TimeStamp,"%b %d, %H:%M:%S,%6N"), "%Y-%m-%d %H:%M:%S,%3N")

You can also use TIME_FORMAT at index time and set it as %b %d, %H:%M:%S,%6Nit will avoid you having to run any time formatting command.

Cheers,
David

[dowdag]

David,

I am doing what you suggested .

#field extraction
^(?P\w+\s+\d+,\s+\d+:\d+:\d+.\d+)(?:[^ \n]* ){4}(?P[^,]+),\s\w+-\d+\s\"(?P.+)\"

Source type is correct, however TimeStamp is still pulling back
Jun 06, 11:11:35.754855

The converted version that is in '_time' is correct!

| table _time TimeStamp 

But the hour field is offset -1 in '_time' and I have been told it is best not to use _time.

What did I miss?

Thanks 🙂

[DavidHourani]

Just to be sure I understand you, could you confirm this check list is good :
1- _time is being extracted as Jun 18, 11:36:08.131667 but with 1 hour offset. Possibly due to your user timezone.
2- TimeStamp is extracted properly.
3- The eval expression I gave you works well and gives you the right time.

Give me a yes/no for 1,2 & 3.

I have a feeling that all what your missing is a props.conf configuration to get _time in the right TZ then you can use _time instead of all that hassle.

[dowdag]

Yes to 1,2,3! So I cleared out all data and starting fresh.
There still is confusion on why someone would advice not to use _time,

My goal is to convert:
Jun 18, 11:36:08.131667
^(?P\w+\s+\d+,\s+\d+:\d+:\d+.\d+)
when data is uploaded or forwarded into splunk,

When I defined the source type I did use this
"%b %d, %H:%M:%S,%6N" - and I do see timestamp selected correctly in the Set Source Type dialog and Time zone is set to my local computer.
Thanks!

[DavidHourani]

Always use _time when possible, its way more efficient to use that compared to field extraction or a calculated field.

Last step for you is to set the right TZ to avoid that hour offset. So you should have in your props.conf:

[YourSourcetype]
....
TIME_FORMAT = %b %d, %H:%M:%S,%6N
TZ= <POSIX time zone string>
...

Hope that helps !
And please upvote and accept if its helpful!

[dowdag]

Hi David,

Thanks for your help... still a little unclear on how everything works.
I have set the timezone on my source types to CST and my VM instance has it's timezone set to CST. Now I read somewhere that splunk converts data to be indexed to UTC.... but when I import data and run a query
|table _time TimeStamp

no mater what source type it is _time is always an hour earlier. <>

 TIME_FORMAT = %b %d, %H:%M:%S,%6N
 TZ= <POSIX time zone string>

Can you provide a literal string example of what you mean by

Also in Source Type under TimeStamp / Advanced
what does the Timestamp prefix do. I have seen just a ^ but sure what this is doing.... any examples are appreciated....
Thanks!
Dave Gilden / Ft. Worth Tx

[adonio]

can you elaborate?
what is the problem you are trying to solve?