Powershell Iterate through multidimensional array of hashtables to find a match and combine values from both arrays

Question

I need to combine values from 2 JSONs:

If there is a match in alerts IDs, I need to create structure, that will take data from both jsons

Result for a match should look like:

$array = @()
$hashtable = @{}
$hashtable.AlertID (does not matter what JSON is it from)
$hashtable.Tags (from JSON 1)
$hashtable.IncidentName (from JSON2)
$hashtable.IncidentID (from JSON2)
$array += $hashtable

I would prefer if this would be done with c style powershell loop.

c style for loop = for ($x = 0; $x -array.count; $x++)

JSON 1:

[
    {
        "Status":  "Active",
        "IncidentId":  "3",
        "tags":  "SINC0008009",
        "AlertId":  [
                        "da637563185629568182_-638872186",
                        "da637563185631732095_1120592736",
                        "da637563185706412029_-614525914",
                        "da637563185760439486_-276692370",
                        "da637563185856325888_-1949235651",
                        "da637563186785996176_2128073884",
                        "da637563186789897000_1239551047",
                        "da637563186806513555_1512241399",
                        "da637563193194338043_-244132089"
                    ],
        "severity":  "Medium"
    },
    {
        "Status":  "Active",
        "IncidentId":  "4",
        "tags":  "SINC0008008",
        "AlertId":  [
                        "da637643650725801726_1735022501",
                        "da637643650741237104_1473290917",
                        "da637643650748739479_-40211355",
                        "da637643652767933265_-1887823168",
                        "da637643670830160376_-443360743"
                    ],
        "severity":  "Medium"
    },
    {
        "Status":  "Active",
        "IncidentId":  "2",
        "tags":  null,
        "AlertId":  [
                        "caD76232A5-F386-3C5D-94CD-7C82A7F778DC"
                    ],
        "severity":  "Medium"
    },
    {
        "Status":  "Active",
        "IncidentId":  "1",
        "tags":  null,
        "AlertId":  [
                        "ca6534FF45-D62A-3FB7-BD6B-FF5029C553DB"
                    ],
        "severity":  "Medium"
    }
]

JSON 2:

{
  "value": [
    {
      "incidentId": 3,
      "incidentName": "Multi-stage incident involving Initial access & Discovery on one endpoint",
      "status": "Active",
      "severity": "Medium",
      "tags": ["SINC0000001"],
      "comments": [],
      "alerts": [
        {
          "alertId": "da637563185629568182_-638872186",
          "incidentId": 3,
          "description": "A suspicious PowerShell activity was observed on the machine. ",
          "status": "New",
          "severity": "Medium",
          "devices": [
            {
              "deviceDnsName": "xxxxx"
            }
          ],
          "entities": [
            {
              "entityType": "User",
              "accountName": "xxxxxx",
              "userPrincipalName": "[email protected]"
            },
            {
              "entityType": "Process"
            },
            {
              "entityType": "Process",
              "verdict": "Suspicious"
            },
            {
              "entityType": "File"
            }
          ]
        },
        {
          "alertId": "da637563185631732095_1120592736",
          "incidentId": 3,
          "devices": [
            {
              "osPlatform": "Windows10",
              "version": "1909"
            }
          ],
          "entities": [
            {
              "entityType": "User",
              "remediationStatus": "None"
            }
          ]
        }
      ]
    },
    {
      "incidentId": 4,
      "incidentName": "Multi-stage incident involving Initial access & Discovery on one endpoint",
      "status": "Active",
      "severity": "Medium",
      "tags": ["SINC0000002"],
      "comments": [],
      "alerts": [
        {
          "alertId": "da637563185629568182_-638872186",
          "incidentId": 3,
          "description": "A suspicious PowerShell activity was observed on the machine. ",
          "status": "New",
          "severity": "Medium",
          "devices": [
            {
              "deviceDnsName": "xxxxx"
            }
          ],
          "entities": [
            {
              "entityType": "User",
              "accountName": "xxxxxx",
              "userPrincipalName": "[email protected]"
            },
            {
              "entityType": "Process"
            },
            {
              "entityType": "Process",
              "verdict": "Suspicious"
            },
            {
              "entityType": "File"
            }
          ]
        },
        {
          "alertId": "da637563185631732095_1120592736",
          "incidentId": 3,
          "devices": [
            {
              "osPlatform": "Windows10",
              "version": "1909"
            }
          ],
          "entities": [
            {
              "entityType": "User",
              "remediationStatus": "None"
            }
          ]
        }
      ]
    }  
  ]
}

Till now, I was looking into using nested foreach loop to address it but it does not behave like I want. I am looking for for loop as I could use the indexes.

What did you try so far and what do you mean by C style for loop? — Alex_P
– Alex_P, Commented Aug 18, 2021 at 20:05
I was relying on foreach loop. This is the latest snippet and I will need to give it a try in test environment tomorrow. I could not put it in here so I have updated the ticket description. C style for loop is for($i = 0; $i -le $array.count; $i++) I need to know how to do it by using that loops. — aster007
– aster007, Commented Aug 18, 2021 at 20:21
Please update your question to describe the desired outcome and, specifically, what aspect you're having difficulty with. — mklement0
– mklement0, Commented Aug 18, 2021 at 21:31

Theo · Accepted Answer · 2021-08-19 12:05:40Z

Instead of creating an array of Hashtables, I think it's better to create an array of PsCustomObjects, because outputting the result to console/file/json would be a lot easier then.

$json1 = Get-Content -Path 'X:\json1.json' -Raw | ConvertFrom-Json
$json2 = Get-Content -Path 'X:\json2.json' -Raw | ConvertFrom-Json

$result = foreach ($incident in $json1) {
    foreach ($alertId in $incident.AlertId) {
        $json2.value | Where-Object { $_.alerts.alertId -eq $alertId } | ForEach-Object {
            # output an object with the wanted properties
            [PsCustomObject]@{
                AlertID      = $alertId          # from json1
                Tags         = $incident.Tags    # from json1
                IncidentName = $_.incidentName   # from json2
                IncidentID   = $_.incidentId     # from json2
            }
        }
    }
}

# output on screen
$result | Format-Table -AutoSize  # or use Out-GridView

# output to new JSON
$result | ConvertTo-Json

# output to CSV file
$result | Export-Csv -Path 'X:\incidents.csv' -NoTypeInformation

Using your examples, the output to console window is:

AlertID                         Tags        IncidentName                                                              IncidentID
-------                         ----        ------------                                                              ----------
da637563185629568182_-638872186 SINC0008009 Multi-stage incident involving Initial access & Discovery on one endpoint          3
da637563185629568182_-638872186 SINC0008009 Multi-stage incident involving Initial access & Discovery on one endpoint          4
da637563185631732095_1120592736 SINC0008009 Multi-stage incident involving Initial access & Discovery on one endpoint          3
da637563185631732095_1120592736 SINC0008009 Multi-stage incident involving Initial access & Discovery on one endpoint          4

I have accepted your solution in this case + posted a new question. Sorry again.

Collectives™ on Stack Overflow

Powershell Iterate through multidimensional array of hashtables to find a match and combine values from both arrays

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related