Creating an Ansible variable file from an Excel

Introduction

Creating an Ansible variable File from an Excel. In the world of infrastructure as code (IaC), Ansible stands out as a powerful tool for provisioning and managing infrastructure resources. Managing variables for your Ansible scripts can become challenging, especially when dealing with a large number of variables or when collaborating with others.

This blog post will guide you through the process of creating an Ansible variable file from an Excel spreadsheet using Python. By automating this process, you can streamline your infrastructure management workflow and improve collaboration.

Prerequisites

Before we begin, make sure you have the following installed:

• Python: Download and install Python.
• Pandas and openpyxl library: Install it using pip3 install packename.

Clone the Ansible Excel Tool repository from GitHub:

git clone https://github.com/dangnhuhieu/ansible-excel-tool.git
cd ansible-excel-tool

Steps to Creating an Ansible variable file from an Excel

• Step 1: 0.hosts sheet setup
• Step 2: Setting value sheet setup
• Step 3: Execute the Script to Create an Ansible variable File from Excel

Step 1: 0.hosts sheet setup

Start by organizing your hosts in an Excel spreadsheet.

The created inventory file will look like this

Step 2: Setting value sheet setup

Four variable name patterns are created as examples.

Pattern 1: List of objects with the same properties

Example: The list of OS users for RHEL is as follows.

The web01.yml host_vars variables that are generated are as follows

os_users:
- username: apache
  userid: 10010
  groupname: apache
  groupid: 10010
  password: apache
  homedir: /home/apache
  shell: /sbin/nologin
- username: apache2
  userid: 10011
  groupname: apache
  groupid: 10010
  password: apache
  homedir: /home/apache2
  shell: /sbin/nologin

One way to use the host_vars variable

- name: Create user
  user:　<br />
    name: "{{ item.username }}"
    uid: "{{ item.userid }}"
    group: "{{ item.groupname }}"
    state: present
  loop: "{{ os_users }}"

Pattern 2: List of dictionaries

Example: RHEL kernel parameters

The host_vars variables created are: para_list is a list of dictionaries, each of which contains a key and value pair.

lst_dic:
- name: os_kernel
  para_list:
  - key: net.ipv4.ip_local_port_range
    value: 32768 64999
  - key: kernel.hung_task_warnings
    value: 10000000
  - key: net.ipv4.tcp_tw_recycle
    value: 0
  - key: net.core.somaxconn
    value: 511

One way to use the host_vars variable

- name: debug list kernel parameters
  debug:
    msg="{{ item.key }} = {{ item.value }}"
  with_items: "{{ lst_dic | selectattr('name', 'equalto', 'os_kernel') | map(attribute='para_list') | flatten }}"

Pattern 3: A list of dictionaries. Each dictionary has a key called name and a key called para_list. para_list is a list of strings.

Example: < Directory /> tag settings in httpd.conf

The web01.yml host_vars variables that are generated are as follows

lst_lst_httpd_conf_b:
- name: <Directory />
  para_list:
  - AllowOverride None
  - Require all denied
  - Options FollowSymLinks

One way to use the host_vars variable

- name: debug lst_lst_httpd_conf_b
  debug:
    msg:
    - "{{ item.0.name }}"
    - "{{ item.1 }}"
  loop: "{{ lst_lst_httpd_conf_b|subelements('para_list') }}"
  loop_control:　<br />
    label: "{{ item.0.name }}"

Pattern 4: Similar to pattern 3, but the parameter name is blank.

Example: Include settings in httpd.conf

The web01.yml host_vars variables that are generated are as follows

lst_lst_httpd_conf_a:
- name: Include
  para_list:
  - conf.modules.d/00-base.conf
  - conf.modules.d/00-mpm.conf
  - conf.modules.d/00-systemd.conf
- name: IncludeOptional
  para_list:
  - conf.d/autoindex.conf
  - conf.d/welcome.conf

One way to use the host_vars variable

- name: debug lst_lst_httpd_conf_a
  debug: 
    msg:
    - "{{ item.0.name }}"
    - "{{ item.1 }}"
  loop: "{{ lst_lst_httpd_conf_a|subelements('para_list') }}"
  loop_control:
    label: "{{ item.0.name }}"

Step 3: Execute the Script to Create an Ansible Variable File from Excel

python .\ansible\Ansible_Playbook\excel\main.py httpd_parameter_sheet.xlsx

Output

The inventory and host_vars files will be generated as follows

The web01.yml file contents are as follows

os_users:
- username: apache
  userid: 10010
  groupname: apache
  groupid: 10010
  password: apache
  homedir: /home/apache
  shell: /sbin/nologin
- username: apache2
  userid: 10011
  groupname: apache
  groupid: 10010
  password: apache
  homedir: /home/apache2
  shell: /sbin/nologin
lst_dic:
- name: os_kernel
  para_list:
  - key: net.ipv4.ip_local_port_range
    value: 32768 64999
  - key: kernel.hung_task_warnings
    value: 10000000
  - key: net.ipv4.tcp_tw_recycle
    value: 0
  - key: net.core.somaxconn
    value: 511
- name: httpd_setting
  para_list:
  - key: LimitNOFILE
    value: 65536
  - key: LimitNPROC
    value: 8192
- name: httpd_conf
  para_list:
  - key: KeepAlive
    value: 'Off'
  - key: ServerLimit
    value: 20
  - key: ThreadLimit
    value: 50
  - key: StartServers
    value: 20
  - key: MaxRequestWorkers
    value: 1000
  - key: MinSpareThreads
    value: 1000
  - key: MaxSpareThreads
    value: 1000
  - key: ThreadsPerChild
    value: 50
  - key: MaxConnectionsPerChild
    value: 0
  - key: User
    value: apache
  - key: Group
    value: apache
  - key: ServerAdmin
    value: root@localhost
  - key: ServerName
    value: web01:80
  - key: ErrorLog
    value: logs/error_log
  - key: LogLevel
    value: warn
  - key: CustomLog
    value: logs/access_log combined
  - key: LogFormat
    value: '"%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %D" combined'
  - key: Listen
    value: 80
  - key: ListenBackLog
    value: 511
  - key: ServerTokens
    value: ProductOnly
  - key: ServerSignature
    value: 'Off'
  - key: TraceEnable
    value: 'Off'
lst_lst_httpd_conf_a:
- name: Include
  para_list:
  - conf.modules.d/00-base.conf
  - conf.modules.d/00-mpm.conf
  - conf.modules.d/00-systemd.conf
- name: IncludeOptional
  para_list:
  - conf.d/autoindex.conf
  - conf.d/welcome.conf
lst_lst_httpd_conf_b:
- name: <Directory />
  para_list:
  - AllowOverride None
  - Require all denied
  - Options FollowSymLinks
- name: <Directory /var/www/html>
  para_list:
  - Require all granted

Conclusion

By following these steps, you’ve automated the process of creating an Ansible variable file from Excel. This not only saves time but also enhances collaboration by providing a standardized way to manage and document your Ansible variables.

Feel free to customize the script based on your specific needs and scale it for more complex variable structures. Thank you for reading the DevopsRoles page!