How to make Splunk recognize a timestamp that is logged across 2 fields with 3 digit time

[paulbannister]

I'm currently monitoring a directory of CSV files with a universal forwarder (UF) that has the timestamp split across 2 fields, which isn't a problem if the time is after midday:

14-Nov-17,SOME_RANDOM_DATA,1525

Which gives me the following correct timestamp (11/14/17 3:25:00.000 PM)

However before midday the time appears in a 3 digit format:

14-Nov-17,SOME_RANDOM_DATA,740

Which should give me (11/14/17 7:40:00.000 AM), but Splunk just won't recognize the 3 digit format.

I've tried every combination of Date\Time format variables I can think of and even made an attempt at a custom datetime config but all to no avail.

I hoping I'm overlooking a simple solution but any insight anyone can offer will be greatly appreciated.

[woodcock · Accepted solution]

Take over complete control with datetime.xml:

https://www.function1.com/2013/01/oh-no-splunking-log-files-with-multiple-formats-no-problem

[paulbannister]

Thanks for the nudge to re-visit the datetime.xml method, after stripping right back to basics and building from the ground up I managed to get it to parse the timestamp correctly with a custom config and stress level has now been greatly reduced

[gregbo]

I created a datetime.xml file following the instructions in that link and all I get is an error about "Couldn't find 'timePatterns' in config data for AggregatorProcessor"...any idea what that means?

[niketn]

@paulbannister, For Splunk to recognize your Time field as valid time either you will have to prefix single digit Hour and Minute with zero in your CSV being inserted or have colon as the separator between hour and minute. The strptime() function in Splunk will not be able to confirm which is Hour and which is minute otherwise. Also what if your time is 07:04, your CSV will have 74 which is even more confusing for time.

[paulbannister]

The source doesn't remove all 0's from the data, merely the initial 0 from the time field ( so 07:04 would be displayed as 704 in the csv) so this is most likely how the source system has the export field formatted. We have looked into pre-formatting the csv but this involves a manual step using excel which I am trying to avoid (although we may try and setup an idiot proof macro if needed, but again I'd rather avoid a manual step) and unfortunately the source system doesn't allow much in the form of customization of exports

[micahkemp]

If you index a line like the working example above, but well after the fact (specifically on a different date that the event itself), does it still work? For example, if you index the event 14-Nov-17,SOME_RANDOM_DATA,1525 right now what does it parse the timestamp as?

[paulbannister]

The data will index just fine in the above case, as I'm using old data as it is anyway to test, the only issue occurs when it the time is before midday and the first 0 is omitted from the time field, in which case Splunk defaults to the event time of the previous event