topic Re: Change _time before indexing in Getting Data In

Change _time before indexing

nuaraujo — Tue, 29 Sep 2020 18:25:56 GMT

Hello all,

I need to sum 1 day(86400 seconds) to my _time, if the event(_raw) includes the string "SB". This needs to be done, before indexing data.

My data is like this:
"RH",2018/03/12 03:21:40 -0700,,"Z76LVNG7N"
"FH",01
"SH",2018/03/11 00:00:00 -0800,2018/03/11 23:59:59 -0700,"Z76LVNG99RA7N",""
"SB","123456","Z76LVNG7N","3456789","","","T0006",2018/03/11 00:02:26 -0800,2018/03/11 00:02:26 -0800,"aa",234,"rud","ee",4,"eyt","S",,0,0,"dhl, bla, Postage, Ins:$0.00","","N","ShipSvc:First Class Package, blablabla, Postage, Ins:$0.00","","","","","","",,"","","","Express Checkout","01","02","","","","","","S","US"
"SB","1234564","Z76LVNG7N","34567894","","","T0006",2018/03/11 00:03:26 -0600,2018/03/11 00:02:26 -0800,"aa",234,"rud","ee",4,"eyt","S",,0,0,"dhl, bla, Postage, Ins:$0.00","","N","ShipSvc:First Class Package, blablabla, Postage, Ins:$0.00","","","","","","",,"","","","Express Checkout","01","02","","","","","","S","US"
"FF",2

To achieve this, I am using EVAL-_time in props .conf
[mydata]
DATETIME_CONFIG =
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TRANSFORMS-sourcetype = metadata,events,discard
REPORT-report_timestamp = report_timestamp
category = Custom
disabled = false
pulldown_type = true
FIELD_HEADER_REGEX = ^\"(?:CH)\"
FIELD_DELIMITER = ,
*EVAL-_time = strftime(if(match(_raw,"SB"),_time+86400,_time),"%Y/%m/%d %H:%M:%S %z")*

Can someone help me understand what am I doing wrong?

NOTE: I tested this EVAL string during search time and it works well.

Re: Change _time before indexing

tiagofbmm — Tue, 13 Mar 2018 17:45:37 GMT

The EVAL you are setting in the props.conf is not done on Index Time.

It is done every time you do a search on that sourcetype mydata

What exactly are you seeing, the time is not modified as you expected?

Re: Change _time before indexing

deepashri_123 — Tue, 13 Mar 2018 17:47:08 GMT

Hey nuaraujo,

Can you try adding something as TZ_ALIAS in props.conf
Refer this link :
http://docs.splunk.com/Documentation/Splunk/7.0.2/Data/Applytimezoneoffsetstotimestamps

Let me know if this helps!!

Re: Change _time before indexing

nuaraujo — Tue, 13 Mar 2018 18:08:39 GMT

Thanks @tiagofbmm for the quick reply to my question.

The problem that I have in my data is that, this is a report file, generated daily, but with data from previous day.

In the example that I posted, the report, was generated on 2018/03/12.
"RH",2018/03/12 03:21:40 -0700,,"Z76LVNG7N"

The data (in the lines that start with SB), contains a field that splunk is using as timestamp
"SB","123456","Z76LVNG7N","3456789","","","T0006",2018/03/11 00:02:26 -0800,2018/03/11 00:02:26 -0800,"aa",234,"rud","ee",4,"eyt","S",,0,0,"dhl, bla, Postage, Ins:$0.00","","N","ShipSvc:First Class Package, blablabla, Postage, Ins:$0.00","","","","","","",,"","","","Express Checkout","01","02","","","","","","S","US"

In this example that I am sharing, I want to convert the date 2018/03/11 00:02:26 -0800 to 2018/03/12 00:02:26 -0800

I thought that using this eval in the props.conf of my indexer would change the value of _time, before the data is indexed.

Currently, after cleaning the index and reindex my data, nothing happens. I suspect that I may need to use a TRANSFORM instructions.

Re: Change _time before indexing

nuaraujo — Tue, 13 Mar 2018 18:26:22 GMT

Thanks deepashri, but a TZ_ALIAS will not solve my issue because I the data is from the previous day. I really need to sum 86400 seconds to the _time field.

Re: Change _time before indexing

tiagofbmm — Tue, 29 Sep 2020 18:28:49 GMT

I'm not sure you can change an indexed field such as _time with an EVAL in the props.conf. Could you test doing an EVAL to another field, such as time_test?

Re: Change _time before indexing

tiagofbmm — Tue, 13 Mar 2018 18:40:42 GMT

Hey I just tested your exact EVAL and you do can change it in Search Time with that EVAL.
The problem is not the time itself, I think it is the strptime:

Change the expression to EVAL-_time=if(match(_raw,"SB"),_time+86400,_time)

Also...
Have you restarted Splunk after changing the props? (Pardon me if it is a stupid question, but I don't see anything else wrong with the eval expression)

Re: Change _time before indexing

nuaraujo — Tue, 13 Mar 2018 18:56:40 GMT

During Search Time, works really well. I already tested it before.

At this stage, there are no stupid questions @tiagofbmm, but yes, I restarted my indexer and deleted the previous data in it. I guaranteed that the indexer was clean, before all my test.

https://ibb.co/gFHyec

link text

Re: Change _time before indexing

tiagofbmm — Tue, 29 Sep 2020 18:28:52 GMT

This is really weird. I just ingested your line of data, created a calculated field in the UI (that goes exactly to props as you have): EVAL-_time=if(match(_raw,"SB"),_time+86400,_time)

And it works like a charm.

Maybe it's time to doubt from more basic things.

Can you do a $SPLUNK_HOME/bin/splunk btool props list --debug and look for your EVAL and see if is a running configuration?

Btw, when splunk comes up, the logs say something about a misconfiguration ?

What is that Report-timestamp doing?

Re: Change _time before indexing

nuaraujo — Tue, 13 Mar 2018 19:36:29 GMT

@tiagofbmm, THANK YOU. It works.

Your suggestions makes total sense, because the _time is in seconds, even if it is shown as a normal date.

NOTE: Do you know how to mark your answer as "accepted answer"?

Re: Change _time before indexing

tiagofbmm — Tue, 13 Mar 2018 19:37:53 GMT

Yes, I promoted my last comment to an answer so please accept it below

Re: Change _time before indexing

nuaraujo — Tue, 13 Mar 2018 19:38:27 GMT

Thanks @tiagofbmm

Re: Change _time before indexing

malvidin — Wed, 22 Jul 2020 16:59:33 GMT

EVAL-_time = if(match(_raw,"SB"), _time+86400, _time)

Fixing the formatting issues caused by the transition to Khoros, copied from the @tiagofbmm post