Splunk Convert Duration in Seconds to HH:MM:SS

[willryals]

Hey there, 

Right now I have come close to completing an absolute epic in getting a multi-array json  API response converted to a semi reportable format. The final hurdle I am running into is getting seconds converted to hh:mm:ss for duration reporting. So far, I have tried the following different approaches, working with a field that just has numerical content:

eval TimeLoggedIn=tostring(TimeLoggedInSecs, "duration") This does not return any value 

eval TimeLoggedIn=strftime(TimeLoggedInSecs,"%H:%M:%S") This returns inaccurate values. EX: converts 25595 seconds into 1 hours, six minutes, and 35 seconds when it should be over 7 hours. but this is the only way that returns a value

convert num(TimeLoggedInSecs)| eval TimeLoggedIn=TimeLoggedInSecs/60 this returns no value. 

So far the community has been super helpful in explaining some less than common things and allowing me to learn and reverse engineer to solve the overall problem. But this is kinda frustrating as it's the only thing left in the way, and it seems to be the hardest. 

Any help or ideas would be greatly appreciated!

reportingthe news as presented by reporters for newspapers or radio or televisionMore (Definitions, Synonyms, Translation)

[tscroggins]

Hi @willryals,

Have you tried converting the field to a number first?

| eval TimeLoggedIn=tostring(tonumber(TimeLoggedInSecs), "duration")

strftime treats 25595 as a Unix epoch value, which is why it's converted to (1970-01-01) 07:06:35 UTC or a time dependent on your locale, e.g. 01:06:35 for US Central Time.

[willryals]

When I add that eval statement in, it returns no values. In fact, I am not sure why, but most of the eval statements I have tried have failed with no data returned... 

[tscroggins]

That implies TimeLoggedInSecs is null. Can you post an extended example with source events, if applicable?

[willryals]

Sure:

{ "paused_time" : "14585", "completed_calls" : "8", "paused_since" : "2021-01-20 13:20:23", "talking_to_name" : "", "login_type" : "login", "order" : "1", "login_time" : "24593", "extension" : "4826", "max_talk_time" : "2045", "time_of_last_call" : "2021-01-20 13:12:23", "paused" : "1", "account_id" : "1503", "missed_calls" : "1", "logged_in_status" : "logged_in", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "896" } { "paused_time" : "4245", "completed_calls" : "2", "paused_since" : "2021-01-20 14:24:00", "talking_to_name" : "", "login_type" : "login", "order" : "2", "login_time" : "7438", "extension" : "4824", "max_talk_time" : "1323", "time_of_last_call" : "2021-01-20 13:14:45", "paused" : "1", "account_id" : "1587", "missed_calls" : "13", "logged_in_status" : "logged_in", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "700" } { "paused_time" : "2338", "completed_calls" : "10", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "3", "login_time" : "22995", "extension" : "4847", "max_talk_time" : "3121", "time_of_last_call" : "2021-01-20 14:05:36", "paused" : "0", "account_id" : "1605", "missed_calls" : "39", "logged_in_status" : "logged_in", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "934" } { "paused_time" : "5266", "completed_calls" : "8", "paused_since" : "2021-01-20 14:30:03", "talking_to_name" : "", "login_type" : "login", "order" : "4", "login_time" : "13717", "extension" : "4869", "max_talk_time" : "1546", "time_of_last_call" : "2021-01-20 14:20:36", "paused" : "1", "account_id" : "1730", "missed_calls" : "7", "logged_in_status" : "logged_in", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "726" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "5", "login_time" : "0", "extension" : "4951", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1623", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "6", "login_time" : "0", "extension" : "4857", "max_talk_time" : "0", "time_of_last_call" : "2020-12-22 13:34:46", "paused" : "0", "account_id" : "1666", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "7", "login_time" : "0", "extension" : "4839", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1158", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "8", "login_time" : "0", "extension" : "4823", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1502", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "9", "login_time" : "0", "extension" : "4952", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1624", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "10", "login_time" : "0", "extension" : "4831", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1617", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "*******", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "11", "login_time" : "0", "extension" : "4832", "max_talk_time" : "0", "time_of_last_call" : "", "paused" : "0", "account_id" : "1601", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "&&&", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login", "order" : "12", "login_time" : "0", "extension" : "4829", "max_talk_time" : "0", "time_of_last_call" : "2021-01-15 10:28:09", "paused" : "0", "account_id" : "1523", "missed_calls" : "0", "logged_in_status" : "logged_off", "fullname" : "***", "talking_to_number" : "", "avg_talk_time" : "0" } { "paused_time" : "0", "completed_calls" : "0", "paused_since" : "", "talking_to_name" : "", "login_type" : "login",

[willryals]

That is post processing. Here is the raw structure that comes out of the system:

{
"response": {
"method": "switchvox.callQueues.getCurrentStatus",
"result": {
"call_queue": {
"extension": "2070",
"strategy": "ring_all",
"queue_members": {
"queue_member": [
{
"paused_time": "10011",
"completed_calls": "7",
"paused_since": "2021-01-20 12:43:40",
"talking_to_name": "",
"login_type": "login",
"order": "1",
"login_time": "19532",
"extension": "4826",
"max_talk_time": "2045",
"time_of_last_call": "2021-01-20 12:40:46",
"paused": "1",
"account_id": "1503",
"missed_calls": "1",
"logged_in_status": "logged_in",
"fullname": "***",
"talking_to_number": "",
"avg_talk_time": "966"
},
{
"paused_time": "2296",
"completed_calls": "1",
"paused_since": "2021-01-20 12:29:13",
"talking_to_name": "",
"login_type": "login",
"order": "2",
"login_time": "2377",
"extension": "4824",
"max_talk_time": "76",
"time_of_last_call": "2021-01-20 12:27:54",
"paused": "1",
"account_id": "1587",
"missed_calls": "0",
"logged_in_status": "logged_in",
"fullname": "***",
"talking_to_number": "",
"avg_talk_time": "76"
},
{
"paused_time": "1286",
"completed_calls": "8",
"paused_since": "2021-01-20 12:59:07",
"talking_to_name": "",
"login_type": "login",
"order": "3",
"login_time": "17934",
"extension": "4847",
"max_talk_time": "3121",
"time_of_last_call": "2021-01-20 12:37:39",
"paused": "1",
"account_id": "1605",
"missed_calls": "39",
"logged_in_status": "logged_in",
"fullname": "***",
"talking_to_number": "",
"avg_talk_time": "1058"
},
{
"paused_time": "3770",
"completed_calls": "6",
"paused_since": "",
"talking_to_name": "WIRELESS CALLER",
"login_type": "login",
"order": "4",
"login_time": "8656",
"extension": "4869",
"max_talk_time": "1546",
"time_of_last_call": "2021-01-20 12:57:17",
"paused": "0",
"account_id": "1730",
"missed_calls": "4",
"logged_in_status": "logged_in",
"fullname": "***",
"talking_to_number": "***",
"avg_talk_time": "689"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "5",
"login_time": "0",
"extension": "4951",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1623",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "6",
"login_time": "0",
"extension": "4857",
"max_talk_time": "0",
"time_of_last_call": "2020-12-22 13:34:46",
"paused": "0",
"account_id": "1666",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "7",
"login_time": "0",
"extension": "4839",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1158",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "8",
"login_time": "0",
"extension": "4823",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1502",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "9",
"login_time": "0",
"extension": "4952",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1624",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "10",
"login_time": "0",
"extension": "4831",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1617",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "11",
"login_time": "0",
"extension": "4832",
"max_talk_time": "0",
"time_of_last_call": "",
"paused": "0",
"account_id": "1601",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "12",
"login_time": "0",
"extension": "4829",
"max_talk_time": "0",
"time_of_last_call": "2021-01-15 10:28:09",
"paused": "0",
"account_id": "1523",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
},
{
"paused_time": "0",
"completed_calls": "0",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "13",
"login_time": "0",
"extension": "4874",
"max_talk_time": "0",
"time_of_last_call": "2020-12-07 11:45:36",
"paused": "0",
"account_id": "1703",
"missed_calls": "0",
"logged_in_status": "logged_off",
"fullname": "*******",
"talking_to_number": "",
"avg_talk_time": "0"
}
]
},
"waiting_callers": {
"waiting_caller": {
"caller_id_name": "BRANDON YU",
"caller_id_number": "8134103644",
"position": "1",
"waiting_duration": "295"
}
},
"call_queue_name": "RS Tier II",
"account_id": "1465"
}
}
}
}

Greenfieldpreviously undevelopedMore (Definitions, Synonyms, Translation)

[tscroggins]

I'm not sure how you're parsing the data, of course, but I'm not seeing any issues with a combination of spath and eval:

| makeresults 
| eval json="{ \"paused_time\" : \"14585\", \"completed_calls\" : \"8\", \"paused_since\" : \"2021-01-20 13:20:23\", \"talking_to_name\" : \"\", \"login_type\" : \"login\", \"order\" : \"1\", \"login_time\" : \"24593\", \"extension\" : \"4826\", \"max_talk_time\" : \"2045\", \"time_of_last_call\" : \"2021-01-20 13:12:23\", \"paused\" : \"1\", \"account_id\" : \"1503\", \"missed_calls\" : \"1\", \"logged_in_status\" : \"logged_in\", \"fullname\" : \"*******\", \"talking_to_number\" : \"\", \"avg_talk_time\" : \"896\" }"
| spath input=json
| eval login_time=tostring(login_time, "duration")
| table _time login_time

_time	login_time
2021-01-21 00:44:26	06:49:53