topic Re: Extract date (timestamp) from raw data and source field in Splunk Search

Extract date (timestamp) from raw data and source field

ips_mandar — Tue, 11 Jun 2019 01:56:08 GMT

Hi
my events looks like-

  31,04:56:47:928,  abc:0xabc,  49.716720, -59.271553,197
  30,04:56:47:928,  abc:0xabc,  49.716720, -59.271553,197

where 31 OR 30 is day of month
and source field looks like - D:\\abc\\def\\XYLog09229190601.txt.zip:.\\XYLog09229190601.txt
from source- XYLog09229190601.txt I can know 190601 i.e. 01 June 2019
So by which way I can get _time as for first event as 31 MAY 2019 and for second event as 30 May 2019?
where day is extracted from event and month is manipulated as the events are always of previous 1-5 days than it shows date in source field.

Re: Extract date (timestamp) from raw data and source field

DavidHourani — Tue, 11 Jun 2019 06:13:31 GMT

Hi @ips_mandar,

This duplicates https://answers.splunk.com/answers/750976/extract-date-from-filename-source.html

You have to use INGEST_EVAL for this use-case and apply it as follows for props.conf :

[mysourcetype]
TRANSFORMS = myeval

And transforms.conf:

[myeval]
INGEST_EVAL= inputDate = substr(source,len(source)-9,6), inputDateEpoch=strptime(inputDate,"%y%m%d"), dateFromEvent=strftime(_time,"%y%m%d"), dateFromEventEpoch=strptime(dateFromEvent,"%y%m%d"), inputDateDelta=dateFromEventEpoch-inputDateEpoch, _time=_time-inputDateDelta

You can test this on your search assuming that your _time is automatically taken from the event and tagged with the current month and year:

|makeresults 
| eval source="XYLog09229190601.txt" 
| eval inputDate= substr(source,len(source)-9,6),inputDateEpoch=strptime(inputDate,"%y%m%d"),dateFromEvent=strftime(_time,"%y%m%d"),dateFromEventEpoch=strptime(dateFromEvent,"%y%m%d"),inputDateDelta=dateFromEventEpoch-inputDateEpoch,_time=_time-inputDateDelta

Let me know if that helps.

Cheers,
David

Re: Extract date (timestamp) from raw data and source field

ips_mandar — Tue, 11 Jun 2019 08:43:26 GMT

Thanks @DavidHourani Appreciate your answer..
Does it will satisfy all condition ? As I checked another example

    |makeresults 
 | eval source="dffgfXYLog09229190611.txt" 
 | eval _time="1560136057",a=substr(source,len(source)-9,6),inputDate= substr(source,len(source)-9,6),inputDateEpoch=strptime(inputDate,"%y%m%d"),dateFromEvent=strftime(_time,"%y%m%d"),dateFromEventEpoch=strptime(dateFromEvent,"%y%m%d"),inputDateDelta=dateFromEventEpoch-inputDateEpoch,_time=_time-inputDateDelta

Here I considered source field as today's date and event _time as yesterday then it should give me manipulated _time as yesterday but it won't.
Basic understanding is in file name 1-5 days above in name while event will always be behind 1 -5 days .
so basically I want event time as _time .

Re: Extract date (timestamp) from raw data and source field

ips_mandar — Tue, 11 Jun 2019 08:47:41 GMT

also how _time will automatically picks timestamp as in event only day of month is mentioned.
basic concept is file is copied in one folder where the files will contain data of yesterday/day before yesterday and this folder is monitored in splunk.
exact day only can be identified from each event and in event only day is mentioned and no month or year.

Re: Extract date (timestamp) from raw data and source field

DavidHourani — Tue, 11 Jun 2019 09:17:51 GMT

It will take day and time from the event, then for the month and year it will auto configure them to the time they were indexed

Re: Extract date (timestamp) from raw data and source field

ips_mandar — Tue, 11 Jun 2019 09:24:59 GMT

but below query will not give desired result of _time as yesterday

     |makeresults 
  | eval source="dffgfXYLog09229190611.txt" 
  | eval _time="1560136057",a=substr(source,len(source)-9,6),inputDate= substr(source,len(source)-9,6),inputDateEpoch=strptime(inputDate,"%y%m%d"),dateFromEvent=strftime(_time,"%y%m%d"),dateFromEventEpoch=strptime(dateFromEvent,"%y%m%d"),inputDateDelta=dateFromEventEpoch-inputDateEpoch,_time=_time-inputDateDelta

Re: Extract date (timestamp) from raw data and source field

hmaldonado — Wed, 30 Sep 2020 01:53:14 GMT

Hello @ips_mandar ,
I feel that I may be missing part of what you are asking for, but allow me to propose the following solution...
I have settings that will extract the date from the name of the file and the time of day from the event. Here are the sample events...
source= /Users/hansmaldonado/testing/dffgfXYLog09229190611.txt
_raw=30,04:56:47:928, abc:0xabc, 49.716720, -59.271553,197

If we assume that the last 6 digits in the source field represent the date, and if we assume that the time of day comes from "04:56:47:928" within the raw event, here are the settings that will extract _time as "06/11/2019 04:56:47.928"...

props.conf
[timestamp:test:splunkanswers]
TRANSFORMS-timestampeval = splunkanswers
DATETIME_CONFIG =
LINE_BREAKER = ([\r\n]+)
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
category = Custom
pulldown_type = true

transforms.conf
[splunkanswers]
INGEST_EVAL=date=strptime(replace(source,".*(?=\d{6})",""), "%y%m%d"),date:=strftime(date,"%m/%d/%Y"),newtime=strftime(_time,"%H:%M:%S.%3N"),timestamp=date." ".newtime,_time:=strptime(timestamp,"%m/%d/%Y %H:%M:%S.%3N")

Note the syntax of ":=", which is required for INGEST_EVAL if you are performing operations on fields that already have values AND if you want to keep the new value. If you use "=", you will end up with multiple values for the fields.

My solution requires that you make the source/name of the file reflective of the date of the events, then we pull the time of day from the event.

Please let me know if this solution is acceptable for your needs. If not, please clarify how I might edit the solution to fit your use case.