Splunk Search

How to able Formatting _time to be used in the timepicker?

elomotanpru
Path Finder

Hi everyone,

Pretty new to Splunk and would really appreciate your insight on my current project. Currently creating a dashboard where I want to use a timepicker to change the values in my charts depending on the time period selected by the user via the Date Range - Between.

Currently experiencing problems formatting my _time value to include DATE and eventHour together. Below is my search query and search result for reference.

Thank you in advance.

index=mainframe-platform sourcetype="mainframe:cecmaverage" EXPRSSN = D7X0
| dedup DATE EXPRSSN MIPS
| eval DATE=strftime(strptime(DATE,"%d%b%Y"),"%Y-%m-%d")
| eval HOUR=if (isnull(HOUR),"0",HOUR) | eval eventHour=substr("0".HOUR,-2,2).":00:00"
| eval _time=strptime(DATE." ".eventHour,"%Y-%m-%d %H:%M:%S")
| table DATE eventHour _time EXPRSSN MIPS

Screenshot 2022-03-22 095819.png

 

Labels (3)
0 Karma
1 Solution

Marco_Develops
Path Finder

@elomotanpru  There's many ways to do this, here's one way to do it.

index=* 
| eval DATE = strftime(_time, "%m/%d/%Y")
| eval TIME = strftime(_time, "%T") 
|eval DateAndTime = DATE +" "+TIME 
|table DATE TIME DateAndTime

@elomotanpru The following doc explains Date and Time formatting.

If this helped please like and accept as solution. 

https://docs.splunk.com/Documentation/Splunk/8.2.5/SearchReference/Commontimeformatvariables#Time_va...

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Is this perhaps just a presentation issue? If all the times on that particular page are 00:00:00, then they are displayed as just the date. If you look at a few pages further on, do you get the correct hours presented? Note that this is particular to the automatic formatting of the _time field.

You could use fieldformat if you want to force the format used

| fieldformat _time=strftime(_time,"%F %T")
0 Karma

venky1544
Builder

Hi @elomotanpru 

just for curiosity why not concatenate the two fields before using for time format conversion 

| eval _time=strptime(DATE." ".eventHour,"%Y-%m-%d %H:%M:%S") 

is it not strptime would convert it into epoch and then you need to use again strftime to convert the timestamp in the desired format you need 

probably something like below hope it helps 

| makeresults |eval DATE="2021-01-03", eventhour="00:00:00"
|append [| makeresults |eval DATE="2021-01-03", eventhour="02:00:00"]
|append [| makeresults |eval DATE="2021-01-03", eventhour="03:00:00"]
| eval newtime=DATE." ".eventhour
| eval finaltime=strptime(newtime,"%Y-%m-%d %H:%M:%S")
| eval date = strftime(finaltime,"%d/%m/%Y %H:%M:%S")
|fields - finaltime _time newtime

0 Karma

Marco_Develops
Path Finder

@elomotanpru  Can you post an example of how you want the time & date to look like? 

0 Karma

elomotanpru
Path Finder

Hi,

The desired out put would be something like this:
3/14/2022 09:00:00

0 Karma

Marco_Develops
Path Finder

@elomotanpru  There's many ways to do this, here's one way to do it.

index=* 
| eval DATE = strftime(_time, "%m/%d/%Y")
| eval TIME = strftime(_time, "%T") 
|eval DateAndTime = DATE +" "+TIME 
|table DATE TIME DateAndTime

@elomotanpru The following doc explains Date and Time formatting.

If this helped please like and accept as solution. 

https://docs.splunk.com/Documentation/Splunk/8.2.5/SearchReference/Commontimeformatvariables#Time_va...

 

elomotanpru
Path Finder

Yes it worked for the statistics table version. Would also be able to help me applying this for the time picker version (please see screenshots for reference). I was unable to use the same search query. Did I use the wrong token (timerange) by any chance when using it for earliest and latest?

01.png02.png

0 Karma

Marco_Develops
Path Finder

@elomotanpru  I was able to get it to work on my system, your Where statement looks strange, what are you trying to accomplish. 

Query.PNG

 

Dashboard.PNG

-Marco 

0 Karma

elomotanpru
Path Finder

Sorry for the delayed response. When I tried using the same query with the timepicker, the results was that it could not be found but my other query that uses a Text Input was able to return the desired data minus the DateTime format. 

I thought that I needed to add the where clause for it to work because of that result. I check for the dates between February 1 2022 to March 1, 2022.

03.png

04.png05.png

0 Karma

venky1544
Builder

hi @elomotanpru 

it should be $timerange.earliest$  you have extra $sign

venky1544_0-1647966757063.png

 

Hope it helps

 

 

 

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...