topic Re: Log files with different Time Formats in Splunk Enterprise

Log files with different Time Formats

dowdag — Wed, 10 Jun 2020 16:09:32 GMT

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!

Re: Log files with different Time Formats

adonio — Wed, 19 Jun 2019 02:31:15 GMT

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

Re: Log files with different Time Formats

DavidHourani — Wed, 19 Jun 2019 02:49:27 GMT

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

Re: Log files with different Time Formats

dowdag — Wed, 19 Jun 2019 15:34:27 GMT

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!

Re: Log files with different Time Formats

DavidHourani — Wed, 19 Jun 2019 17:11:07 GMT

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!

Re: Log files with different Time Formats

dowdag — Wed, 19 Jun 2019 18:02:02 GMT

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

Re: Log files with different Time Formats

DavidHourani — Wed, 19 Jun 2019 19:46:03 GMT

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

Re: Log files with different Time Formats

dowdag — Wed, 19 Jun 2019 20:55:59 GMT

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 🙂

Re: Log files with different Time Formats

DavidHourani — Wed, 19 Jun 2019 21:31:20 GMT

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.

Re: Log files with different Time Formats

dowdag — Wed, 19 Jun 2019 22:09:32 GMT

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!

Re: Log files with different Time Formats

DavidHourani — Thu, 20 Jun 2019 04:27:27 GMT

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!

Re: Log files with different Time Formats

dowdag — Thu, 20 Jun 2019 21:47:21 GMT

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