Home > Enterprise >  ansible CSV header options when appending with for loop
ansible CSV header options when appending with for loop

Time:01-24

i'm trying to gather network devices data using ansible and add to csv file which is working fine for me but then now i would like to have the headers to the match to whats been gathered .

- name: Playbook to collect ntp,snmp_facts and put into csv file
  hosts: all
  connection: network_cli
  gather_facts: true
#  check_mode: yes
  vars:
    output_path: "./reports/"
    filename: "device_report_{{ date }}.csv"
    vendor: CISCO

  tasks:

    - name: CSV - Generate output filename
      set_fact: date="{{lookup('pipe','date  %Y%m%d')}}"
      run_once: true

    - name: CSV - Create file and set the header
      lineinfile:
        dest: "{{ output_path }}/{{ filename }}"
        **line:   hostname,ip_address,image,iostype,model,serialnum,system,version,ntp_server_1,ntp_server_2,vrf,snmp_server_1,snmp_server_2,snmp_server_3,snmp_server_4,snmp_server_5,snmp_server_6**
        create: true
        state: present

    - import_tasks: /path/playbooks/facts/ntp_facts/ntp_facts_get.yml
    # - import_tasks: /path/playbooks/facts/snmp_facts/snmp_facts_get_2960.yml
    # - import_tasks: /path/playbooks/facts/snmp_facts/snmp_facts_get_not_2960.yml
    - import_tasks: /path/playbooks/facts/snmp_facts/snmp_another_test.yml
    - import_tasks: /path/playbooks/facts/dns_facts/dns_facts_domain_name_get.yml

    - name: CSV - Getting all the data just before printing to csv
      set_fact:
        csv_tmp: >
          {{ ansible_net_hostname|default('N/A') }},
          {{ ansible_host|default('N/A') }},
          {{ ansible_net_image|default('N/A') }},
          {{ ansible_net_iostype|default('N/A') }},
          {{ ansible_net_model|default('N/A') }},
          {{ ansible_net_serialnum|default('N/A') }},
          {{ ansible_net_system|default('N/A') }},
          {{ ansible_net_version|default('N/A') }},
          {{ ntp_servers.gathered.servers[0].server|default('N/A') }},
          {{ ntp_servers.gathered.servers[1].server|default('N/A') }},
          {{ ntp_servers.gathered.servers[0].vrf|default('N/A') }}, 
          {% set snmp_list = [] %}
          {% for snmp_host in snmp_hosts %}
            {% set snmp_list = snmp_list.append(snmp_host.host ~ ',' ~ snmp_host.version) %}
          {% endfor %}
          {{ snmp_list|join(',') }},
          {{ domain_name[0]|default('N/A') }},

    - name: check whats up with this csv_tmp
      debug:
        var: csv_tmp
        
    - name: CSV - Write information into .csv file
      lineinfile:
        insertafter: EOF
        dest: "{{ output_path }}/{{ filename }}"
        line: "{{ csv_tmp }}"

    - name: CSV - Blank lines removal
      lineinfile:
        path: "./{{ output_path }}/{{ filename }}"
        state: absent
        regex: '^\s*$'

when appending for each device using csv_tmp i have the for loop for snmp

          {% for snmp_host in snmp_hosts %}
            {% set snmp_list = snmp_list.append(snmp_host.host ~ ',' ~ snmp_host.version) %}
          {% endfor %}
          {{ snmp_list|join(',') }},

So i would not know how many snmp hosts are configured so i was thinking if there are some better ways to have this achieved or somehow have a dynamic option to generate header or have an option to make sure that each of value {{ ansible_net_hostname|default('N/A') }}, into certain specified values first and then remove any empty column .

I have a bit of time constraint so reaching out here for help .

CodePudding user response:

Given the inventory for testing

shell> cat hosts
test_11 ansible_host=10.1.0.61
test_13 ansible_host=10.1.0.63

Create a dictionary first. Declare the below variables in vars. For example, in group_vars

shell> cat group_vars/all/csv_content.yml 
csv_content_dict_str: |
  hostname: {{ ansible_net_hostname|d('N/A') }}
  ip_address: {{ ansible_host|d('N/A') }}
  image: {{ ansible_net_image|d('N/A') }}
  iostype: {{ ansible_net_iostype|d('N/A') }}
  model: {{ ansible_net_model|d('N/A') }}
  serialnum: {{ ansible_net_serialnum|d('N/A') }}
  system: {{ ansible_net_system|d('N/A') }}
  version: {{ ansible_net_version|d('N/A') }}
  ntp_server_1: {{ ntp_servers.gathered.servers[0].server|d('N/A') }}
  ntp_server_2: {{ ntp_servers.gathered.servers[1].server|d('N/A') }}
  vrf: {{ ntp_servers.gathered.servers[0].vrf|d('N/A') }}
  {% for snmp_host in snmp_hosts|d([]) %}
  snmp_server_{{ loop.index }}:  {{ snmp_host.host }}
  snmp_server_version_{{ loop.index }}: {{ snmp_host.version }}
  {% endfor %}
  domain_name: {{ domain_name[0]|d('N/A') }}
csv_content_dict: "{{ csv_content_dict_str|from_yaml }}"
csv_content: |
  {{ csv_content_dict.keys()|join(',') }}
  {{ csv_content_dict.values()|join(',') }}

Without any facts collected, this gives

ok: [test_11] => 
  csv_content_dict:
    domain_name: N/A
    hostname: N/A
    image: N/A
    iostype: N/A
    ip_address: 10.1.0.61
    model: N/A
    ntp_server_1: N/A
    ntp_server_2: N/A
    serialnum: N/A
    system: N/A
    version: N/A
    vrf: N/A
ok: [test_13] => 
  csv_content_dict:
    domain_name: N/A
    hostname: N/A
    image: N/A
    iostype: N/A
    ip_address: 10.1.0.63
    model: N/A
    ntp_server_1: N/A
    ntp_server_2: N/A
    serialnum: N/A
    system: N/A
    version: N/A
    vrf: N/A

Write the files

    - copy:
        dest: "{{ output_path }}/{{ filename }}"
        content: "{{ csv_content }}"

gives

shell> ssh admin@test_11 cat /tmp/reports/device_report_2023-01-22.csv
hostname,ip_address,image,iostype,model,serialnum,system,version,ntp_server_1,ntp_server_2,vrf,domain_name
N/A,10.1.0.61,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A
shell> ssh admin@test_13 cat /tmp/reports/device_report_2023-01-22.csv
hostname,ip_address,image,iostype,model,serialnum,system,version,ntp_server_1,ntp_server_2,vrf,domain_name
N/A,10.1.0.63,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A

  • Example of a complete playbook to write the report
shell> cat write_report.yml 
- hosts: all

  vars:

    output_path: /tmp/reports
    filename: "device_report_{{ date }}.csv"

  tasks:

    - set_fact:
        date: "{{ '%Y-%m-%d'|strftime }}"
      run_once: true
    - file:
        state: directory
        path: "{{ output_path }}"

    - block:
        - debug:
            var: csv_content_dict
        - debug:
            msg: |
              {{ csv_content }}
      when: debug|d(false)|bool

    - copy:
        dest: "{{ output_path }}/{{ filename }}"
        content: "{{ csv_content }}"
  • Example of a complete playbook to read the report
shell> cat read_report.yml 
- hosts: all

  vars:

    output_path: /tmp/reports
    filename: "device_report_{{ date }}.csv"

  tasks:

    - set_fact:
        date: "{{ '%Y-%m-%d'|strftime }}"
      run_once: true
    - community.general.read_csv:
        path: "{{ output_path }}/{{ filename }}"
      register: report
    - debug:
        var: report.list

Create, or collect facts. For example, create host_vars for testing

shell> cat host_vars/test_11/test_network_facts.yml 
snmp_hosts:
  - host: snmp1.example.com
    version: SNMPv3
  - host: snmp2.example.com
    version: SNMPv3
  • Write the report to test_11
shell> ansible-playbook write_report.yml -l test_11

PLAY [all] ***********************************************************************************

TASK [Gathering Facts] ***********************************************************************
ok: [test_11]

TASK [set_fact] ******************************************************************************
ok: [test_11]

TASK [file] **********************************************************************************
ok: [test_11]

TASK [debug] *********************************************************************************
skipping: [test_11]

TASK [debug] *********************************************************************************
skipping: [test_11]

TASK [copy] **********************************************************************************
changed: [test_11]

PLAY RECAP ***********************************************************************************
test_11: ok=4    changed=1    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0   
  • Take a look at the file
shell> ssh admin@test_11 cat /tmp/reports/device_report_2023-01-22.csv
hostname,ip_address,image,iostype,model,serialnum,system,version,ntp_server_1,ntp_server_2,vrf,snmp_server_1,snmp_server_version_1,snmp_server_2,snmp_server_version_2,domain_name
N/A,10.1.0.61,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,N/A,snmp1.example.com,SNMPv3,snmp2.example.com,SNMPv3,N/A
  • Read the file from test_11
shell> ansible-playbook read_report.yml -l test_11

PLAY [all] ***********************************************************************************

TASK [Gathering Facts] ***********************************************************************
ok: [test_11]

TASK [set_fact] ******************************************************************************
ok: [test_11]

TASK [community.general.read_csv] ************************************************************
ok: [test_11]

TASK [debug] *********************************************************************************
ok: [test_11] => 
  report.list:
  - domain_name: N/A
    hostname: N/A
    image: N/A
    iostype: N/A
    ip_address: 10.1.0.61
    model: N/A
    ntp_server_1: N/A
    ntp_server_2: N/A
    serialnum: N/A
    snmp_server_1: snmp1.example.com
    snmp_server_2: snmp2.example.com
    snmp_server_version_1: SNMPv3
    snmp_server_version_2: SNMPv3
    system: N/A
    version: N/A
    vrf: N/A

PLAY RECAP ***********************************************************************************
test_11: ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Q: "It's overwriting every time since some hosts have 5 SNMP configured and some only 2."

A: This can't happen if you write reports into separate files at the remote hosts. Add hours-minutes-seconds %H-%M-%S to the name of the CSV file

    - set_fact:
        date: "{{ '%Y-%m-%d-%H-%M-%S'|strftime }}"
      run_once: true

Then, a new file will be created each time you run the playbook. For example,

shell> ansible-playbook write_report.yml

will create two files

shell> ssh admin@test_11 ls -la /tmp/reports
total 34
drwxr-xr-x   2 root  wheel    4 Jan 23 03:30 .
drwxrwxrwt  13 root  wheel   27 Jan 23 03:30 ..
-rw-r--r--   1 root  wheel  283 Jan 22 08:57 device_report_2023-01-22.csv
-rw-r--r--   1 root  wheel  283 Jan 23 03:30 device_report_2023-01-23-04-30-00.csv
shell> ssh admin@test_13 ls -la /tmp/reports
total 34
drwxr-xr-x   2 root  wheel    4 Jan 23 03:30 .
drwxrwxrwt  10 root  wheel   17 Jan 23 03:30 ..
-rw-r--r--   1 root  wheel  161 Jan 22 08:30 device_report_2023-01-22.csv
-rw-r--r--   1 root  wheel  161 Jan 23 03:30 device_report_2023-01-23-04-30-00.csv

The next option is to write all files to the controller (localhost). For example, create the directories

    - file:
        state: directory
        path: "{{ output_path }}/{{ inventory_hostname }}"
      delegate_to: localhost

and write the files

    - copy:
        dest: "{{ output_path }}/{{ inventory_hostname }}/{{ filename }}"
        content: "{{ csv_content }}"
      delegate_to: localhost

Then, each time you run the playbook new files will be created at the controller

shell> tree /tmp/reports/
/tmp/reports/
├── test_11
│   └── device_report_2023-01-23-04-49-27.csv
└── test_13
    └── device_report_2023-01-23-04-49-27.csv

2 directories, 2 files

You can easily read the reports from the files at the controller. For example, give the CSV files

shell> tree /tmp/reports/
/tmp/reports/
├── test_11
│   ├── device_report_2023-01-23-04-49-27.csv
│   └── device_report_2023-01-23-05-32-40.csv
└── test_13
    ├── device_report_2023-01-23-04-49-27.csv
    └── device_report_2023-01-23-05-32-40.csv

2 directories, 4 files

Read the files

    - community.general.read_csv:
        path: "{{ item.src }}"
      register: out
      with_community.general.filetree: "{{ output_path }}"
      when: item.state == 'file'
      loop_control:
        label: "{{ item.path }}"

Declare the below variables

    output_path: "/tmp/reports"
    reports_str: |
      {% for result in out.results|selectattr('list', 'defined') %}
      {% set _keys = result.item.path|split('/') %}
      {% set host = _keys|first %}
      {% set report = _keys|last|regex_replace('^device_report_(.*)\.csv', '\\1') %}
      - {{ host }}:
          {{ report }}: {{ result.list }}
      {% endfor %}
    reports: "{{ reports_str|from_yaml|combine(recursive=true) }}"
    reports_lists: "{{ dict(reports|dict2items|json_query('[].[key, value.keys(@)]')) }}"

give

  reports:
    test_11:
      2023-01-23-04-49-27:
      - domain_name: N/A
        hostname: N/A
        image: N/A
        iostype: N/A
        ip_address: 10.1.0.61
        model: N/A
        ntp_server_1: N/A
        ntp_server_2: N/A
        serialnum: N/A
        snmp_server_1: snmp1.example.com
        snmp_server_2: snmp2.example.com
        snmp_server_version_1: SNMPv3
        snmp_server_version_2: SNMPv3
        system: N/A
        version: N/A
        vrf: N/A
      2023-01-23-05-32-40:
      - domain_name: N/A
        hostname: N/A
        image: N/A
        iostype: N/A
        ip_address: 10.1.0.61
        model: N/A
        ntp_server_1: N/A
        ntp_server_2: N/A
        serialnum: N/A
        snmp_server_1: snmp1.example.com
        snmp_server_2: snmp2.example.com
        snmp_server_version_1: SNMPv3
        snmp_server_version_2: SNMPv3
        system: N/A
        version: N/A
        vrf: N/A
    test_13:
      2023-01-23-04-49-27:
      - domain_name: N/A
        hostname: N/A
        image: N/A
        iostype: N/A
        ip_address: 10.1.0.63
        model: N/A
        ntp_server_1: N/A
        ntp_server_2: N/A
        serialnum: N/A
        system: N/A
        version: N/A
        vrf: N/A
      2023-01-23-05-32-40:
      - domain_name: N/A
        hostname: N/A
        image: N/A
        iostype: N/A
        ip_address: 10.1.0.63
        model: N/A
        ntp_server_1: N/A
        ntp_server_2: N/A
        serialnum: N/A
        system: N/A
        version: N/A
        vrf: N/A
  reports_lists:
    test_11:
    - 2023-01-23-05-32-40
    - 2023-01-23-04-49-27
    test_13:
    - 2023-01-23-05-32-40
    - 2023-01-23-04-49-27

Example of a complete playbook to read the reports at the controller

shell> cat read_report.yml
- hosts: localhost

  vars:

    output_path: /tmp/reports

    reports_str: |
      {% for result in out.results|selectattr('list', 'defined') %}
      {% set _keys = result.item.path|split('/') %}
      {% set host = _keys|first %}
      {% set report = _keys|last|regex_replace('^device_report_(.*)\.csv', '\\1') %}
      - {{ host }}:
          {{ report }}: {{ result.list }}
      {% endfor %}
    reports: "{{ reports_str|from_yaml|combine(recursive=true) }}"
    reports_lists: "{{ dict(reports|dict2items|json_query('[].[key, value.keys(@)]')) }}"

  tasks:

    - debug:
        msg: "{{ item.src }}"
      with_community.general.filetree: "{{ output_path }}"
      when:
        - debug|d(false)|bool
        - item.state == 'file'
      loop_control:
        label: "{{ item.path }}"

    - community.general.read_csv:
        path: "{{ item.src }}"
      register: out
      with_community.general.filetree: "{{ output_path }}"
      when: item.state == 'file'
      loop_control:
        label: "{{ item.path }}"
    - debug:
        var: reports
    - debug:
        var: reports_lists
  • Related