This has been driving me insane - I've got some JSON data I'm pulling from an API. In terms of the data I need I'm almost there, however I need to add some conditions to the data before it is inserted into an SQL table (this is missing from the code as it's not ready yet).
$tktHdrs=@{}
$tktHdrs.Add("accept", "application/json")
$tktHdrs.Add("Authorization", "Basic [redacted]")
$getTkts = Invoke-WebRequest -Method 'GET' -Uri "https://[redacted].zendesk.com/api/v2/search.json?query=created>24hours type:ticket" -Headers $tktHdrs
$tktInfo = $getTkts | ConvertFrom-JSON
$test1 = $tktInfo.results
############## THE DATA ABOVE EXISTS IN THE CURRENT SCRIPT. DO NOT COPY. #############################
## Grab a list of Zendesk agents so we can put a name to the id used in the audits API below
$uri_a = "https://[redacted].zendesk.com/api/v2/users?role=agent"
$agentDetails = Invoke-WebRequest -Uri $uri_a -Method Get -Headers $tktHdrs | ConvertFrom-JSON
$agentList = $agentDetails.users
## grab the audit data from Zendesk
ForEach ($test in $test1) {
$ticket_no = $test.id
ForEach ($tktID in $ticket_no) {
$uri = "https://[redacted].zendesk.com/api/v2/tickets/$ticket_no/audits.json"
$auditJSON = Invoke-WebRequest -Uri $uri -Method Get -Headers $tktHdrs
$getAudits = $auditJSON | ConvertFrom-Json
$auditData = $getAudits.audits | Select-Object -Property id, created_at, ticket_id, author_id
$eventData = $getAudits.audits | Select-Object -ExpandProperty events
## Grab the generic information for this audit including the date and time of audit and the ticket ID for use as reference points.
ForEach ($auditItem in $AuditData) {
$dt1 = [PSCustomObject]@{
aid = $auditItem.id
adate = $auditItem.created_at
atkt = $auditItem.ticket_id
aauth = $auditItem.author_id
}
}
## do the same thing for the audit events
ForEach ($eventItem in $eventData) {
$dt2 = [PSCustomObject]@{
eID = $eventItem.id
eType = $eventItem.type
eCmtType = $eventItem.public
}
}
$dt3 = [PSCustomObject]@{
AuditID = $dt1.aid
AuditDate = $dt1.adate
AuditTkt = $dt1.atkt
AuditAgent = $dt1.aauth ## How do I get the agent name in here when the agent ID exists in AgentList variable, and ignore anything not updated by an agent?
EventID = $dt2.eID
EventType = $dt2.eType
}
$dt3 | format-table
## Get this thing completed bucket list:
## 1. If the agent name is blank, discard the result. We are only interested in agent activity.
## 2. If the field_name (eventData) is Subject and the type is Create, the 'type' column in SQL should read 'create'.
## 3. If the type is Comment, and the public value is true, the 'type' column in SQL should read 'public'. If false, the 'type' column in SQL should read 'private'.
## 4. If the 'status' field value is 'solved', the 'type' column in SQL should read 'solved'
## 5. Unsure how we'll handle 'updates' and 'updates with comments' - leave this for later once everything else is sorted.
}
}
One issue I'm having is replacing the Agent / Author ID with the agent/author name. Here is the output of the above, noting instead of the name I appear to be getting an array with a single value. This also appears to replicate the same author id across all results instead of the correct result for that particular audit or event.
AuditID AuditDate AuditTkt AuditAgent EventID EventType CommentType
------- --------- -------- ---------- ------- --------- -----------
18553303748121 2023-05-15T20:31:54Z 39015 @{auditID=18516851787417} 18553258410521 Change
AuditID AuditDate AuditTkt AuditAgent EventID EventType CommentType
------- --------- -------- ---------- ------- --------- -----------
18555877611417 2023-05-15T23:56:53Z 39014 @{auditID=18516851787417} 18555877611801 Notification
Once I get that resolved, I need to be able to manipulate the data to achieve the following:
- If the agent name is blank, discard the result. We are only interested in agent activity.
- If the field_name (eventData) is Subject and the type is Create, the 'type' column in SQL should read 'create'.
- If the type is Comment, and the public value is true, the 'type' column in SQL should read 'public'. If false, the 'type' column in SQL should read 'private'.
- If the 'status' field value is 'solved', the 'type' column in SQL should read 'solved'
Here is example output from the API call for reference, I've tried to truncate it a bit.
{
"audits": [
{
"id": 18515281126169,
"ticket_id": 38907,
"created_at": "2023-05-14T22:01:28Z",
"author_id": 1901380829368,
"metadata": {
"system": {
"message_id": "<>",
"email_id": "",
"raw_email_identifier": "12646503/1971c60f-264c-4739-b6c6-49ff44d4a713.eml",
"json_email_identifier": "12646503/1971c60f-264c-4739-b6c6-49ff44d4a713.json",
"eml_redacted": false
},
"custom": {},
"suspension_type_id": null
},
"events": [
{
"id": 18515281126297,
"type": "Comment",
"author_id": 1901380829368,
"body": "",
"html_body": "",
"public": true,
"attachments": [],
"audit_id": 18515281126169
},
{
"id": 18515281126425,
"type": "Create",
"value": "Further testing, please ignore.",
"field_name": "subject"
},
{
"id": 18515281126553,
"type": "Create",
"value": "1901380829368",
"field_name": "requester_id"
},
{
"id": 18515281126681,
"type": "Create",
"value": "4480835199385",
"field_name": "brand_id"
},
{
"id": 18515281126809,
"type": "Create",
"value": null,
"field_name": "priority"
},
{
"id": 18515281127577,
"type": "Notification",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Notify requester and CCs of received request - From Email",
"id": 5502133832217
},
"rel": "trigger"
}
},
"subject": "Request received",
"body": "",
"recipients": [
1901380829368
]
},
{
"id": 18515281127705,
"type": "Change",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Requester Reply - Move to open, email assignee",
"id": 4480835208345
},
"rel": "trigger"
}
},
"value": "open",
"field_name": "status",
"previous_value": "new"
},
{
"id": 18515281127833,
"type": "Change",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Requester Reply - Move to open, email assignee",
"id": 4480835208345
},
"rel": "trigger"
}
},
"value": "4480835200921",
"field_name": "custom_status_id",
"previous_value": "4480835200793"
},
{
"id": 18515281127961,
"type": "Change",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Send other Issue Types to Customer Support group",
"id": 4546291990681
},
"rel": "trigger"
}
},
"value": "4480945892505",
"field_name": "group_id",
"previous_value": null
},
{
"id": 18515281128089,
"type": "Change",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Apply Normal priority to all email tickets",
"id": 5479117639321
},
"rel": "trigger"
}
},
"value": "normal",
"field_name": "priority",
"previous_value": null
},
{
"id": 18515311236505,
"type": "Create",
"value": "Main Policy - 2 business days",
"field_name": "sla_policy"
}
],
"via": {
"channel": "email",
"source": {
"from": {
"address": "brett@redacted.com.au",
"name": "Brett",
"original_recipients": [
"brett@redacted.com.au",
"info@redacted.com.au"
]
},
"to": {
"name": "redacted",
"address": "info@redacted.com.au"
},
"rel": null
}
}
},
{
"id": 18515281166617,
"ticket_id": 38907,
"created_at": "2023-05-14T22:01:29Z",
"author_id": -1,
"metadata": {
"system": {},
"custom": {},
"parent_audit_id": "18515281126169"
},
"events": [
{
"id": 18515287265561,
"type": "Change",
"previous_value": null,
"value": {
"minutes": 960,
"in_business_hours": true
},
"field_name": "requester_wait_time",
"via": {
"source": {
"rel": "sla_target_change"
},
"current_sla_policy": "Main Policy - 2 business days"
}
}
],
"via": {
"channel": "sla",
"source": {
"from": {},
"to": {},
"rel": null
}
}
},
"events": [
{
"id": 18518673078681,
"type": "Comment",
"author_id": 5277546440729,
"body": "",
"html_body": ",
"public": true,
"attachments": [],
"audit_id": 18518673077401
},
{
"id": 18518673081497,
"type": "Notification",
"via": {
"channel": "rule",
"source": {
"from": {
"deleted": false,
"title": "Notify requester and CCs of comment update",
"id": 4480835207961
},
"rel": "trigger"
}
},
"subject": "Re: {{ticket.title}}",
"body": "Your ticket with reference ID [{{ticket.id}}] has been updated.\n{{ticket.comments_formatted}}",
"recipients": [
1901380829368
]
}
],
"via": {
"channel": "web",
"source": {
"from": {},
"to": {
"name": "Brett",
"address": "brett@redacted.com.au"
},
"rel": null
}
}
},
"events": [
{
"id": 18554695180569,
"type": "Change",
"value": "pending",
"field_name": "status",
"previous_value": "open"
},
{
"id": 18554695180697,
"type": "Change",
"value": "4480835201305",
"field_name": "custom_status_id",
"previous_value": "4480835200921"
}
],
"via": {
"channel": "web",
"source": {
"from": {},
"to": {},
"rel": null
}
}
}
],
"next_page": null,
"previous_page": null,
"count": 4
}
I would also appreciate any comments on how to improve the code overall. Many thanks.
