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

Welcome Forums General PowerShell Q&A Convert JSON file (Ansible win_update module log) to CSV in Powershell

  • This topic has 1 reply, 2 voices, and was last updated 4 weeks ago by
    Participant
    .
Viewing 1 reply thread
  • Author
    Posts
    • #187678
      Participant
      Topics: 1
      Replies: 0
      Points: 17
      Rank: Member

      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.

    • #187753
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      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.

Viewing 1 reply thread
  • You must be logged in to reply to this topic.