Splunk Search

Extract date (timestamp) from raw data and source field

ips_mandar
Builder

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.

0 Karma

hmaldonado
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...
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.

DavidHourani
Super Champion

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

ips_mandar
Builder

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 .

0 Karma

ips_mandar
Builder

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.

0 Karma

DavidHourani
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

0 Karma

ips_mandar
Builder

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
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...