topic Re: Convert Number to Hour in Reporting

Convert Number to Hour

aquinojason — Fri, 30 Apr 2021 16:51:43 GMT

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

Re: Convert Number to Hour

ITWhisperer — Fri, 30 Apr 2021 17:16:59 GMT

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

Re: Convert Number to Hour

aquinojason — Fri, 30 Apr 2021 17:25:06 GMT

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

Re: Convert Number to Hour

ITWhisperer — Fri, 30 Apr 2021 17:33:28 GMT

Is DATE a multivalue field perhaps?

Re: Convert Number to Hour

aquinojason — Fri, 30 Apr 2021 17:53:41 GMT

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.

Re: Convert Number to Hour

ITWhisperer — Fri, 30 Apr 2021 17:57:42 GMT

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?

Re: Convert Number to Hour

aquinojason — Fri, 30 Apr 2021 18:05:09 GMT

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 !

Re: Convert Number to Hour

aquinojason — Sun, 02 May 2021 07:14:39 GMT

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