I have an issue where I have a number of fields in my data in following format of "%H:%M:%S" and they are stored as text. Therefore I cannot do any stats calculations such as avg on these fields.
I am trying to convert these fields in EPOCH time using the strptime, this then allows me to calculate an average & convert back to "%H:%M:%S" format.
However, some of the calculations aren't converting back correctly.
Full search query:
sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG
| eval actual_stack_time1=strptime(actual_stack_time,"%H:%M:%S")
| eval handling_time1=strptime(handling_time,"%H:%M:%S")
| stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day DOW
| eval "Avg. Time on Stack"=strftime(actual_stack_time1, "%H:%M:%S")
| eval "Avg. Handling Time"=strftime(handling_time1,"%H:%M:%S")
| fields Day DOW "Call Volume" "Avg. Handling Time" "Avg. Time on Stack"
EXAMPLE before adding the strftime syntax:
Day DOW Call Volume actual_stack_time1 handling_time1
06/03 Tue 24 1521072478.75 1521072078.1666667
This should then convert to 01:01:18 and 01:07:58 but currently is returning as 00:01:18 and 00:07:58.
Can anyone help?
Thanks,
Jack
Hi,
Can you try:
sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG Day=06/03/2018
| convert num(actual_stack_time) as "actual_stack_time1"
| convert num(handling_time) as "handling_time1"
| stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day
| eval handling_time1=round(handling_time1,0)
| convert timeformat="%H:%M:%S" ctime(actual_stack_time1)
| convert timeformat="%H:%M:%S" ctime(handling_time1)
Still returning same result 😞
It appears to be adding an extra hour to every value. If I amend the Time Zone in administrator settings this also impacts it?
Any ideas?
Hey
Sorry but I checked the values and it should convert to 00:01:18 and 00:07:58, check here https://www.epochconverter.com/
Unsure what is going wrong because when I calculate the avg in Excel it is 01:01:18 and 01:07:58.
I've amended the search to the following:
sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG Day=06/03/2018
| convert num(actual_stack_time) as "actual_stack_time1"
| convert num(handling_time) as "handling_time1"
| stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day
| eval handling_time1=round(handling_time1,0)
| eval "Avg. Time on Stack"=strftime(actual_stack_time1,"%H:%M:%S")
| eval "Avg. Handling Time"=strftime(handling_time1,"%H:%M:%S")
Now returning with the values of 02:01:18 and 02:07:58. The actual_stack_time1=4078.75secs and handling_time1=3678secs. This converted should equal 01:01:18 and 01:07:58