How do I get this nested array into a csv (or at least a flat json list)?

Questions
,
1

I have hit my wits end (well half that), trying to turn this API output into a spreadsheet. The problem is it includes an array that returns values that aren’t consistent, and it’s buried in indexes that I just can’t understand how to access.

I have no problem pulling the items that are on the same level. It is when it gets to the custom_fields part that I get totally lost. The best I can figure out how to do is to get them out into a list. But when I try and merge them in again I only get the first object. What I need them to be is from:

    "custom_fields": [
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },

to

"custom_fields_6": "BitlockerKey"
"custom_field_9": "rustdeskid"

The things that make it a challenge is that the index of custom_fields (length or order) doesn’t match from object to object. It might be 3,6,9 one time then 9,3,6 the next, or 3 only, or all null. What is certain is the field:n is always an int and always key to the same value type. All the other keys/values on the same level I can split out and sort.

I am sure this is easy. But I don’t know javascript at all, and I just need to get this bottleneck broken for my MVP of a spreadsheet with a few picked key/values from this object.

There aren’t any errors, but Here is what I get and what I want:

image
image950×611 156 KB

Please share your workflow

Share the output returned by the last node

it’s a file, but the screenshot shows what I get and what I want.

Information on your n8n setup

n8n Version - 1.8.2

  • Database : SQLite:
  • n8n EXECUTIONS_PROCESS setting (default: own, main): not sure
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker
  • Operating system: Debian12

Here is the input content that has been sanitised.

[
  {
    "agent_id": "9999999999UQpesNop",
    "alert_template": null,
    "hostname": "BP5NXQ1",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": false,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:34:32.620971Z",
    "boot_time": 1696265833,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "dwight",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 10 Pro, 64 bit v21H2 (build 19044.3086)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz"
    ],
    "graphics": "Intel(R) HD Graphics",
    "local_ips": "192.168.1.106",
    "make_model": "Dell Inc. OptiPlex 390",
    "physical_disks": [
      "ST3250312AS ATA Device 233GB IDE",
      "SanDisk SSD PLUS 240GB ATA Device 224GB IDE"
    ],
    "custom_fields": [
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      }
    ],
    "serial_number": "XQ1BP5N"
  },
  {
    "agent_id": "9999999999eSuxhUkI",
    "alert_template": null,
    "hostname": "ABC-ACCOUNTING",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": false,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:35:09.861123Z",
    "boot_time": 1696961336,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "jim",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 10 Pro, 64 bit v21H2 (build 19044.3086)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "Intel(R) Xeon(R) E-2236 CPU @ 3.40GHz"
    ],
    "graphics": "Microsoft Basic Display Adapter",
    "local_ips": "192.168.1.194",
    "make_model": "QEMU Standard PC (Q35 + ICH9, 2009)",
    "physical_disks": [
      "Red Hat VirtIO SCSI Disk Device 55GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      },
      {
        "id": 115,
        "field": 3,
        "agent": 129,
        "value": "ScreenConnectGUID"
      },
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      }
    ],
    "serial_number": ""
  },
  {
    "agent_id": "9999999999lXqEmgUL",
    "alert_template": null,
    "hostname": "D58003EF70970",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": false,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:35:11.429025Z",
    "boot_time": 1697052084,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "michael",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 11 Pro, 64 bit v21H2 (build 22000.2416)",
    "public_ip": "17.7.5.14",
    "cpu_model": [
      "AMD Ryzen 7 5800H with Radeon Graphics "
    ],
    "graphics": "AMD Radeon(TM) Graphics",
    "local_ips": "192.168.1.86",
    "make_model": "AZW SER",
    "physical_disks": [
      "KINGSTON OM8SEP4512N-A0 477GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 115,
        "field": 3,
        "agent": 129,
        "value": "ScreenConnectGUID"
      },
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      }
    ],
    "serial_number": "F70970D58003E"
  },
  {
    "agent_id": "MbVdRpuAQtomakIDdntAJtWxoBuLBCgwiEUSLvWT",
    "alert_template": null,
    "hostname": "24CVP52",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": false,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:35:07.323549Z",
    "boot_time": 1695654195,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "oscar",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 10 Pro, 64 bit v21H2 (build 19044.3086)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "Intel(R) Core(TM) i5-4590 CPU @ 3.30GHz"
    ],
    "graphics": "Intel(R) HD Graphics 4600",
    "local_ips": "192.168.1.118",
    "make_model": "Dell Inc. OptiPlex 9020",
    "physical_disks": [
      "ADATA SU800 477GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 115,
        "field": 3,
        "agent": 129,
        "value": "ScreenConnectGUID"
      },
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      }
    ],
    "serial_number": "24CVP52"
  },
  {
    "agent_id": "nKjiqUbWBXDwliQUtfGkqkrCmvggMkcAehrJrlxV",
    "alert_template": null,
    "hostname": "ABCTV",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": true,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:35:01.438284Z",
    "boot_time": 1697247629,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "angela",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 10 Pro, 64 bit v22H2 (build 19045.3324)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "Intel(R) Core(TM) i5-7260U CPU @ 2.20GHz"
    ],
    "graphics": "Intel(R) Iris(R) Plus Graphics 640",
    "local_ips": "192.168.1.140",
    "make_model": " ",
    "physical_disks": [
      "KINGSTON RBUSNS8154P3256GJ1 238GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 115,
        "field": 3,
        "agent": 129,
        "value": "ScreenConnectGUID"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      }
    ],
    "serial_number": " "
  },
  {
    "agent_id": "hbCVMgRXfcraNtOzTaTxkXDcjmsynTPhIMxmwJZQ",
    "alert_template": null,
    "hostname": "ABC-QUICKBOOKS",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": true,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:34:41.255283Z",
    "boot_time": 1695596758,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": null,
    "italic": true,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows Server 2022 Standard, 64 bit v21H2 (build 20348.1726)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "Intel(R) Xeon(R) E-2236 CPU @ 3.40GHz"
    ],
    "graphics": "Red Hat VirtIO GPU DOD controller",
    "local_ips": "192.168.1.10",
    "make_model": "QEMU Standard PC (Q35 + ICH9, 2009)",
    "physical_disks": [
      "Red Hat VirtIO SCSI Disk Device 64GB SCSI",
      "Red Hat VirtIO SCSI Disk Device 64GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 1,
        "field": 6,
        "agent": 129,
        "value": "BitlockerKey"
      },
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      }
    ],
    "serial_number": ""
  },
  {
    "agent_id": "lleCdRfHWUQGuQAgyTxxsMznqpPooSQVLVYaHmTs",
    "alert_template": null,
    "hostname": "D58003EF70677",
    "site_name": "Main",
    "client_name": "Dunder Mifflin, Inc.",
    "monitoring_type": "workstation",
    "description": "",
    "needs_reboot": false,
    "pending_actions_count": 0,
    "status": "online",
    "overdue_text_alert": false,
    "overdue_email_alert": false,
    "overdue_dashboard_alert": false,
    "last_seen": "2023-10-17T05:35:10.402843Z",
    "boot_time": 1696127127,
    "checks": {
      "total": 5,
      "passing": 5,
      "failing": 0,
      "warning": 0,
      "info": 0,
      "has_failing_checks": false
    },
    "maintenance_mode": false,
    "logged_username": "pam",
    "italic": false,
    "block_policy_inheritance": false,
    "plat": "windows",
    "goarch": "amd64",
    "has_patches_pending": false,
    "version": "2.5.0",
    "operating_system": "Windows 10 Pro, 64 bit v22H2 (build 19045.3448)",
    "public_ip": "17.99.18.5",
    "cpu_model": [
      "AMD Ryzen 7 5800H with Radeon Graphics "
    ],
    "graphics": "AMD Radeon(TM) Graphics",
    "local_ips": "192.168.1.112",
    "make_model": "AZW SER",
    "physical_disks": [
      "KINGSTON OM8SEP4512N-A0 477GB SCSI"
    ],
    "custom_fields": [
      {
        "id": 116,
        "field": 9,
        "agent": 129,
        "value": "rustdeskid"
      },
      {
        "id": 115,
        "field": 3,
        "agent": 129,
        "value": "ScreenConnectGUID"
      },
      {
        "id": 117,
        "field": 10,
        "agent": 129,
        "value": "rustdeskpwd"
      }
    ],
    "serial_number": "3EF7D58000677"
  }
]
3

Indeed, this just requires some javascript.

Here’s the javascript code (from ChatGPT so that you can explore further):
https://chat.openai.com/share/3418f230-5845-48e6-b49a-f87d0d52df32

And here’s the code when used in a workflow:

3 Likes
4

That’s such a simple bit of code. I love that you got a working result from ChatGPT. I know a little golang and ruby, but never learned any javascript. I didn’t even know what to search for, lol. Thanks for including the query you used. You taught me how to fish, lol. Big thanks, and greetings from Alaska!

1 Like
5

Can I trouble you to help me add some additional fields? The code works great for all the custom fields, but I can’t figure out where in that command I need to add in some regular key:values. I have been striking out with chatGPT… it is reverting to using hardcoded IDs in the custom fields.

EDIT: I amended the chatGPT discussion to specify an n8n node. Whammy, it worked. Thank you so much!
https://chat.openai.com/share/e8853f8e-2eed-4491-abfe-7dea487a5af9

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.