Splunk Search

how to format date and time in searches

samble
Path Finder

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)

0 Karma
1 Solution

somesoni2
Revered Legend

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 *

View solution in original post

somesoni2
Revered Legend

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
Path Finder

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

0 Karma

woodcock
Esteemed Legend

Do it like this:

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

samble
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

samble
Path Finder

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"

0 Karma

FritzWittwer
Contributor

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
Legend

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.

0 Karma

samble
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...