Greetings fellow Earthlings,

I'm looking for a little guidance on the current challenge I'm faced with. I'm somewhat of a intermediate user of PowerShell, however, there are some gaps in my knowledge. I'm not looking for a complete solution, just some guidance. I want to be very clear of that because it appears there are individuals who want someone to code everything for them, or are confronted by assumption, that's not the case.

My desired result is to get missing patch information for each running instance in an AWS account and place that data into an Excel workbook, with each instance having its own worksheet that includes headers which match the the keys and corresponding columns that display the values for each JSON document. I've tried a number of ways to accomplish this and I'm having some challenges. Any help/guidance would be greatly appreciated. Again, I'm not looking for someone to code everything for me. I'm trying to learn HOW to fish, not WHO can fish for me --although in the entrepreneurial world, finding the WHO is the key to success.

Code I have thus far:

$instance_source = "C:\missing-patches\instance_source.txt"

# Get running instanceIds and write them to a source text file.
aws ec2 describe-instances --profile $PROFILE --query "Reservations[*].Instances[*].{InstanceId: InstanceId}" --filters "Name=instance-state-name,Values=running" --output text | Out-File $instance_source

# Get content from source text file, then query each AWS instance for missing patches, then write to file
foreach($instance_id in Get-Content $instance_source){
    $writeToJsonFile = "C:\missing-patches\running-instances\$instance_id.json"
    aws --profile $PROFILE ssm describe-instance-patches --instance-id  $instance_id --filters Key=State,Values=Missing --output json | Out-File $writeToJsonFile
}

# Convert each JSON in the directory and import to an Excel workbook/worksheet
$pathToJsonFiles = Get-ChildItem "C:\missing-patches\running-instances\"

foreach($json_file in $pathToJsonFiles){
    Get-Content $pathToJsonFiles -Raw | ConvertFrom-Json | Export-Excel .\missing-patches.xlsx -WorksheetName $json_file.BaseName
}

Remove-Item $instance_source

Contents of one valid JSON file:

{
    "Patches": [
        {
            "Title": "2020-10 Cumulative Update for .NET Framework 4.8 for Windows Server 2016 for x64 (KB4578969)",
            "KBId": "KB4578969",
            "Classification": "SecurityUpdates",
            "Severity": "Important",
            "State": "Missing",
            "InstalledTime": "1969-12-31T19:00:00-05:00"
        },
        {
            "Title": "Security Update for Windows Server 2016 for x64-based Systems (KB4535680)",
            "KBId": "KB4535680",
            "Classification": "SecurityUpdates",
            "Severity": "Critical",
            "State": "Missing",
            "InstalledTime": "1969-12-31T19:00:00-05:00"
        },
        {
            "Title": "2021-09 Servicing Stack Update for Windows Server 2016 for x64-based Systems (KB5005698)",
            "KBId": "KB5005698",
            "Classification": "SecurityUpdates",
            "Severity": "Critical",
            "State": "Missing",
            "InstalledTime": "1969-12-31T19:00:00-05:00"
        }
    ]
}

Results in the .xlsx are displaying the objects rather than the values of the object, a string:

"Patches"
"System.Object[]"
"System.Object[]"
"System.Object[]"

I know there is more to this so please exercise some patience and understanding that I do not know everything about coding/PowerShell; I'm learning through experience, not theory. Be nice. Some responders assume you should know everything or berate you because you don't. Thanks in advance!

CodePudding user response：

You need to dig 1 level deeper and output the contents of the Patches property, not the parent object that owns it:

Get-Content $pathToJsonFiles -Raw | ConvertFrom-Json |ForEach-Object -MemberName Patches | Export-Excel .\missing-patches.xlsx -WorksheetName $json_file.BaseName

The call to ForEach-Object Patches will extract just the value(s) of that property from the input object (similar to Select-Object -ExpandProperty)