topic Re: CSV multiple timestamp fallback in Getting Data In

CSV multiple timestamp fallback

anthonysomerset — Wed, 30 Sep 2020 00:19:48 GMT

I have the following CSV format:

cgrid,run_id,tor,origin_id,request_type,tenant,category,account,subject,destination,setup_time,answer_time,usage,cost

currently my props is set to use the answer_time field for timestamp extraction, but somes this is effectively empty - e.g.:

6729deb0544ee7e070fc5435542a46f6f194c5a4,*default,*voice,24bc42be2202b3233669b06522bbff680gQAAC8WAAACBAAALxYAABfa2CKPkEHALB+DouyTTcix3zfVEmuvdz2ZCwFK/iDh0afBTmJ48N3K+SgnNIXs+ye+NV964OG1,*rated,zw.liquid.tel,call,2638677XXXXXX,2638677XXXXXX,263YYYYYYYYY,2019-05-02T05:59:53Z,0001-01-01T00:00:00Z,0,0.00000

specifically 0001-01-01T00:00:00Z

what i'd like to do is when this comes up, to use the setup_time field instead, i tried adding setup time as a second field in the props:

[CGRATES_RATED_CSV]
DATETIME_CONFIG =
FIELD_NAMES = cgrid,run_id,tor,origin_id,request_type,tenant,category,account,subject,destination,setup_time,answer_time,usage,cost
INDEXED_EXTRACTIONS = csv
KV_MODE = none
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIMESTAMP_FIELDS = answer_time,setup_time
TZ = UTC

however its not taking effect, the events are still taking answer_time and then generating as midnight on the same day, answer_time is my preferred timestamp, so i'd prefer to keep it and fall back to setup_time rather than always use setup_time

is this possible and how would i do this?

Re: CSV multiple timestamp fallback

DavidHourani — Thu, 02 May 2019 06:54:28 GMT

Hi @anthonysomerset,

As shown here, it's normal that in your case even when specifying both fields the timestamp will always be stuck on the first field (answer_time) :

TIMESTAMP_FIELDS = [ <string>,..., <string>]
* Some CSV and structured files have their timestamp encompass multiple
  fields in the event separated by delimiters. This setting tells Splunk 
  software to specify all such fields which constitute the timestamp in a
  comma-separated fashion.
* If not specified, Splunk software tries to automatically extract the 
  timestamp of the event.

https://docs.splunk.com/Documentation/Splunk/7.2.6/Admin/Propsconf

This is because Splunk will try to stick the fields together to get a date, but your first field is already complete and therefore the date will not use any other field. Your best way to work around this is to make sure your answer_time field is empty instead of using "0001-01-01T00:00:00Z" as a filler.

Cheers,
David

Re: CSV multiple timestamp fallback

anthonysomerset — Thu, 02 May 2019 06:58:31 GMT

so by that logic a transforms that detects presence and deletes 0001-01-01T00:00:00Z from the incoming line should work?

Re: CSV multiple timestamp fallback

DavidHourani — Thu, 02 May 2019 07:02:58 GMT

I was thinking more of getting rid of that line before even indexing, but yes transforms should do the trick as well 🙂 let me know if it works out for u! Try it first by manually editing that 0001-01-01T00:00:00Z out and then add the transforms.