Getting Data In

CSV multiple timestamp fallback

anthonysomerset
Path Finder

Hi

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?

0 Karma
1 Solution

DavidHourani
Super Champion

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

View solution in original post

DavidHourani
Super Champion

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

anthonysomerset
Path Finder

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

0 Karma

DavidHourani
Super Champion

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.

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...