Splunk Search

## Extract date (timestamp) from raw data and source field

Contributor

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.

Tags (3)
Explorer

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...
_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
DATETIME_CONFIG =
LINE_BREAKER = ([\r\n]+)
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
category = Custom
pulldown_type = true

transforms.conf
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.

Super Champion

Hi @ips_mandar,

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

Contributor

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 .

Contributor

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.

Super Champion

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

Contributor

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

State of Splunk Careers