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")
| 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")
| 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")
Thank you. But would you know why I am getting this as _time result?
Is DATE a multivalue field perhaps?
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.
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?
It's look like there is an issue with the input itself so it is not being converted properly. Thanks for the help!
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 !