topic Re: how to format date and time in searches in Splunk Search

how to format date and time in searches

samble — Thu, 13 Aug 2015 02:22:28 GMT

In my logs that is pulled into Splunk the time is recorded as datetime="2015-08-13 01:43:38" . So when I do a search and go to the statistics tab, the date and time is displayed with the year first, then the month and the date and the time. How can I format the field so that it will be in the following format

MM-DD-YYYY 00:00 AM or PM (08-13-2015 01:43 AM)

Re: how to format date and time in searches

lguinn2 — Thu, 13 Aug 2015 05:55:30 GMT

If your logs are parsed properly, each event will also have a _time field - it appears in the left column when you search. This is the time field that I would use, as it takes into account the fact that different logs and servers may have different timezones.

To use it as you described:

yoursearchhere
| eval TimeOutput=strftime(_time,"%x %r")
| fields TimeOutput _raw

Although most of the time, Splunk will format the time appropriately for you, depending on the statistics. Exactly what did you want to calculate?
You can find out more info about strftime by Googling - it is a standard formatting function in many computer languages.

Re: how to format date and time in searches

FritzWittwer_ol — Tue, 29 Sep 2020 06:59:24 GMT

use

| convert timeformat="%m-%d-%Y %l:%M %p" ctime(_time) AS c_time | table _time, c_time

| eval strf_time =strftime(_time, "%m-%d-%Y %l:%M %p")  | table _time, strf_time

This results in

  2015-08-13 06:33:17   08-13-2015 6:33 AM

There are no leading zeros on the hour. See also http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Convert and http://docs.splunk.com/Documentation/Splunk/6.2.4/SearchReference/CommonEvalFunctions#Date_and_Time_functions

Re: how to format date and time in searches

samble — Thu, 13 Aug 2015 11:06:26 GMT

I do not see _time field as a field that is extracted in the left, but it does use the _time field when displaying the data in the statistics tab. I'm trying to display the temperature in the data closets for a 24 hour period in a dashboard using the time chart function. When I try the above it does display the time correctly ( would be nice if I could display time as 00:00 AM or PM instead and avoid the seconds) but the columns for the cabinets is missing. Now I end up with only 3 columns timeoutput, _raw and time

Below is my original search

key=Temp | timechart span=30m latest(value) by host limit=0

Re: how to format date and time in searches

woodcock — Thu, 13 Aug 2015 12:58:00 GMT

Do it like this:

key=Temp | timechart span=30m latest(value) by host limit=0 | fieldformat _time = strftime(_time,"%x %r")

Re: how to format date and time in searches

samble — Thu, 13 Aug 2015 13:02:27 GMT

Now it displays all the columns I want, but the time is not displayed correctly, it just has a bunch of characters under the _time column. Below is an example.

_time
0NaN-NaN-NaN NaN:NaN:NaN

Re: how to format date and time in searches

woodcock — Thu, 13 Aug 2015 13:21:14 GMT

Are you sure you copied it exactly as the answer? I just re-tested it and it works fine. How are your events created (perhaps something is not creating the _time field correctly because the error is from strftime saying that it is not finding a number to use N=Not, a=a, N=Number -> NaN -> Not-a-Number.

Re: how to format date and time in searches

samble — Thu, 13 Aug 2015 13:39:53 GMT

Thank you for taking the time to answer this question. I copied the line above as is in my search window and that is what I got. Below is how the time is displayed in the logs.

server host="NOC 06thFL E" address="xxx.xx.xxx.xx" name="WatchDog 15" product-version="1.5.1" mac-address="00:04:A3:C9:BD:CF" datetime="2015-08-13 13:25:58"

Re: how to format date and time in searches

somesoni2 — Thu, 13 Aug 2015 15:20:01 GMT

The field _time (or any field starting with underscore) is special/internal fields generated by Splunk and will not be visible on the Field sidebar. Also, since this is a special field, the fieldformat does't really changes the format of _time, so what you need to do is to create a new regular field and use that. e.g.

key=Temp | timechart span=30m latest(value) by host limit=0 | eval Timestamp=strftime(_time,"%x %r") | fields - _time | table Timestamp *

Re: how to format date and time in searches

samble — Thu, 13 Aug 2015 15:31:59 GMT

That did the trick, now I have the table in the way it should be. Thanks again.