Reporting

Convert Number to Hour

aquinojason
Path Finder

Hi,

I need to convert the specific number  from 0 to 23 to 00:00:00 format.  I used the following in my statement by I am getting a blank on my eventHour field.

eval HOUR=(if (isnull(HOUR),"0",HOUR)) | eval eventHour=strftime(strptime(HOUR,"%k"),"%H")

This will be used to rebuild the _time

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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")

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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")
0 Karma

aquinojason
Path Finder

Thank you. But would you know why I am getting this as _time result?

aquinojason_0-1619803489645.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Is DATE a multivalue field perhaps?

0 Karma

aquinojason
Path Finder

Hi,

Sorry but what do you mean by multivalue?  The DATE from our input is formatted as 01JAN2021 so I used

eval DATE=strftime(strptime(DATE,"%d%b%Y"),"%Y-%m-%d") to reformat it.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It looks like you are doing it correctly. Can you share the full search (anonymised where necessary) so we can see where the issue might be coming from?

0 Karma

aquinojason
Path Finder

It's look like there is an issue with the input itself so it is not being converted properly. Thanks for the help!

0 Karma

aquinojason
Path Finder

Hi,

Here is my full search.

index=index1 sourcetype=sourcertype1 MVS_SYSTEM_ID != MVS_SYSTEM_ID | multikv forceheader=1 | dedup ACCOUNT_CODE DATE MVS_SYSTEM_ID CALCMIPS | eval DATE=strftime(strptime(DATE,"%d%b%Y"),"%Y-%m-%d") | lookup lookup.csv ACCOUNT_CODE OUTPUT Application BusinessUnit ApplicationRTO | eval Application=(if (isnull(Application),"Others",Application)) | eval BusinessUnit=(if (isnull(BusinessUnit),"Others",BusinessUnit)) | 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 Application BusinessUnit DATE SYSTEM_ID CALCMIPS eventHour _time | chart avg(CALCMIPS) over _time span=1mon by BusinessUnit limit=0

Thanks !

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...