Splunk Search

How to extract a timestamp from several fields of a raw record after some time offset calculation?

MaxxY
New Member

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?

Thanks

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

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")

View solution in original post

0 Karma

javiergn
SplunkTrust
SplunkTrust

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")
0 Karma

javiergn
SplunkTrust
SplunkTrust

Did this work for you?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...