Convert JSON file (Ansible win_update module log) to CSV in Powershell

Hi All,

First time on this forum.

I would like to ask some help to convert a json log file from Ansible to csv format using Powershell.

I tried many things I was advised but no satisfactory result so far.

See bellow an example of the JSON log file style (very nested):

2019-10-09 18:34:18Z WUA is available in current logon process, running natively
2019-10-09 18:34:18Z Creating Windows Update session…
2019-10-09 18:34:18Z Create Windows Update searcher…
2019-10-09 18:34:18Z Setting the Windows Update Agent source catalog…
2019-10-09 18:34:18Z Requested search source is ‘default’
2019-10-09 18:34:18Z Search source set to ‘default’ (ServerSelection = 0)
2019-10-09 18:34:18Z Searching for updates to install
2019-10-09 18:34:52Z Found 4 updates
2019-10-09 18:34:52Z Creating update collection…
2019-10-09 18:34:52Z Adding update d60c506b-85dc-4bd8-a308-f2f0658aca6a - Windows Malicious Software Removal Tool x64 - October 2019 (KB890830)
2019-10-09 18:34:52Z Adding update 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8 - 2019-10 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4519998)
2019-10-09 18:34:52Z Adding update 83d7bc64-ff39-4073-9d77-02102226aff6 - 2019-10 Servicing Stack Update for Windows Server 2016 for x64-based Systems (KB4521858)
2019-10-09 18:34:52Z Adding update 239c2d1f-9b9f-4dc9-ae35-eed16af25694 - Security Intelligence Update for Windows Defender Antivirus - KB2267602 (Version 1.303.1318.0)
2019-10-09 18:34:52Z Calculating pre-install reboot requirement…
2019-10-09 18:34:52Z No reboot is pending…
2019-10-09 18:34:52Z Downloading updates…
2019-10-09 18:34:52Z Creating downloader object…
2019-10-09 18:34:52Z Creating download collection…
2019-10-09 18:34:52Z Adding update (1 of 4) d60c506b-85dc-4bd8-a308-f2f0658aca6a
2019-10-09 18:34:52Z Downloading (1 of 4) d60c506b-85dc-4bd8-a308-f2f0658aca6a
2019-10-09 18:34:53Z Download result code for (1 of 4) d60c506b-85dc-4bd8-a308-f2f0658aca6a = 2
2019-10-09 18:34:53Z Creating downloader object…
2019-10-09 18:34:53Z Creating download collection…
2019-10-09 18:34:53Z Adding update (2 of 4) 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8
2019-10-09 18:34:53Z Downloading (2 of 4) 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8
2019-10-09 18:43:01Z Download result code for (2 of 4) 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8 = 2
2019-10-09 18:43:01Z Creating downloader object…
2019-10-09 18:43:01Z Creating download collection…
2019-10-09 18:43:01Z Adding update (3 of 4) 83d7bc64-ff39-4073-9d77-02102226aff6
2019-10-09 18:43:01Z Downloading (3 of 4) 83d7bc64-ff39-4073-9d77-02102226aff6
2019-10-09 18:43:07Z Download result code for (3 of 4) 83d7bc64-ff39-4073-9d77-02102226aff6 = 2
2019-10-09 18:43:07Z Update (4 of 4) 239c2d1f-9b9f-4dc9-ae35-eed16af25694 already downloaded, skipping…
2019-10-09 18:43:07Z Installing updates…
2019-10-09 18:43:07Z Creating installer object…
2019-10-09 18:43:07Z Creating install collection…
2019-10-09 18:43:07Z Adding update d60c506b-85dc-4bd8-a308-f2f0658aca6a
2019-10-09 18:43:07Z Adding update 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8
2019-10-09 18:43:07Z Adding update 83d7bc64-ff39-4073-9d77-02102226aff6
2019-10-09 18:43:07Z Adding update 239c2d1f-9b9f-4dc9-ae35-eed16af25694
2019-10-09 18:50:59Z Update (1 of 4) d60c506b-85dc-4bd8-a308-f2f0658aca6a succeeded
2019-10-09 18:50:59Z Update (2 of 4) 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8 succeeded
2019-10-09 18:50:59Z Update (3 of 4) 83d7bc64-ff39-4073-9d77-02102226aff6 succeeded
2019-10-09 18:50:59Z Update (4 of 4) 239c2d1f-9b9f-4dc9-ae35-eed16af25694 succeeded
2019-10-09 18:50:59Z Performing post-install reboot requirement check…
2019-10-09 18:50:59Z Return value:
{
“updates”: {
“239c2d1f-9b9f-4dc9-ae35-eed16af25694”: {
“id”: “239c2d1f-9b9f-4dc9-ae35-eed16af25694”,
“title”: “Security Intelligence Update for Windows Defender Antivirus - KB2267602 (Version 1.303.1318.0)”,
“categories”: [
“Definition Updates”,
“Windows Defender”
],
“kb”: [
“2267602”
],
“installed”: true
},
“83d7bc64-ff39-4073-9d77-02102226aff6”: {
“id”: “83d7bc64-ff39-4073-9d77-02102226aff6”,
“title”: “2019-10 Servicing Stack Update for Windows Server 2016 for x64-based Systems (KB4521858)”,
“categories”: [
“Security Updates”,
“Windows Server 2016”
],
“kb”: [
“4521858”
],
“installed”: true
},
“0c7eb702-e48b-48da-8ef8-e984aa6cb0b8”: {
“id”: “0c7eb702-e48b-48da-8ef8-e984aa6cb0b8”,
“title”: “2019-10 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4519998)”,
“categories”: [
“Security Updates”,
“Windows Server 2016”
],
“kb”: [
“4519998”
],
“installed”: true
},
“d60c506b-85dc-4bd8-a308-f2f0658aca6a”: {
“id”: “d60c506b-85dc-4bd8-a308-f2f0658aca6a”,
“title”: “Windows Malicious Software Removal Tool x64 - October 2019 (KB890830)”,
“categories”: [
“Update Rollups”,
“Windows Server 2016”
],
“kb”: [
“890830”
],
“installed”: true
}
},
“failed_update_count”: 0,
“found_update_count”: 4,
“changed”: true,
“reboot_required”: true,
“installed_update_count”: 4,
“filtered_updates”: {

}
}
2019-10-09 18:50:59Z Native job completed with output:
Name Value


updates {239c2d1f-9b9f-4dc9-ae35-eed16af25694, 83d7bc64-ff39-4073-9d77-02102226aff6, 0c7eb702-e48b-48da-8ef8-e984aa6cb0b8, d60c506b-85dc-4bd8-a308-f2f0658aca6a}
failed_update_count 0
found_update_count 4
changed True
reboot_required True
installed_update_count 4
filtered_updates {}

===================================================================================

I would like to have a CSV with headers being:
Date, KB, ID, Title, Category, Product, Reboot is required, Installed.
Those would be the columns title of the CSV.

For example:

Date,KB,ID,Title,Category,Product,Reboot_required,Installed
2019-10-09 18:50:59Z,2267602,239c2d1f-9b9f-4dc9-ae35-eed16af25694,“Windows Malicious Software Removal Tool x64 - October 2019 (KB890830)”,“Definition Updates”,“Windows Defender”,True,True

Maybe it is not possible to do that, but let me know if that´s the case.

If anyone can help me, I will be really thankful.

Thanks in advance.

As far as I know, there is no direct support for converting the JSON to CSV. But you can parse to hashtable or data table and then convert it to CSV.

Thank you.