I have a Powershell object that I pass to a variable $vars using a Invoke-RestMethod.
expand : schema,names
startAt : 0
maxResults : 50
total : 1524
issues : {@{expand=operations,versionedRepresentations,editmeta,changelog,renderedFields; id=3898024; self=https://website.net/rest/api/latest/issue/3898024; key=ABCD-2606; fields=},
@{expand=operations,versionedRepresentations,editmeta,changelog,renderedFields; id=3898020; self=https://website.net/rest/api/latest/issue/3898020; key=ABCD-2605; fields=},
@{expand=operations,versionedRepresentations,editmeta,changelog,renderedFields; id=3898016; self=https://website.net/rest/api/latest/issue/3898016; key=ABCD-2604; fields=},
@{expand=operations,versionedRepresentations,editmeta,changelog,renderedFields; id=3778623; self=https://website.net/rest/api/latest/issue/3778623; key=ABCD-2603; fields=}...}
If I look at vars.issues | select-Object
expand : operations,versionedRepresentations,editmeta,changelog,renderedFields
id : 1100849
self : https://website.net/rest/api/latest/issue/1100849
key : ABCD-2552
fields : @{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=; customfield_18508=;
customfield_18509=; customfield_10905=; lastViewed=; customfield_14300={}; customfield_18504=; customfield_10460=; customfield_18505=; customfield_16202=2020-04-01T13:51:55.000+0000; customfield_18506=; customfield_18507=; priority=;
customfield_10100=; customfield_18500=; customfield_10101=System.Object[]; customfield_18501=; customfield_13612=; customfield_18502=; labels=System.Object[]; customfield_13611=; customfield_18503=; customfield_11700=; customfield_13605=;
customfield_11701=System.Object[]; customfield_13607=; aggregatetimeoriginalestimate=; timeestimate=; versions=System.Object[]; issuelinks=System.Object[]; assignee=; status=; components=System.Object[]; customfield_17003=; customfield_17001=No
worklog defined; customfield_11423=OI-26284624BBE24248B51522FD2B6E2CC0; customfield_10842=; customfield_10449=117754; customfield_10724=; aggregatetimeestimate=; customfield_10604=; customfield_10725=; customfield_10726=; customfield_10728=;
customfield_18200=; customfield_18201=U779690(u779690); creator=; customfield_18202=; customfield_15210=System.Object[]; subtasks=System.Object[]; reporter=; customfield_14001=; customfield_10440=; aggregateprogress=; customfield_10200=;
customfield_18600=; customfield_11412=; customfield_16701=; customfield_11411=; customfield_16700=; customfield_10435=; customfield_11801=; customfield_10838=; progress=; votes=; issuetype=; customfield_17102=; timespent=; project=;
customfield_11000=; customfield_11001=; aggregatetimespent=; customfield_17900=; customfield_16806=; customfield_16805=; customfield_16804=; customfield_14505=; customfield_16809=Mer, Jessie. [ASSOCIATE]: Policy exception
requested from George Skipper as previous policy owner.; customfield_16808=; resolutiondate=2020-04-01T13:51:55.000+0000; customfield_16807=; workratio=-1; watches=; customfield_18300=; customfield_18301=;
created=2020-01-21T10:18:01.000+0000; customfield_14103=; customfield_16401=; customfield_12200=; customfield_16400=; customfield_16802=1_*:*_1_*:*_6143634000_*|*_10102_*:*_1_*:*_0; customfield_18302=; customfield_10420=; customfield_11113=TS001;
customfield_16801=; customfield_18303=; customfield_10300=; customfield_11114=; customfield_10301=; customfield_10412=; customfield_11106=; customfield_11107=; customfield_13805=; updated=2020-11-06T21:30:34.000+0000; timeoriginalestimate=;
description=; customfield_11100=; customfield_17202=; customfield_13401=; customfield_11102=; customfield_11103=; customfield_17603=; customfield_13402=; customfield_13911=; customfield_10006=; customfield_10009=1|i0ef5o:; customfield_10406=;
customfield_10407=; customfield_10803=; summary=WFMS - TSYS; customfield_18400=; customfield_15253=; customfield_15252=; customfield_18000=; customfield_16105=; customfield_16104=; customfield_18406=; customfield_18407=; customfield_10000=;
customfield_18408=; customfield_16109=; customfield_18401=; customfield_10002=; customfield_16504=; customfield_16107=; customfield_10004=; customfield_16106=; environment=; customfield_10911=; duedate=}
I would like to flatten this to get data into a CSV in the format ID, KEY, Field, Field Value
This is where I started
$vars.issues | % {
$data = $_ | Select-Object id, key
$_.Fields | % {
$data | Add-Member -Name $_[0] -Value $_[0] -MemberType NoteProperty
}
return $data
}
The Returned Result
id key @{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=;
customfield_18508=; customfield_18509=; customfield_10905=; lastViewed=; customfield_14300={}; customfield_18504=; customfield_10460=; customfield_18505=; customfield_16202=2020-11-19T22:43:27.000+0000; customfield_18506=;
customfield_18507=; priority=; customfield_10100=; customfield_18500=; customfield_10101=System.Object[]; customfield_18501=; customfield_13612=; customfield_18502=; labels=System.Object[]; customfield_13611=; customfield_18503=;
customfield_11700=; customfield_13605=; customfield_11701=System.Object[]; customfield_13607=; aggregatetimeoriginalestimate=; timeestimate=; versions=System.Object[]; issuelinks=System.Object[]; assignee=; status=;
components=System.Object[]; customfield_17003=; customfield_17001=No worklog defined; customfield_11423=; customfield_10842=; customfield_10449=125832; customfield_10724=2020-11-19; aggregatetimeestimate=; customfield_10604=;
customfield_10725=2020-12-03; customfield_10726=; customfield_10728=; customfield_18200=; customfield_18201=Lstoltz(lstoltz); creator=; customfield_18202=; customfield_15210=System.Object[]; subtasks=System.Object[]; reporter=;
customfield_14001=; customfield_10440=; aggregateprogress=; customfield_10200=; customfield_18600=; customfield_11412=; customfield_16701=; customfield_11411=; customfield_16700=; customfield_10435=; customfield_11801=;
customfield_10838=; progress=; votes=; issuetype=; customfield_17102=; timespent=; project=; customfield_11000=; customfield_11001=; aggregatetimespent=; customfield_17900=; customfield_16806=; customfield_16805=; customfield_16804=;
customfield_14505=; customfield_16809=Random, Marissa [ASSOCIATE]: Request to add 3 new applications to our 2020 scope; customfield_16808=; resolutiondate=; customfield_16807=; workratio=-1; watches=;
customfield_18300=; customfield_18301=; created=2020-11-19T22:43:27.000+0000; customfield_14103=; customfield_16401=; customfield_12200=; customfield_16400=; customfield_16802=; customfield_18302=; customfield_10420=;
customfield_11113=BCBS; customfield_16801=; customfield_18303=; customfield_10300=; customfield_11114=; customfield_10301=; customfield_10412=; customfield_11106=; customfield_11107=; customfield_13805=;
updated=2020-11-19T22:44:04.000+0000; timeoriginalestimate=; description=; customfield_11100=; customfield_17202=; customfield_13401=; customfield_11102=; customfield_11103=; customfield_17603=; customfield_13402=; customfield_13911=;
customfield_10006=; customfield_10009=1|i1g5c8:; customfield_10406=; customfield_10407=; customfield_10803=; summary=TriWorld Application; customfield_18400=; customfield_15253=; customfield_15252=; customfield_18000=;
customfield_16105=; customfield_16104=; customfield_18406=; customfield_18407=; customfield_10000=; customfield_18408=; customfield_16109=; customfield_18401=; customfield_10002=; customfield_16504=; customfield_16107=;
customfield_10004=; customfield_16106=; environment=; customfield_10911=; duedate=}
-- --- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3898024 ABCD-2606 @{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=; customfield...
3898020 ABCD-2605
3898016 ABCD-2604
3778623 ABCD-2603
2673120 ABCD-2597
2480222 ABCD-2596
2141820 ABCD-2595
2141724 ABCD-2594
2007542 ABCD-2593
1754324 ABCD-2592
1734826 ABCD-2591
1734678 ABCD-2590
1734662 ABCD-2589
1727630 ABCD-2588
1727605 ABCD-2587
1727597 ABCD-2586
1727578 ABCD-2585
1191472 ABCD-2584
1100886 ABCD-2583
1100885 ABCD-2582
1100884 ABCD-2581
1100883 ABCD-2580
1100882 ABCD-2579
1100881 ABCD-2578
1100880 ABCD-2577
1100879 ABCD-2576
1100878 ABCD-2575
1100877 ABCD-2574
1100876 ABCD-2573
Output should look something like
id key customfield_11114 status customfield_1142
3898024 ABCD-2606 2020 Open Device1x2
3898025 ABCD-2607 2019 Waiting for customer Device1x3
3898026 ABCD-2608 2019 Open Device1x4
3898027 ABCD-2609 2020 Assigned Device1x5
3898028 ABCD-2610 2002 Closed Device1x6
3898029 ABCD-2611 2020 Closed Device1x7
3898030 ABCD-2612 2020 Open Device1x8
3898031 ABCD-2613 Cancelled Device1x9
This is an excerpt of the JSON
{
"expand": "schema,names",
"startAt": 0,
"maxResults": 3,
"total": 1523,
"issues": [
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id": "1100851",
"self": "https://website.net/rest/api/latest/issue/1100851",
"key": "ABCD-2554",
"fields": "@{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=; customfield_18508=; customfield_18509=; customfield_10905=; lastViewed=; customfield_14300={}; customfield_18504=; customfield_10460=; customfield_18505=; customfield_16202=2020-01-21T15:17:34.000+0000; customfield_18506=; customfield_18507=; priority=; customfield_10100=; customfield_18500=; customfield_10101=System.Object[]; customfield_18501=; customfield_13612=; customfield_18502=; labels=System.Object[]; customfield_13611=; customfield_18503=; customfield_11700=; customfield_13605=; customfield_11701=System.Object[]; customfield_13607=; aggregatetimeoriginalestimate=; timeestimate=; versions=System.Object[]; issuelinks=System.Object[]; assignee=; status=; components=System.Object[]; customfield_17003=; customfield_17001=No worklog defined; customfield_11423=OIaergBD7E231A942A1F; customfield_10842=; customfield_10449=119830; customfield_10724=; aggregatetimeestimate=; customfield_10604=; customfield_10725=; customfield_10726=; customfield_10728=; customfield_18200=; customfield_18201=U679313(u679313); creator=; customfield_18202=2020-01-21T15:17:34.000+0000; customfield_15210=System.Object[]; subtasks=System.Object[]; reporter=; customfield_14001=; customfield_10440=; aggregateprogress=; customfield_10200=; customfield_18600=; customfield_11412=; customfield_16701=; customfield_11411=; customfield_16700=; customfield_10435=; customfield_11801=; customfield_10838=; progress=; votes=; issuetype=; customfield_17102=; timespent=; project=; customfield_11000=; customfield_11001=; aggregatetimespent=; customfield_17900=; customfield_16806=; customfield_16805=; customfield_16804=; customfield_14505=; customfield_16809=Simpson, Homer [ASSOCIATE]: Not in Scope - IVR; customfield_16808=; resolutiondate=2020-01-21T15:17:34.000+0000; customfield_16807=; workratio=-1; watches=; customfield_18300=; customfield_18301=; created=2020-01-21T10:18:02.000+0000; customfield_14103=; customfield_16401=; customfield_12200=; customfield_16400=; customfield_16802=1_*:*_1_*:*_17972000_*|*_10102_*:*_1_*:*_0; customfield_18302=; customfield_10420=; customfield_11113=ACHIV; customfield_16801=; customfield_18303=; customfield_10300=; customfield_11114=; customfield_10301=; customfield_10412=; customfield_11106=; customfield_11107=; customfield_13805=; updated=2020-10-08T15:58:30.000+0000; timeoriginalestimate=; description=; customfield_11100=; customfield_17202=; customfield_13401=; customfield_11102=; customfield_11103=; customfield_17603=; customfield_13402=; customfield_13911=; customfield_10006=; customfield_10009=1|i0ef64:; customfield_10406=; customfield_10407=; customfield_10803=; summary=ACH HCA; customfield_18400=; customfield_15253=; customfield_15252=; customfield_18000=; customfield_16105=; customfield_16104=; customfield_18406=; customfield_18407=; customfield_10000=; customfield_18408=; customfield_16109=; customfield_18401=; customfield_10002=; customfield_16504=; customfield_16107=; customfield_10004=; customfield_16106=; environment=; customfield_10911=; duedate=}"
},
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id": "1100850",
"self": "https://website.net/rest/api/latest/issue/1100850",
"key": "ABCD-2553",
"fields": "@{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=; customfield_18508=; customfield_18509=; customfield_10905=; lastViewed=; customfield_14300={}; customfield_18504=; customfield_10460=; customfield_18505=; customfield_16202=2020-01-21T10:18:02.000+0000; customfield_18506=; customfield_18507=; priority=; customfield_10100=; customfield_18500=; customfield_10101=System.Object[]; customfield_18501=; customfield_13612=; customfield_18502=; labels=System.Object[]; customfield_13611=; customfield_18503=; customfield_11700=; customfield_13605=; customfield_11701=System.Object[]; customfield_13607=; aggregatetimeoriginalestimate=; timeestimate=; versions=System.Object[]; issuelinks=System.Object[]; assignee=; status=; components=System.Object[]; customfield_17003=; customfield_17001=No worklog defined; customfield_11423=RaaarrrgaTNE7SU; customfield_10842=; customfield_10449=117987; customfield_10724=; aggregatetimeestimate=; customfield_10604=; customfield_10725=; customfield_10726=; customfield_10728=; customfield_18200=; customfield_18201=U779690(u779690); creator=; customfield_18202=2020-08-13T18:03:16.000+0000; customfield_15210=System.Object[]; subtasks=System.Object[]; reporter=; customfield_14001=; customfield_10440=; aggregateprogress=; customfield_10200=; customfield_18600=; customfield_11412=; customfield_16701=; customfield_11411=; customfield_16700=; customfield_10435=; customfield_11801=; customfield_10838=; progress=; votes=; issuetype=; customfield_17102=; timespent=; project=; customfield_11000=; customfield_11001=; aggregatetimespent=; customfield_17900=; customfield_16806=; customfield_16805=; customfield_16804=; customfield_14505=; customfield_16809=Bill, Wild [ASSOCIATE]: According to ISL site, testing was completed by Joe and we are waiting on the report. ; customfield_16808=; resolutiondate=; customfield_16807=; workratio=-1; watches=; customfield_18300=; customfield_18301=; created=2020-01-21T10:18:02.000+0000; customfield_14103=; customfield_16401=; customfield_12200=; customfield_16400=; customfield_16802=; customfield_18302=; customfield_10420=; customfield_11113=NYDTF; customfield_16801=; customfield_18303=; customfield_10300=; customfield_11114=; customfield_10301=; customfield_10412=; customfield_11106=; customfield_11107=; customfield_13805=; updated=2020-11-17T15:42:36.000+0000; timeoriginalestimate=; description=; customfield_11100=; customfield_17202=; customfield_13401=; customfield_11102=; customfield_11103=; customfield_17603=; customfield_13402=; customfield_13911=; customfield_10006=; customfield_10009=1|i0ef5w:; customfield_10406=; customfield_10407=; customfield_10803=; summary=Flat Data Goes Somewhere (FDGS) (VID 15489) NY DTF Electronic Payment; customfield_18400=; customfield_15253=; customfield_15252=; customfield_18000=; customfield_16105=; customfield_16104=; customfield_18406=; customfield_18407=; customfield_10000=; customfield_18408=; customfield_16109=; customfield_18401=; customfield_10002=; customfield_16504=; customfield_16107=; customfield_10004=; customfield_16106=; environment=; customfield_10911=; duedate=}"
},
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id": "1100849",
"self": "https://website.net/rest/api/latest/issue/1100849",
"key": "ABCD-2552",
"fields": "@{customfield_15000=; customfield_17700=; fixVersions=System.Object[]; customfield_18511=; resolution=; customfield_15405=; customfield_15526=; customfield_17702=; customfield_11203=; customfield_17701=; customfield_10503=; customfield_18508=; customfield_18509=; customfield_10905=; lastViewed=; customfield_14300={}; customfield_18504=; customfield_10460=; customfield_18505=; customfield_16202=2020-04-01T13:51:55.000+0000; customfield_18506=; customfield_18507=; priority=; customfield_10100=; customfield_18500=; customfield_10101=System.Object[]; customfield_18501=; customfield_13612=; customfield_18502=; labels=System.Object[]; customfield_13611=; customfield_18503=; customfield_11700=; customfield_13605=; customfield_11701=System.Object[]; customfield_13607=; aggregatetimeoriginalestimate=; timeestimate=; versions=System.Object[]; issuelinks=System.Object[]; assignee=; status=; components=System.Object[]; customfield_17003=; customfield_17001=No worklog defined; customfield_11423=eeeerrgggaerBE2424aege522FD2B6E2CC0; customfield_10842=; customfield_10449=117754; customfield_10724=; aggregatetimeestimate=; customfield_10604=; customfield_10725=; customfield_10726=; customfield_10728=; customfield_18200=; customfield_18201=U779690(u779690); creator=; customfield_18202=; customfield_15210=System.Object[]; subtasks=System.Object[]; reporter=; customfield_14001=; customfield_10440=; aggregateprogress=; customfield_10200=; customfield_18600=; customfield_11412=; customfield_16701=; customfield_11411=; customfield_16700=; customfield_10435=; customfield_11801=; customfield_10838=; progress=; votes=; issuetype=; customfield_17102=; timespent=; project=; customfield_11000=; customfield_11001=; aggregatetimespent=; customfield_17900=; customfield_16806=; customfield_16805=; customfield_16804=; customfield_14505=; customfield_16809=Mouse, Mickey. [ASSOCIATE]: Policy exception requested previous policy exception has expired. ; customfield_16808=; resolutiondate=2020-04-01T13:51:55.000+0000; customfield_16807=; workratio=-1; watches=; customfield_18300=; customfield_18301=; created=2020-01-21T10:18:01.000+0000; customfield_14103=; customfield_16401=; customfield_12200=; customfield_16400=; customfield_16802=1_*:*_1_*:*_6143634000_*|*_10102_*:*_1_*:*_0; customfield_18302=; customfield_10420=; customfield_11113=TS001; customfield_16801=; customfield_18303=; customfield_10300=; customfield_11114=; customfield_10301=; customfield_10412=; customfield_11106=; customfield_11107=; customfield_13805=; updated=2020-11-06T21:30:34.000+0000; timeoriginalestimate=; description=; customfield_11100=; customfield_17202=; customfield_13401=; customfield_11102=; customfield_11103=; customfield_17603=; customfield_13402=; customfield_13911=; customfield_10006=; customfield_10009=1|i0ef5o:; customfield_10406=; customfield_10407=; customfield_10803=; summary=MS - TSYS; customfield_18400=; customfield_15253=; customfield_15252=; customfield_18000=; customfield_16105=; customfield_16104=; customfield_18406=; customfield_18407=; customfield_10000=; customfield_18408=; customfield_16109=; customfield_18401=; customfield_10002=; customfield_16504=; customfield_16107=; customfield_10004=; customfield_16106=; environment=; customfield_10911=; duedate=}"
}
]
}
I am fairly new to powershell so any assistance would be helpful. I have tried a few different iterations of opening the objects to get the data sources but cannot quite figure out the forEach loop requirements.
I was able to produce the results I wanted in CSV for using the following code. If there is a more efficient way I am glad to learn it.
$obj = Invoke-RestMethod -uri $url$startAt -Headers $Headers
$obj.issues | % {
$data = $_ | Select-Object ID,Key
$_.Fields | % {
$data | Add-Member -Name "CommonID" -Value $_.customfield_11423 -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "Year" -Value $_.customfield_11114.Value -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "Status" -Value $_.Status.name -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "Summary" -Value $_.Summary -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "Plan" -Value $_.customfield_10449 -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "EndDate" -Value $_.customfield_10725 -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "StartDate" -Value $_.customfield_10724 -MemberType NoteProperty
}
$_.Fields | % {
$data | Add-Member -Name "App" -Value $_.customfield_11113 -MemberType NoteProperty
}
return $data
}