topic Re: how to extract month date and year and save in a new field from _time in Splunk Search

how to extract month date and year and save in a new field from _time

vrmandadi — Thu, 10 May 2018 17:13:32 GMT

I am trying to do field extraction from the _time only the month,date and year but just not getting it.I know strftime does what i want but I want to save it into a new field

Thanks,
vr

Re: how to extract month date and year and save in a new field from _time

niketn — Thu, 10 May 2018 17:49:44 GMT

@vrmandadi before trying to extract date, month and year from _time, have you analysed raw events in your index in verbose mode to see whether you already have default date fields i.e.

date_mday, date_month, date_year

You can also try the following search

<yourBaseSearch>
| table _time date_mday, date_month, date_year

Only if for some reason you don't see date_* fields, you may try the following regex

<yourBaseSearch>
| eval Time=strftime(_time,"%Y-%m-%d")
| rex field="Time" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)"
| table _time Time year month day

The reason why your regex is not working is that time is in epoch time format and it needs to be converted to String time first. I have converted _time to Time in example above. As stated, this is not best approach as `date*` fields might already be present.

Re: how to extract month date and year and save in a new field from _time

vrmandadi — Tue, 29 Sep 2020 19:27:27 GMT

hello @niketn

The data does not have date_mday, date_month, date_year that is why I am using regex to save it as a new field called date for

_time=2018-05-10 09:33:54

|rex field=_raw "(?(\d{4}-\d{2}-\d{2})[\s]+)"

expected output

2018-05-10

The search you have given me does not save it in the fields until I run the search everytime

Re: how to extract month date and year and save in a new field from _time

niketn — Thu, 10 May 2018 18:33:24 GMT

So you need Date in YYYY-mm-dd format, then rex is not required... Just the following should suffice.

<yourBaseSearch>
 | eval Time=strftime(_time,"%Y-%m-%d")

However, since Splunk deals with time series data, and your request is around manipulation of _time field. I would like to understand your use case so that we do not complicate things.

Why do you need YYYY-mm-dd?
Also once you have dates like 2018-05-10, what is it that you need to do next?
Or is it only for displaying to users?

Re: how to extract month date and year and save in a new field from _time

vrmandadi — Thu, 10 May 2018 18:45:23 GMT

I just need to have a field extracted named "date" which has YYYY-MM-DD from the _time ,the eval needs to run everytime,but I want to save it as a extracted field.Yep I want to show it in the fields column

Re: how to extract month date and year and save in a new field from _time

niketn — Tue, 29 Sep 2020 19:30:57 GMT

@vrmandadi if you need YYYY-mm-dd to be your _time field you can do so in props.conf using TIME_FORMAT and MAX_TIMESTAMP_LOOKAHEAD

For us to assist you with regular expression you will have to provide sample of your raw event with timestamp field. We would be interested in exact string time format in data and any pattern before or after timestamp field.

Re: how to extract month date and year and save in a new field from _time

somesoni2 — Thu, 10 May 2018 19:54:34 GMT

You would need to setup a calculated field to saved the strftime command output as a saved field. If you're creating the field using Splunk Web UI, follow instruction from below link.
https://docs.splunk.com/Documentation/Splunk/7.1.0/Knowledge/CreatecalculatedfieldswithSplunkWeb

For name give the date and for Eval expression use strftime(_time,"%Y-%m-%d")

To save the same thing in configuration files (if deploying through Deployer OR deployment server), add this in props.conf

[yourSourcetypeNameHere]
EVAL-date = strftime(_time,"%Y-%m-%d")

Re: how to extract month date and year and save in a new field from _time

vrmandadi — Thu, 10 May 2018 20:25:47 GMT

Thank you calculated fields has done the trick