Ansible variable data to CSV file

AharonuReddy · March 6, 2023, 7:09pm

Hi All,

Could anyone please help for storing data into CSV file from variable with required structure ? Thank you

I have csv variable data from other set_fact as mentioned below

“csv”: [
“cluster1,virtual_clu1,log_vol1,online,used”,
“cluster1,virtual_clu1,log_vol2,offline,not_used”,
“cluster1,virtual_clu1,log_vol3_Test,online,not_used”,
“cluster1,virtual_clu1,log_vol4,offline,not_used”,
“cluster2,virtual_clu2,log_vol6,online,used”,
“cluster2,virtual_clu2,log_vol1,offline,not_used”,
“cluster2,virtual_clu2,log_vol3,online,not_used”
“cluster2,virtual_clu2,log_vol4,online,used”
]

Looking for:
The header must be: cluster_name,log_cluster,vol_name,status,work_status

not working:
(it has to give here when status=online and work_status=not_used )
exclude:
(it has go give here when status=offline and vol_name=<name contacts ‘Test’>)
working:
(it has to give here when status=online and work_status=used
[other then not working & exclude list] )

example: file1.csv

not working:
cluster_name,log_cluster,vol_name,status,work_status
cluster2,virtual_clu2,log_vol3,online,not_used

exclude:
cluster_name,log_cluster,vol_name,status,work_status
cluster1,virtual_clu1,log_vol3_Test,online,not_used
cluster1,virtual_clu1,log_vol2,offline,not_used
cluster1,virtual_clu1,log_vol4,offline,not_used
cluster2,virtual_clu2,log_vol1,offline,not_used

working:
cluster_name,log_cluster,vol_name,status,work_status
cluster1,virtual_clu1,log_vol1,online,used
cluster2,virtual_clu2,log_vol6,online,used
cluster2,virtual_clu2,log_vol4,online,used

Walter_Rowe · March 6, 2023, 7:18pm

Do you have a playbook that isn’t working or are you asking us to write this playbook? When I have challenges like this I create a sandbox playbook that tackles the individual challenge. When I get it working I copy that back into the larger playbook I am developing.

Walter

Walter_Rowe · March 6, 2023, 7:20pm

FYI … based on your CSV list of strings you can use split and map as predecessors in a filter sequence as I showed you in your prior question. This would convert your list of strings into a list of JSON dictionaries. You could then loop through that dict list on multiple tasks with an appropriate when: clause on each one.

Walter

AharonuReddy · March 6, 2023, 7:43pm

Hi Walter,

Getting for the day!

Actually I have data stored in ‘csv’ as mentioned below it is received from some other task and stories in ‘csv’ variable.

I wanted to created CVS file from that data with required data structure I requested. Could you please help me with this to achieve my requirement? Thank you

AharonuReddy · March 6, 2023, 7:58pm

Thanks Walter.

May I still ask a favor for CVS data structure atleast for any of one condion here so I can refer and work on remaining? Once I got flow it helps to explore more. Thanking you.

The header must be: cluster_name,log_cluster,vol_name,status,work_status

not working:
(it has to give here when status=online and work_status=not_used )
exclude:
(it has go give here when status=offline and vol_name=<name contacts ‘Test’>)
working:
(it has to give here when status=online and work_status=used
[other then not working & exclude list] )

Walter_Rowe · March 6, 2023, 8:15pm

name: not working

debug: var=item

loop: “{{ csv_json }}”

when: item.status == ‘online’ and item.work_status == ‘not_used’

name: exclude

debug: var=item

loop: “{{ csv_json }}”

when: (item.status == ‘offline’) and (‘Test’ in item.vol_name)

name: working

debug: var=item

loop: “{{ csv_json }}”

when: (item.status == ‘online’) and (item.work_status == ‘used’)

Walter

AharonuReddy · March 6, 2023, 8:26pm

Hello Walter,

Oh, it helps.

So can I move these 3 conditions data (not workimg, excludr and working) into only one csv file? That is what my final required result.

Thank you,

Walter_Rowe · March 6, 2023, 8:53pm

% cat csv_report.j2
(it has to give here when status=online and work_status=not_used )
not working:
cluster_name,log_cluster,vol_name,status,work_status
{% for vol in csv_json %}
{% if vol.status == ‘online’ and vol.work_status == ‘not_used’ %}
{{ vol.cluster_name }},{{vol.log_cluster}},{{vol.vol_name}},{{vol.status}},{{vol.work_status}}
{% endif %}
{% endfor %}

(it has go give here when status=offline and vol_name=<name contacts ‘Test’>)
exclude:
cluster_name,log_cluster,vol_name,status,work_status
{% for vol in csv_json %}
{% if vol.status == ‘offline’ and ‘Test’ in vol.vol_name %}
{{ vol.cluster_name }},{{vol.log_cluster}},{{vol.vol_name}},{{vol.status}},{{vol.work_status}}
{% endif %}
{% endfor %}

(it has to give here when status=online and work_status=used [other then not working & exclude list] )
working:
cluster_name,log_cluster,vol_name,status,work_status
{% for vol in csv_json %}
{% if vol.status == ‘online’ and vol.work_status == ‘used’ %}
{{ vol.cluster_name }},{{vol.log_cluster}},{{vol.vol_name}},{{vol.status}},{{vol.work_status}}
{% endif %}
{% endfor %}

ansible task

name: write output file
template:
src: csv_report.j2
dest: csv_report.csv
delegate_to: localhost

% more csv_report.csv
(it has to give here when status=online and work_status=not_used )
not working:
cluster_name,log_cluster,vol_name,status,work_status
cluster2,virtual_clu2,log_vol3,online,not_used

(it has go give here when status=offline and vol_name=<name contacts ‘Test’>)
exclude:
cluster_name,log_cluster,vol_name,status,work_status
cluster1,virtual_clu1,log_vol3_Test,offline,not_used

(it has to give here when status=online and work_status=used [other then not working & exclude list] )
working:
cluster_name,log_cluster,vol_name,status,work_status
cluster1,virtual_clu1,log_vol1,online,used
cluster2,virtual_clu2,log_vol6,online,used
cluster2,virtual_clu2,log_vol4,online,used

Walter

Walter_Rowe · March 6, 2023, 9:01pm

If you want to learn all about jinja2 you can read more here.

https://jinja.palletsprojects.com/en/3.1.x/

Walter

utoddl · March 7, 2023, 4:05am

---
- name: Creating CSVs from subsets of data
  hosts: localhost
  gather_facts: false
  vars:
    csv:
      - cluster1,virtual_clu1,log_vol1,online,used
      - cluster1,virtual_clu1,log_vol2,offline,not_used
      - cluster1,virtual_clu1,log_vol3_Test,online,not_used
      - cluster1,virtual_clu1,log_vol4,offline,not_used
      - cluster2,virtual_clu2,log_vol6,online,used
      - cluster2,virtual_clu2,log_vol1,offline,not_used
      - cluster2,virtual_clu2,log_vol3,online,not_used
      - cluster2,virtual_clu2,log_vol4,online,used
  tasks:

    - name: Copy csv where status == 'online' and work_status == 'not_used'
      ansible.builtin.copy:
        content: |
          cluster_name,log_cluster,vol_name,status,work_status
          {% for line in csv %}
          {%     set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(',', maxsplit=4) %}
          {%     if status == 'online' and work_status == 'not_used' %}
          {{ line }}
          {%     endif %}
          {% endfor %}
        dest: aharonu-online-not_used.csv

    - name: Copy csv where status == 'offline' and vol_name.endswith('Test')
      # Note: there are none of these in the test data.
      ansible.builtin.copy:
        content: |
          cluster_name,log_cluster,vol_name,status,work_status
          {% for line in csv %}
          {%     set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(',', maxsplit=4) %}
          {%     if status == 'offline' and vol_name.endswith('Test') %}
          {{ line }}
          {%     endif %}
          {% endfor %}
        dest: aharonu-offline-test.csv

    - name: Copy csv where status == 'online' and work_status == 'used'
      ansible.builtin.copy:
        content: |
          cluster_name,log_cluster,vol_name,status,work_status
          {% for line in csv %}
          {%     set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(',', maxsplit=4) %}
          {%     if status == 'online' and work_status == 'used' %}
          {{ line }}
          {%     endif %}
          {% endfor %}
        dest: aharonu-online-used.csv

Save the above as aharonu-csv.yml and run as

ansible-playbook aharonu-csv.yml -vv && grep , ./aharonu*.csv

vbotka · March 7, 2023, 9:14am

Put the header into the list

header: [cluster_name, log_cluster, vol_name, status, work_status]

and use the filter *community.general.dict* to create the dictionary

  csv_dict: "{{ csv|
                map('split', ',')|
                map('zip', header)|
                map('map', 'reverse')|
                map('community.general.dict') }}"

gives

  csv_dict:
  - cluster_name: cluster1
    log_cluster: virtual_clu1
    status: online
    vol_name: log_vol1
    work_status: used
  - cluster_name: cluster1
    ...

Select the subsets

  notworking: "{{ csv_dict|
                  selectattr('status', '==', 'online')|
                  selectattr('work_status', '==', 'not_used') }}"
  exclude: "{{ csv_dict|
               selectattr('status', '==', 'offline')|
               selectattr('vol_name', 'regex', '^log_vol[1-4]$') }}"
  working: "{{ csv_dict|
               selectattr('status', '==', 'online')|
               difference(notworking)|
               difference(exclude) }}"

and write them to the files. Test it first

    - debug:
        msg: |
          dest: {{ item.1 }}
          {{ item.0 }}:
          {{ header|join(',') }}
          {% for l in lookup('vars', item.0) %}
          {{ l.values()|join(',') }}
          {% endfor %}
      loop:
        - [notworking, fiel1.csv]
        - [exclude, fiel2.csv]
        - [working, fiel3.csv]

gives (abridged)

  msg: |-
    dest: fiel1.csv
    notworking:
    cluster_name,log_cluster,vol_name,status,work_status
    cluster1,virtual_clu1,log_vol3_Test,online,not_used
    cluster2,virtual_clu2,log_vol3,online,not_used

  msg: |-
    dest: fiel2.csv
    exclude:
    cluster_name,log_cluster,vol_name,status,work_status
    cluster1,virtual_clu1,log_vol2,offline,not_used
    cluster1,virtual_clu1,log_vol4,offline,not_used
    cluster2,virtual_clu2,log_vol1,offline,not_used

  msg: |-
    dest: fiel3.csv
    working:
    cluster_name,log_cluster,vol_name,status,work_status
    cluster1,virtual_clu1,log_vol1,online,used
    cluster2,virtual_clu2,log_vol6,online,used
    cluster2,virtual_clu2,log_vol4,online,used

Notes:

* There are two items in *notworking* not only one

* The line where vol_name='log_vol3_Test' shouldn't be in the set
*exclude* because of the condition status=='offline'. Fit the regex
to your needs.

cluster1,virtual_clu1,log_vol3_Test,online,not_used

AharonuReddy · March 7, 2023, 6:08pm

Thank you Todd for your time and help.

Hi All,

I have tested in my environment. It is working good.

i have one item to update for conditions. Could anyone please do me a favor . Thank you

[ how to add one condition like vol_name != ‘_test’ or vol_name !=‘Test*’ ] ( this way working in Python looking help of playbook as i tried and not working)

name: Copy csv where status == ‘online’ and work_status == ‘not_used’
ansible.builtin.copy:
content: |
cluster_name,log_cluster,vol_name,status,work_status
{% for line in csv %}
{% set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(‘,’, maxsplit=4) %}
{% if status == ‘online’ and work_status == ‘not_used’ %}
{{ line }}
{% endif %}
{% endfor %}
dest: aharonu-online-not_used.csv
[ How dow we include instead of endswith(‘Test’), i have to include like vol_name contains ‘Test’, ‘test’…etc]
name: Copy csv where status == ‘offline’ and vol_name.endswith(‘Test’)

Note: there are none of these in the test data.

ansible.builtin.copy:
content: |
cluster_name,log_cluster,vol_name,status,work_status
{% for line in csv %}
{% set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(‘,’, maxsplit=4) %}
{% if status == ‘offline’ or status == ‘online’ and vol_name.endswith(‘Test’) %}
{{ line }}
{% endif %}
{% endfor %}
dest: aharonu-offline-test.csv

name: Copy csv where status == ‘online’ and work_status == ‘used’
ansible.builtin.copy:
content: |
cluster_name,log_cluster,vol_name,status,work_status
{% for line in csv %}
{% set (cluster_name,log_cluster,vol_name,status,work_status) = line.split(‘,’, maxsplit=4) %}
{% if status == ‘online’ and work_status == ‘used’ %}
{{ line }}
{% endif %}
{% endfor %}
dest: aharonu-online-used.csv

Topic		Replies	Views
Variable value saving into one column of csv file Ansible Project	2	2	October 16, 2019
Ansible dict date to csv file Ansible Project ansible-project	0	1	September 10, 2022
Variables from csv and loops with random records Ansible Project	9	0	January 21, 2017
Ansible for loop on csv file, break out title line Ansible Project ubuntu	14	0	December 8, 2017
inventory export to csv format from ansible tower Ansible Project	1	2	March 12, 2021

Ansible variable data to CSV file

Note: there are none of these in the test data.

Related topics