Convert Number to Hour

[aquinojason]

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

[ITWhisperer · Accepted solution]

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

[aquinojason]

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

[ITWhisperer]

Is DATE a multivalue field perhaps?

[aquinojason]

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.

[ITWhisperer]

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?

[aquinojason]

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

[aquinojason]

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 !