topic Configure timestamp based only on Month and Year in Getting Data In

Configure timestamp based only on Month and Year

MaximeMouquet — Thu, 20 Apr 2017 11:48:17 GMT

Hello,

I have a problem to configure the timestamp on one of my source CSV file.

In this file, I have a column containing the timestamp. Its format is %Y%m (for example 201701 for January 2017). When I define the TIME_FORMAT to %Y%m, it does not work, I still have the error Failed to parse timestamp. I think it is because my timestamp does not contain a specific date.

Is there a way to define a timestamp on data despite it contains only the Year and the Month?

Thanks for your reply.

Maxime

Re: Configure timestamp based only on Month and Year

adonio — Thu, 20 Apr 2017 15:46:56 GMT

Hello MaximeMouquet,
sharing some screenshot in an answer below. i am joining the question as i was not able to extract timestamp with year and month only. However, splunk is all about time and ability to distinguish between events based on time. What is the use case for this data? can the application creating this data add a date to the column that has the timestamp?
finally, its worthwhile to include the field that contains the time in props.conf: TIMESTAMP_FIELDS = timeField
hope it helps

Re: Configure timestamp based only on Month and Year

adonio — Thu, 20 Apr 2017 15:48:58 GMT

adding screenshots per comment above:
one with only year and month and second with year month and day

Re: Configure timestamp based only on Month and Year

DalJeanis — Thu, 20 Apr 2017 15:59:44 GMT

In this case, at search time, I use...

| eval MyField=strptime(MyYYMMfield."01","%Y%m%d")

...which is what you'd hope the default behavior would be.

Worst case scenario, you'd have to do three steps, first to create a calculated field that concatenates "01" on the end, second to parse the field into a time format, and third to rewrite the timestamp. Given the screenshot, I'd think you can do it in two.

Re: Configure timestamp based only on Month and Year

MaximeMouquet — Fri, 21 Apr 2017 12:54:33 GMT

The problem I have is exactly the same as adonio so I am not the only one, thanks for screenshots.

The solution you provide DalJeanis is great and it works but only if we display results in a timechart or a table.
In my case, I would like to reuse this timestamp in the timerange picker of my report or dashboard. Is there a way to do that ? I did not find it...

Re: Configure timestamp based only on Month and Year

DalJeanis — Fri, 21 Apr 2017 14:26:15 GMT

@MaximeMouquet Basically, if some data is being stored as a time field, then you add the "01" and calculate is as the first day of the month. If it is being stored as a display field, you make sure it is in "YYYY/MM" or "YYYY-MM" order so that comparisons will work correctly.

Time pickers will work fine if the underlying data is stored as a date/time. If the data is being stored in a display format, then you will need an additional step to translate the date(s) picked into YYYY/MM format, and then it should work fine.

That extra step is usually coded as running a trivial query to convert the format. See this thread for some sample code... https://answers.splunk.com/answers/438999/dashboard-how-can-i-convert-a-token-from-a-time-pi.html

Re: Configure timestamp based only on Month and Year

MaximeMouquet — Fri, 21 Apr 2017 15:08:05 GMT

I found a way to create dashboard with new timestamp that I specify in the search with the eval command and it works.

In my search, I use this : | eval _time=strptime(MyYYMMfield."01","%Y%m%d")

Then, I use a saved with summary index to store the result of my first search. For the events in the summary index, the timestamp is not the orginal one of the original event but the one calculated in the search with the eval function.

Thanks for your help.