how to format date and time in searches

[samble]

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)

[somesoni2 · Accepted solution]

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 *

[samble]

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

[woodcock]

Do it like this:

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

[samble]

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

[woodcock]

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.

[samble]

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"

[FritzWittwer_ol]

use

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

or

| 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_...

[lguinn2]

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.

[samble]

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