I got a CSV log, and typical record inside is as below:
Header1, Header 2, Header 3, Header 4, 20150703, value1, value2, value3, value4
The initial "Header x" are some IDs. The date is shown in the fifth field, saying
20150703 in the example above and the host records a parameter every 6 hours and puts it into "value x". So, value1 is captured at 6:00:00, value2 at 12:00:00, value3 at 18:00:00, and value4 at 24:00:00
Currently, I want to break the sample record into the events as below:
Header1, Header 2, Header 3, Header 4, 20150703, value1
Header1, Header 2, Header 3, Header 4, 20150703, value2
Header1, Header 2, Header 3, Header 4, 20150703, value3
Header1, Header 2, Header 3, Header 4, 20150703, value4
Basically, the timestamp for each event should be calculated by the date value plus the time offset of each value. For example, timestamp of the first event should be the combination of 20150703 and 6:00:00. The ultimate _time should be 015-07-03T18:06:40.000+8:00.
How could I extract such kind of timestamp?
Assuming your Headers + Date can uniquely identify those 4 events happening each day and your events are happening in order, you could try something like this:
| inputcsv yourcsv.csv | streamstats count by Header1, Header2, Header3, Header4, Date | eval offsetInSecs = 6 * count * 3600 | eval date_epoch = strptime(Date, "%Y%m%d") | eval time_epoch = date_epoch + offsetInSecs | eval timestamp = strftime(time_epoch, "%Y-%m-%dT%T.%3N:%z")