How to parse and convert JSON Into custom string in Powershell?

Viewed 26

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?

1 Answers

It looks like a CSV-file.

Change the last file in your script:

$json | ConvertFrom-Json | foreach-Object {$_.value.properties.itemsKeyValue}|export-csv test.csv
Related