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.
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?
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?
EVAL-_time = if(match(_raw,"SB"), _time+86400, _time)
Fixing the formatting issues caused by the transition to Khoros, copied from the @tiagofbmm post
Thanks @tiagofbmm
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!!
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.
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?
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.
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?
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)
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.
@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"?
Yes, I promoted my last comment to an answer so please accept it below