Ansible build report from json

Question

A PyATS Ansible playbook output is saved to a json file:

- name: Export variable to file
  copy:
    content: "{{ output }}"
    dest: "report.json"

The json content is as follows:

{
  "results": [
    {
      "structured": {
        "interfaces": {
          "Port-channel11": {
            "ipv4": {
              "neighbors": {
                "1.1.0.1": {
                  "ip": "1.1.0.1",
                  "link_layer_address": "0000.0000.00ca"
                }
              }
            }
          },
          "GigabitEthernet1": {
            "ipv4": {
              "neighbors": {
                "1.1.1.2": {
                  "ip": "1.1.1.2",
                  "link_layer_address": "0000.0000.00cb"
                },
                "1.1.1.3": {
                  "ip": "1.1.1.3",
                  "link_layer_address": "0000.0000.00cc"
                }
              }
            }
          }
        }
      },
      "item": {
        "key": "vrf1",
        "value": {
          "route_distinguisher": "1:1",
          "interfaces": [
            "GigabitEthernet1",
            "Port-channel11"
          ]
        }
      },
      "ansible_loop_var": "item"
    },
    {
      "structured": {
        "interfaces": {
          "Port-channel2": {
            "ipv4": {
              "neighbors": {
                "2.2.2.200": {
                  "ip": "2.2.2.200",
                  "link_layer_address": "0000.0000.00dd"
                }
              }
            }
          }
        }
      },
      "item": {
        "key": "vrf2",
        "value": {
          "route_distinguisher": "2:2",
          "interfaces": [
            "Port-channel2"
          ]
        }
      },
      "ansible_loop_var": "item"
    }
  ],
  "skipped": false,
  "msg": "All items completed",
  "changed": false
}

I would like to create a csv file from this json file that contains the following data:

{{ ansible_host/inventory_host }},vrf1,Port-channel11,1.1.0.1,0000.0000.00ca
{{ ansible_host/inventory_host }},vrf1,GigabitEthernet1,1.1.1.2,0000.0000.00cb
{{ ansible_host/inventory_host }},vrf1,GigabitEthernet1,1.1.1.3,0000.0000.00cc
{{ ansible_host/inventory_host }},vrf2,Port-channel2,2.2.2.200,0000.0000.00dd

The closest I could reach has been:

    - name: Set vrflength variable
      set_fact:
        vrflength: "{{ output.results | length }}"

    - name: Set vrfmaxindex variable
      set_fact:
        vrfmaxindex: "{{ (vrflength | int) - 1 }}"

    - name: Create file
      lineinfile:
        insertafter: EOF
        dest: "report1.csv"
        line: "{{ inventory_hostname }},{{ output.results[ item | int ].item.key }},{{ output.results[ item | int ].structured.interfaces }}"
      with_sequence: start=0 end="{{ vrfmaxindex }}"

`device1,vrf1,{"Port-channel11":{"ipv4":{"neighbors":{"1.1.0.1":{"ip":"1.1.0.1","link_layer_address":"0000.0000.00ca"}}}},"GigabitEthernet1":{"ipv4":{"neighbors":{"1.1.1.2":{"ip":"1.1.1.2","link_layer_address":"0000.0000.00cb"},"1.1.1.3":{"ip":"1.1.1.3","link_layer_address":"0000.0000.00cc"}}}}}

device1,vrf2,{"Port-channel2":{"ipv4":{"neighbors":{"2.2.2.200":{"ip":"2.2.2.200","link_layer_address":"0000.0000.00dd"}}}}}`

I'm unable to figure out how to build the required nested loops (for each VRF, for each interface and, for each neighbor, print the device name, the VRF name, the neighbour IP and the MAC on independent CSV lines).

Would really appreciate any assistance.

Answer 1

Use Jinja. For example, the play

- hosts: all

  vars:

    _csv: |
      {% for i in output.results %}
      {% set key=i.item.key %}
      {% for k,v in i.structured.interfaces.items() %}
      {% for n,l in v.ipv4.neighbors.items() %}
      {{ key }},{{ k }},{{ n }},{{ l.link_layer_address }}
      {% endfor %}
      {% endfor %}
      {% endfor %}

  tasks:

    - include_vars:
        file: report.json
        name: output

    - debug:
        var: _csv

gives (abridged)

  _csv: |-
    vrf1,Port-channel11,1.1.0.1,0000.0000.00ca
    vrf1,GigabitEthernet1,1.1.1.2,0000.0000.00cb
    vrf1,GigabitEthernet1,1.1.1.3,0000.0000.00cc
    vrf2,Port-channel2,2.2.2.200,0000.0000.00dd