I have a JSON file, which needs to be parsed and converted to a custom string. I managed to iterate through the JSON file and find key value pairs that I need, but I'm struggling to change it to desirable format.
$json =
'{
"value": [
{
"id": "/subscriptions/e65c8621-9ded-4164-878c-654bbd1ed607/resourceGroups/Sentinel-Automation-Mainstream/providers/Microsoft.OperationalInsights/workspaces/SentinelAutomationMainstream/providers/Microsoft.SecurityInsights/Watchlists/DomainControllerList/WatchlistItems/f55754ec-9ef8-4a3a-a9f1-3356ac686ae4",
"name": "f55754ec-9ef8-4a3a-a9f1-3356ac686ae4",
"etag": "\"2601511d-0000-0d00-0000-6326d31b0000\"",
"type": "Microsoft.SecurityInsights/Watchlists/WatchlistItems",
"systemData": {
"createdAt": "2022-09-18T08:13:06.7222366Z",
"createdBy": "87275b2a-c86e-410b-b69b-1cf07390846f",
"createdByType": "Application",
"lastModifiedAt": "2022-09-18T08:13:06.7222366Z",
"lastModifiedBy": "87275b2a-c86e-410b-b69b-1cf07390846f",
"lastModifiedByType": "Application"
},
"properties": {
"watchlistItemType": "watchlist-item",
"watchlistItemId": "f55754ec-9ef8-4a3a-a9f1-3356ac686ae4",
"tenantId": "4fb568d9-5b2c-4cf4-92d4-41125b3c2202",
"isDeleted": false,
"created": "2022-09-18T10:13:06.7222366+02:00",
"updated": "2022-09-18T10:13:06.7222366+02:00",
"createdBy": {
"objectId": "cc822887-2f84-420d-843a-2e48308c67a8",
"name": "87275b2a-c86e-410b-b69b-1cf07390846f"
},
"updatedBy": {
"objectId": "cc822887-2f84-420d-843a-2e48308c67a8",
"name": "87275b2a-c86e-410b-b69b-1cf07390846f"
},
"itemsKeyValue": {
"DcName": "MKDC01",
"DcHostName": "MKDC01.domain.local",
"IpAddress": "10.20.30.1",
"DcType": "GC",
"Site": "Prague"
},
"entityMapping": {}
}
},
{
"id": "/subscriptions/e65c8621-9ded-4164-878c-654bbd1ed607/resourceGroups/Sentinel-Automation-Mainstream/providers/Microsoft.OperationalInsights/workspaces/SentinelAutomationMainstream/providers/Microsoft.SecurityInsights/Watchlists/DomainControllerList/WatchlistItems/b8b714a0-93f7-440a-acf9-fa3855b73599",
"name": "b8b714a0-93f7-440a-acf9-fa3855b73599",
"etag": "\"2601521d-0000-0d00-0000-6326d31b0000\"",
"type": "Microsoft.SecurityInsights/Watchlists/WatchlistItems",
"systemData": {
"createdAt": "2022-09-18T08:13:06.7222366Z",
"createdBy": "87275b2a-c86e-410b-b69b-1cf07390846f",
"createdByType": "Application",
"lastModifiedAt": "2022-09-18T08:13:06.7222366Z",
"lastModifiedBy": "87275b2a-c86e-410b-b69b-1cf07390846f",
"lastModifiedByType": "Application"
},
"properties": {
"watchlistItemType": "watchlist-item",
"watchlistItemId": "b8b714a0-93f7-440a-acf9-fa3855b73599",
"tenantId": "4fb568d9-5b2c-4cf4-92d4-41125b3c2202",
"isDeleted": false,
"created": "2022-09-18T10:13:06.7222366+02:00",
"updated": "2022-09-18T10:13:06.7222366+02:00",
"createdBy": {
"objectId": "cc822887-2f84-420d-843a-2e48308c67a8",
"name": "87275b2a-c86e-410b-b69b-1cf07390846f"
},
"updatedBy": {
"objectId": "cc822887-2f84-420d-843a-2e48308c67a8",
"name": "87275b2a-c86e-410b-b69b-1cf07390846f"
},
"itemsKeyValue": {
"DcName": "MKDC02",
"DcHostName": "MKDC02.domain.local",
"IpAddress": "10.20.30.2",
"DcType": "GC",
"Site": "NewYork"
},
"entityMapping": {}
}
}
]
}'
$json | ConvertFrom-Json | foreach-Object {$_.value.properties.itemsKeyValue} | Out-File ("test2.json")
I do get output like this
DcName : MKDC01
DcHostName : MKDC01.domain.local
IpAddress : 10.20.30.1
DcType : GC
Site : Prague
DcName : MKDC02
DcHostName : MKDC02.domain.local
IpAddress : 10.20.30.2
DcType : GC
Site : NewYork
Unfortunately this is not sufficient for me as I need it in this format
DcName,DcHostName,IpAddress,DcType,Site\r\nMKDC01,MKDC01.domain.local,10.20.30.1,GC,Prague\r\nMKDC02,MKDC02.domain.local,10.20.30.2,GC,NewYork\r\n
Anyone knows an elegant solutions as "keys" in "itemsKeyValue" can change in the future?