Looping Through JSON and Adding HH:MM Values Together

Hi,

I hope someone can help me with this, I think I’m on the right track with how to go about this but possibly not doing it the most efficiently.


TL:DR :slight_smile:
How do I add HH:MM values together, i.e. 00:03 + 05:02 + 01:01 = 06:06
Then calculate their average (e.g. 02:02)


I’m pulling data from an API and I’m trying to calculate the average “First Response” and average “Resolution” time from this:

The API call can pull back many records like this:

Each of those items is then structured like this

My methodology is to loop through all items (32 in this example) and add each of the to_first_response “business_time_elapsed” values together and then divide the total by the number of items (32).

I thought the looping bit was going to be the issue but i appear to have that sorted with this script

foreach ($a in $apiResponse)
{
	if ($a.statistics.statistic_type -eq "to_first_response")
	{
		#Write-Host to test I get the right values	
		Write-Host $a.statistics[4].business_time_elapsed
	}
}

This outputs

00:03
00:02
00:01
00:01
00:01
00:02
00:04
00:03
...
...

Which is what I expect.

So now I want to replace the Write-Host with the calculation to add those values together and then divide by the number of items.

This is what I’m really struggling with.
How do I add those values together / divide them?

They are in the format of HH:MM

I feel like this is meant to have been the easier part :slight_smile:

Regards,
Jamie

Jamie,

Your screenshots are unreadable. And even if they were readable it wouldn’t help us to help you. Could you please share some sample data (formatted as code) we can copy and use for testing? Either you should post some JSON data or some CSV data.

A general tip: In PowerShell there is the cmdlet New-TimeSpan with type [TimeSpan] and there is the cmdlet Measure-Object. I’d expect both of them together could bring you a step further.

Hi,

Thank you for your response, sorry about the screenshots, they opened and appeared ok my side and i put them there so you could see the structure but i understand the code sample would be useful too so posted it below:

I will also take a look at the TimeSpan, not really used that before so will give it a go, thank you.

[
     {
          "id": 11023170,
          "number": 4420,
          "name": "string",
          "description": "string",
          "description_no_html": "string",
          "state": "Resolved",
          "priority": "Low",
          "category": {
               "id": 765171,
               "name": "string",
               "default_tags": "string",
               "parent_id": null,
               "default_assignee_id": 2130747
          },
          "subcategory": {
               "id": 767175,
               "name": "string",
               "default_tags": "string",
               "parent_id": 765171,
               "default_assignee_id": 2130747
          },
          "assignee": {
               "id": 2130747,
               "name": "string",
               "description": "",
               "disabled": false,
               "is_user": false,
               "reports_to": {
                    "id": 2130746,
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string",
                         "klass": "RealGroup"
                    },
                    "name": "string",
                    "href": "string"
               },
               "avatar": {
                    "type": "group",
                    "color": "#dfcd00",
                    "klass": "RealGroup"
               },
               "type": "RealGroup",
               "send_notifications": true,
               "memberships": [
                    {
                         "id": 1781064,
                         "user": "string"
                    },
                    {
                         "id": 1781066,
                         "user": "string"
                    },
                    {
                         "id": 1784446,
                         "user": "string"
                    }
               ]
          },
          "requester": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "created_at": "2022-02-02T16:32:15+00:00",
          "updated_at": "2022-02-02T16:37:44+00:00",
          "due_at": null,
          "sla_violations": [],
          "number_of_comments": 1,
          "user_saw_all_comments": false,
          "is_service_request": false,
          "created_by": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "resolved_by": {
               "id": 1834254,
               "name": "string",
               "disabled": false,
               "title": "",
               "email": "string",
               "created_at": "2021-07-06T14:27:22+01:00",
               "updated_at": "2022-01-04T09:02:29+00:00",
               "last_login": "2022-01-04T09:02:29+00:00",
               "phone": "",
               "mobile_phone": "",
               "role": {
                    "id": 347139,
                    "name": "Service Agent User",
                    "description": "Similar to an administrator but no access to setup.",
                    "portal": false,
                    "show_my_tasks": false
               },
               "salt": "739a247f1baa49c52c1be1a1e4d8e648f9f87e42",
               "group_ids": [
                    2131456,
                    2130747
               ],
               "available_for_assignment": false,
               "can_be_available_for_assignment": false,
               "custom_fields_values": [],
               "site": {
                    "id": 68887,
                    "name": "string",
                    "location": "string",
                    "description": "",
                    "time_zone": "string",
                    "language": "-1",
                    "business_record": null
               },
               "department": {
                    "id": 89604,
                    "name": "Information Technology",
                    "description": "",
                    "default_assignee_id": 2130747
               },
               "avatar": {
                    "type": "image",
                    "image_class": "avatar_image",
                    "sso_image_class": "",
                    "avatar_url": "string"
               },
               "reports_to": {
                    "group_id": 2130746,
                    "is_user": true,
                    "id": 1833802,
                    "name": "string",
                    "email": "string",
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string"
                    }
               }
          },
          "resolution_description": "string",
          "resolution_code": "Solved",
          "custom": "2022-02-02 16:36:40.170485",
          "href": "string",
          "href_account_domain": "string",
          "site": {
               "id": 68887,
               "name": "string",
               "location": "string",
               "description": "",
               "time_zone": "string",
               "language": "-1",
               "business_record": null
          },
          "department": {
               "id": 89731,
               "name": "string",
               "description": "",
               "default_assignee_id": 2130747
          },
          "cc": [],
          "custom_fields_values": [
               {
                    "id": 6824902,
                    "custom_field_id": 581688,
                    "name": "string",
                    "value": "string",
                    "attachment": null,
                    "options": "",
                    "type": 1,
                    "type_name": "Text",
                    "entity": null,
                    "user": null
               }
          ],
          "origin": "external",
          "comments": [
               {
                    "id": 13181695,
                    "body": "string",
                    "user": {
                         "id": 1835239,
                         "name": "string",
                         "email": "string",
                         "avatar": {
                              "type": "initials",
                              "initials": "string",
                              "color": "#ffa21e"
                         }
                    },
                    "created_at": "2022-02-02T16:36:36+00:00",
                    "updated_at": "2022-02-02T16:36:36+00:00",
                    "attachments": [],
                    "inline_attachments": [],
                    "shared_attachments": [],
                    "is_private": false,
                    "seen_by": [
                         1835239,
                         1834254
                    ],
                    "isTask": false,
                    "task_info": {},
                    "requester": null,
                    "commenter_id": 11023170,
                    "commenter_type": "Incident"
               }
          ],
          "attachments": [],
          "statistics": [
               {
                    "statistic_type": "last_reassigned",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.097+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.757+00:00\",\"timeSpent\":170,\"timeSpentBH\":170,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"-1\":0.956551476,\"2130747\":329.94227342399995},\"bh_summary\":{\"2130747\":330}}"
               },
               {
                    "statistic_type": "last_state_change",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":391873,\"title\":\"\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.228+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391878,\"title\":\"\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:36:40.464+00:00\",\"timeSpent\":105,\"timeSpentBH\":105,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391874,\"title\":\"\",\"startTime\":\"2022-02-02T16:36:40.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.842+00:00\",\"timeSpent\":65,\"timeSpentBH\":65,\"timeSinceCreation\":265,\"timeSinceCreationBH\":265,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"391873\":160.275407245,\"391878\":105.526080636,\"391874\":64.884937894},\"bh_summary\":{\"391873\":160,\"391878\":105,\"391874\":65}}"
               },
               {
                    "statistic_type": "State Changed",
                    "time": "00:04",
                    "time_elapsed": "00:04",
                    "business_time_elapsed": "00:04",
                    "value": "2022-02-02 16:37:44 UTC"
               },
               {
                    "statistic_type": "assignee_opened_show",
                    "time": "00:02",
                    "time_elapsed": "00:02",
                    "business_time_elapsed": "00:02",
                    "value": "2130747"
               },
               {
                    "statistic_type": "to_first_response",
                    "time": "00:03",
                    "time_elapsed": "00:03",
                    "business_time_elapsed": "00:03",
                    "value": "2022-02-02 16:34:55 UTC"
               },
               {
                    "statistic_type": "to_resolve",
                    "time": "00:06",
                    "time_elapsed": "00:06",
                    "business_time_elapsed": "00:06",
                    "value": "2022-02-02 16:37:44 UTC"
               }
          ],
          "tags": [
               {
                    "name": "string"
               },
               {
                    "name": "string"
               },
               {
                    "name": "string"
               }
          ],
          "incidents": [],
          "changes": [],
          "solutions": [],
          "associated_sla_names": [],
          "is_customer_satisfied": null,
          "customer_satisfaction_response": null,
          "total_time_spent": 0,
          "resolution": "string",
          "resolution_type": "Solved",
          "tasks": [],
          "time_tracks": [],
          "assets": [],
          "mobiles": [],
          "other_assets": [],
          "configuration_items": [],
          "discovery_hardwares": [],
          "purchase_orders": [],
          "request_variables": []
     },
     {
          "id": 11023171,
          "number": 4420,
          "name": "string",
          "description": "string",
          "description_no_html": "string",
          "state": "Resolved",
          "priority": "Low",
          "category": {
               "id": 765171,
               "name": "string",
               "default_tags": "string",
               "parent_id": null,
               "default_assignee_id": 2130747
          },
          "subcategory": {
               "id": 767175,
               "name": "string",
               "default_tags": "string",
               "parent_id": 765171,
               "default_assignee_id": 2130747
          },
          "assignee": {
               "id": 2130747,
               "name": "string",
               "description": "",
               "disabled": false,
               "is_user": false,
               "reports_to": {
                    "id": 2130746,
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string",
                         "klass": "RealGroup"
                    },
                    "name": "string",
                    "href": "string"
               },
               "avatar": {
                    "type": "group",
                    "color": "#dfcd00",
                    "klass": "RealGroup"
               },
               "type": "RealGroup",
               "send_notifications": true,
               "memberships": [
                    {
                         "id": 1781064,
                         "user": "string"
                    },
                    {
                         "id": 1781066,
                         "user": "string"
                    },
                    {
                         "id": 1784446,
                         "user": "string"
                    }
               ]
          },
          "requester": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "created_at": "2022-02-02T16:32:15+00:00",
          "updated_at": "2022-02-02T16:37:44+00:00",
          "due_at": null,
          "sla_violations": [],
          "number_of_comments": 1,
          "user_saw_all_comments": false,
          "is_service_request": false,
          "created_by": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "resolved_by": {
               "id": 1834254,
               "name": "string",
               "disabled": false,
               "title": "",
               "email": "string",
               "created_at": "2021-07-06T14:27:22+01:00",
               "updated_at": "2022-01-04T09:02:29+00:00",
               "last_login": "2022-01-04T09:02:29+00:00",
               "phone": "",
               "mobile_phone": "",
               "role": {
                    "id": 347139,
                    "name": "Service Agent User",
                    "description": "Similar to an administrator but no access to setup.",
                    "portal": false,
                    "show_my_tasks": false
               },
               "salt": "739a247f1baa49c52c1be1a1e4d8e648f9f87e42",
               "group_ids": [
                    2131456,
                    2130747
               ],
               "available_for_assignment": false,
               "can_be_available_for_assignment": false,
               "custom_fields_values": [],
               "site": {
                    "id": 68887,
                    "name": "string",
                    "location": "string",
                    "description": "",
                    "time_zone": "string",
                    "language": "-1",
                    "business_record": null
               },
               "department": {
                    "id": 89604,
                    "name": "Information Technology",
                    "description": "",
                    "default_assignee_id": 2130747
               },
               "avatar": {
                    "type": "image",
                    "image_class": "avatar_image",
                    "sso_image_class": "",
                    "avatar_url": "string"
               },
               "reports_to": {
                    "group_id": 2130746,
                    "is_user": true,
                    "id": 1833802,
                    "name": "string",
                    "email": "string",
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string"
                    }
               }
          },
          "resolution_description": "string",
          "resolution_code": "Solved",
          "custom": "2022-02-02 16:36:40.170485",
          "href": "string",
          "href_account_domain": "string",
          "site": {
               "id": 68887,
               "name": "string",
               "location": "string",
               "description": "",
               "time_zone": "string",
               "language": "-1",
               "business_record": null
          },
          "department": {
               "id": 89731,
               "name": "string",
               "description": "",
               "default_assignee_id": 2130747
          },
          "cc": [],
          "custom_fields_values": [
               {
                    "id": 6824902,
                    "custom_field_id": 581688,
                    "name": "string",
                    "value": "string",
                    "attachment": null,
                    "options": "",
                    "type": 1,
                    "type_name": "Text",
                    "entity": null,
                    "user": null
               }
          ],
          "origin": "external",
          "comments": [
               {
                    "id": 13181695,
                    "body": "string",
                    "user": {
                         "id": 1835239,
                         "name": "string",
                         "email": "string",
                         "avatar": {
                              "type": "initials",
                              "initials": "string",
                              "color": "#ffa21e"
                         }
                    },
                    "created_at": "2022-02-02T16:36:36+00:00",
                    "updated_at": "2022-02-02T16:36:36+00:00",
                    "attachments": [],
                    "inline_attachments": [],
                    "shared_attachments": [],
                    "is_private": false,
                    "seen_by": [
                         1835239,
                         1834254
                    ],
                    "isTask": false,
                    "task_info": {},
                    "requester": null,
                    "commenter_id": 11023170,
                    "commenter_type": "Incident"
               }
          ],
          "attachments": [],
          "statistics": [
               {
                    "statistic_type": "last_reassigned",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.097+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.757+00:00\",\"timeSpent\":170,\"timeSpentBH\":170,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"-1\":0.956551476,\"2130747\":329.94227342399995},\"bh_summary\":{\"2130747\":330}}"
               },
               {
                    "statistic_type": "last_state_change",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":391873,\"title\":\"\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.228+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391878,\"title\":\"\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:36:40.464+00:00\",\"timeSpent\":105,\"timeSpentBH\":105,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391874,\"title\":\"\",\"startTime\":\"2022-02-02T16:36:40.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.842+00:00\",\"timeSpent\":65,\"timeSpentBH\":65,\"timeSinceCreation\":265,\"timeSinceCreationBH\":265,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"391873\":160.275407245,\"391878\":105.526080636,\"391874\":64.884937894},\"bh_summary\":{\"391873\":160,\"391878\":105,\"391874\":65}}"
               },
               {
                    "statistic_type": "State Changed",
                    "time": "00:04",
                    "time_elapsed": "00:04",
                    "business_time_elapsed": "00:04",
                    "value": "2022-02-02 16:37:44 UTC"
               },
               {
                    "statistic_type": "assignee_opened_show",
                    "time": "00:02",
                    "time_elapsed": "00:02",
                    "business_time_elapsed": "00:02",
                    "value": "2130747"
               },
               {
                    "statistic_type": "to_first_response",
                    "time": "00:03",
                    "time_elapsed": "00:03",
                    "business_time_elapsed": "00:03",
                    "value": "2022-02-02 16:34:55 UTC"
               },
               {
                    "statistic_type": "to_resolve",
                    "time": "00:06",
                    "time_elapsed": "00:06",
                    "business_time_elapsed": "00:06",
                    "value": "2022-02-02 16:37:44 UTC"
               }
          ],
          "tags": [
               {
                    "name": "string"
               },
               {
                    "name": "string"
               },
               {
                    "name": "string"
               }
          ],
          "incidents": [],
          "changes": [],
          "solutions": [],
          "associated_sla_names": [],
          "is_customer_satisfied": null,
          "customer_satisfaction_response": null,
          "total_time_spent": 0,
          "resolution": "string",
          "resolution_type": "Solved",
          "tasks": [],
          "time_tracks": [],
          "assets": [],
          "mobiles": [],
          "other_assets": [],
          "configuration_items": [],
          "discovery_hardwares": [],
          "purchase_orders": [],
          "request_variables": []
     },
     {
          "id": 11023172,
          "number": 4420,
          "name": "string",
          "description": "string",
          "description_no_html": "string",
          "state": "Resolved",
          "priority": "Low",
          "category": {
               "id": 765171,
               "name": "string",
               "default_tags": "string",
               "parent_id": null,
               "default_assignee_id": 2130747
          },
          "subcategory": {
               "id": 767175,
               "name": "string",
               "default_tags": "string",
               "parent_id": 765171,
               "default_assignee_id": 2130747
          },
          "assignee": {
               "id": 2130747,
               "name": "string",
               "description": "",
               "disabled": false,
               "is_user": false,
               "reports_to": {
                    "id": 2130746,
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string",
                         "klass": "RealGroup"
                    },
                    "name": "string",
                    "href": "string"
               },
               "avatar": {
                    "type": "group",
                    "color": "#dfcd00",
                    "klass": "RealGroup"
               },
               "type": "RealGroup",
               "send_notifications": true,
               "memberships": [
                    {
                         "id": 1781064,
                         "user": "string"
                    },
                    {
                         "id": 1781066,
                         "user": "string"
                    },
                    {
                         "id": 1784446,
                         "user": "string"
                    }
               ]
          },
          "requester": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "created_at": "2022-02-02T16:32:15+00:00",
          "updated_at": "2022-02-02T16:37:44+00:00",
          "due_at": null,
          "sla_violations": [],
          "number_of_comments": 1,
          "user_saw_all_comments": false,
          "is_service_request": false,
          "created_by": {
               "id": 1691640,
               "account_id": 48803,
               "user_id": 1835239,
               "email": "string",
               "name": "string",
               "disabled": false,
               "has_gravatar": false,
               "customer_satisfaction_survey_time": null,
               "avatar": {
                    "type": "initials",
                    "initials": "string",
                    "color": "#ffa21e"
               }
          },
          "resolved_by": {
               "id": 1834254,
               "name": "string",
               "disabled": false,
               "title": "",
               "email": "string",
               "created_at": "2021-07-06T14:27:22+01:00",
               "updated_at": "2022-01-04T09:02:29+00:00",
               "last_login": "2022-01-04T09:02:29+00:00",
               "phone": "",
               "mobile_phone": "",
               "role": {
                    "id": 347139,
                    "name": "Service Agent User",
                    "description": "Similar to an administrator but no access to setup.",
                    "portal": false,
                    "show_my_tasks": false
               },
               "salt": "739a247f1baa49c52c1be1a1e4d8e648f9f87e42",
               "group_ids": [
                    2131456,
                    2130747
               ],
               "available_for_assignment": false,
               "can_be_available_for_assignment": false,
               "custom_fields_values": [],
               "site": {
                    "id": 68887,
                    "name": "string",
                    "location": "string",
                    "description": "",
                    "time_zone": "string",
                    "language": "-1",
                    "business_record": null
               },
               "department": {
                    "id": 89604,
                    "name": "Information Technology",
                    "description": "",
                    "default_assignee_id": 2130747
               },
               "avatar": {
                    "type": "image",
                    "image_class": "avatar_image",
                    "sso_image_class": "",
                    "avatar_url": "string"
               },
               "reports_to": {
                    "group_id": 2130746,
                    "is_user": true,
                    "id": 1833802,
                    "name": "string",
                    "email": "string",
                    "avatar": {
                         "type": "image",
                         "image_class": "avatar_image",
                         "sso_image_class": "",
                         "avatar_url": "string"
                    }
               }
          },
          "resolution_description": "string",
          "resolution_code": "Solved",
          "custom": "2022-02-02 16:36:40.170485",
          "href": "string",
          "href_account_domain": "string",
          "site": {
               "id": 68887,
               "name": "string",
               "location": "string",
               "description": "",
               "time_zone": "string",
               "language": "-1",
               "business_record": null
          },
          "department": {
               "id": 89731,
               "name": "string",
               "description": "",
               "default_assignee_id": 2130747
          },
          "cc": [],
          "custom_fields_values": [
               {
                    "id": 6824902,
                    "custom_field_id": 581688,
                    "name": "string",
                    "value": "string",
                    "attachment": null,
                    "options": "",
                    "type": 1,
                    "type_name": "Text",
                    "entity": null,
                    "user": null
               }
          ],
          "origin": "external",
          "comments": [
               {
                    "id": 13181695,
                    "body": "string",
                    "user": {
                         "id": 1835239,
                         "name": "string",
                         "email": "string",
                         "avatar": {
                              "type": "initials",
                              "initials": "string",
                              "color": "#ffa21e"
                         }
                    },
                    "created_at": "2022-02-02T16:36:36+00:00",
                    "updated_at": "2022-02-02T16:36:36+00:00",
                    "attachments": [],
                    "inline_attachments": [],
                    "shared_attachments": [],
                    "is_private": false,
                    "seen_by": [
                         1835239,
                         1834254
                    ],
                    "isTask": false,
                    "task_info": {},
                    "requester": null,
                    "commenter_id": 11023170,
                    "commenter_type": "Incident"
               }
          ],
          "attachments": [],
          "statistics": [
               {
                    "statistic_type": "last_reassigned",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.097+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":2130747,\"title\":\"string\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.757+00:00\",\"timeSpent\":170,\"timeSpentBH\":170,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"-1\":0.956551476,\"2130747\":329.94227342399995},\"bh_summary\":{\"2130747\":330}}"
               },
               {
                    "statistic_type": "last_state_change",
                    "time": null,
                    "time_elapsed": null,
                    "business_time_elapsed": null,
                    "value": "{\"last_updated\":\"2022-02-02T16:37:44+00:00\",\"history\":[{\"id\":391873,\"title\":\"\",\"startTime\":\"2022-02-02T16:32:15.000+00:00\",\"endTime\":\"2022-02-02T16:34:55.228+00:00\",\"timeSpent\":160,\"timeSpentBH\":160,\"timeSinceCreation\":0,\"timeSinceCreationBH\":0,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391878,\"title\":\"\",\"startTime\":\"2022-02-02T16:34:55.000+00:00\",\"endTime\":\"2022-02-02T16:36:40.464+00:00\",\"timeSpent\":105,\"timeSpentBH\":105,\"timeSinceCreation\":160,\"timeSinceCreationBH\":160,\"isCurrent\":false,\"isDeleted\":false},{\"id\":391874,\"title\":\"\",\"startTime\":\"2022-02-02T16:36:40.000+00:00\",\"endTime\":\"2022-02-02T16:37:44.842+00:00\",\"timeSpent\":65,\"timeSpentBH\":65,\"timeSinceCreation\":265,\"timeSinceCreationBH\":265,\"isCurrent\":false,\"isDeleted\":false}],\"summary\":{\"391873\":160.275407245,\"391878\":105.526080636,\"391874\":64.884937894},\"bh_summary\":{\"391873\":160,\"391878\":105,\"391874\":65}}"
               },
               {
                    "statistic_type": "State Changed",
                    "time": "00:04",
                    "time_elapsed": "00:04",
                    "business_time_elapsed": "00:04",
                    "value": "2022-02-02 16:37:44 UTC"
               },
               {
                    "statistic_type": "assignee_opened_show",
                    "time": "00:02",
                    "time_elapsed": "00:02",
                    "business_time_elapsed": "00:02",
                    "value": "2130747"
               },
               {
                    "statistic_type": "to_first_response",
                    "time": "00:03",
                    "time_elapsed": "00:03",
                    "business_time_elapsed": "00:03",
                    "value": "2022-02-02 16:34:55 UTC"
               },
               {
                    "statistic_type": "to_resolve",
                    "time": "00:06",
                    "time_elapsed": "00:06",
                    "business_time_elapsed": "00:06",
                    "value": "2022-02-02 16:37:44 UTC"
               }
          ],
          "tags": [
               {
                    "name": "string"
               },
               {
                    "name": "string"
               },
               {
                    "name": "string"
               }
          ],
          "incidents": [],
          "changes": [],
          "solutions": [],
          "associated_sla_names": [],
          "is_customer_satisfied": null,
          "customer_satisfaction_response": null,
          "total_time_spent": 0,
          "resolution": "string",
          "resolution_type": "Solved",
          "tasks": [],
          "time_tracks": [],
          "assets": [],
          "mobiles": [],
          "other_assets": [],
          "configuration_items": [],
          "discovery_hardwares": [],
          "purchase_orders": [],
          "request_variables": []
     }
]

Hmmm … sorry … this input data is to overwelming for me … my eyes hurt now … :scream: :stuck_out_tongue_winking_eye:

To show what I meant earlier I created a few rows of sample input data myself …

$InputData = @'
TimeInput
00:03
00:46
00:02
00:12
00:15
00:31
'@ |
    ConvertFrom-Csv |
        Select-Object -Property *,
                @{Name = 'TimeSpan'; Expression = {[TimeSpan]$_.TimeInput}},
                @{Name = 'TotalSeconds'; Expression = {([TimeSpan]$_.TimeInput).TotalSeconds}}

Now when you output this to the console you have the pure input data, the proper TimeSpan type data and - to make it easier to count and calculate - the total seconds:

PS:>$InputData

TimeInput TimeSpan TotalSeconds
--------- -------- ------------
00:03     00:03:00          180
00:46     00:46:00         2760
00:02     00:02:00          120
00:12     00:12:00          720
00:15     00:15:00          900
00:31     00:31:00         1860

Now it is very easy to get some statistics about this data:

$InputData | 
    Measure-Object -Property TotalSeconds -Sum -Minimum -Maximum -Average

What outputs this:

Count    : 6
Average  : 1090
Sum      : 6540
Maximum  : 2760
Minimum  : 120
Property : TotalSeconds

If we’d like to have the output a little human friendlier we can add some additional properties as needed …

$InputData | 
    Measure-Object -Property TotalSeconds -Sum -Minimum -Maximum -Average |
        Select-Object -Property *,
                @{Name = 'AverageTime'; Expression = {New-TimeSpan -Seconds $_.Average}},
                @{Name = 'MinimumTime'; Expression = {New-TimeSpan -Seconds $_.Minimum}},
                @{Name = 'MaximumTime'; Expression = {New-TimeSpan -Seconds $_.Maximum}}

What outputs this:

Count       : 6
Average     : 1090
Sum         : 6540
Maximum     : 2760
Minimum     : 120
Property    : TotalSeconds
AverageTime : 00:18:10
MinimumTime : 00:02:00
MaximumTime : 00:46:00

I hope it helps.

3 Likes

Thank you muchly, it really helped a lot.

The JSON data is pretty full-on, a general data extract contains in the region of 500-1000 separate items, i have to use a view (pictured in the screenshots above) to make sense of the structure :slight_smile:

Your code to calculate time worked perfectly, thank you.

Hi,

The above TimeSpan code is working perfectly when the hours are less than 24 but I have instances where the hours are greater than 24 and in these cases, it doesn’t return a value.

Adding to the above sample input data Olaf added above

$InputData = @'
TimeInput
00:03
00:46
00:02
00:12
00:15
00:31
20:00
23:00
25:00
'@ |
    ConvertFrom-Csv |
        Select-Object -Property *,
                @{Name = 'TimeSpan'; Expression = {[TimeSpan]$_.TimeInput}},
                @{Name = 'TotalSeconds'; Expression = {([TimeSpan]$_.TimeInput).TotalSeconds}}

They all return values expected except for the 25:00 which is empty

image

Is there a way to allow this to count all hours?

Regards,
Jamie

Assumed your input format is ALWAYS “HH:mm” you could change the code to this:

$InputData = @'
TimeInput
00:03
00:46
00:02
00:12
23:59
24:00
24:01
25:00
'@ |
    ConvertFrom-Csv |
        Select-Object -Property *,
                @{Name = 'TimeSpan'; Expression = {New-TimeSpan -Hours (($_.TimeInput -split ':')[0]) -Minutes (($_.TimeInput -split ':')[1])}},
                @{Name = 'TotalSeconds'; Expression = {(New-TimeSpan -Hours (($_.TimeInput -split ':')[0]) -Minutes (($_.TimeInput -split ':')[1])).TotalSeconds}}
1 Like

Hi Olaf,

You are awesome, that works for those over 24 hours perfectly :slight_smile:

Thank you very much!

Regards,
Jamie