Excel/Ansible Dynamic Inventory script

Intro

This is a dynamic Inventory script for Ansible that turns an Excel Spreadsheet into an Inventory.

This is helpful whenever you get an Excel export from another Tool, containing Information on server, which you need to quickly access via Ansible, without manually writing an Inventory file beforehand.

Requirements

The following requirements must be installed to use this Inventory Script:

Python - Python programming language
OpenPyXL - A Python library to read/write Excel 2010 xlsx/xlsm files

Compatibility

Supported Excel file formats are:

.xlsx
.xlsm
.xltx
.xltm

Tested Ansible Version is: 2.6.2 (but it should be compatible with all Ansible version supporting Dynamic Inventory scripts)

Configuration

To make use of xlsx_inventory.py you must first specify some configuration inside the xlsx_inventory.cfg File.

A Typical configuration file looks like this and is rather self explanatory:

[xlsx_inventory]
xlsx_inventory_file = ./example.xlsx
sheet = Sheet1
hostname_col = A
group_by_col = B

If no such configuration file exists, you can easily create it by running:

xlsx_inventory.py --config --file example.xlsx --hostname-col A --group-by-col B --sheet Sheet1

Example File

Included in this Repository is a Example Excel Spreadsheet, containing multiple sheets used to illustrate, and demonstrate in which ways xlsx_inventory.py can be used.

The first Sheet (Sheet1) contains a Export generated by a third party tool, which can easily be used as an Inventory
The second Sheet (Sheet2) contains an a Subset of (Sheet1) with additional Columns used to set/overwrite Ansible and custom variables.

Grouping and Hostnames

Servers can be grouped by any Column, i.E. with the included example file you could choose to group your hosts by the Column B (Project) or D (Environment) :

The example file also contains two posible Columns that could be used for unique Hostnames, either A (VM Name) or E (DNS Name) would be a good choices, depending on your DNS Settings/Environment.

Host variables

All Columns are automatically turned into Host Variables!

i.E. the Host Variables for the Host stg-zornfix-sql01 (row 35 in Sheet1) are:

{
    "datacenter": "FRA-2",
    "dns_name": "sql01.stg.zornfix.uk",
    "environment": "Staging",
    "project": "zornfix",
    "vm_name": "stg-zornfix-sql01"
}

You can use this to either set/overwrite ansible variables like ansible_host and ansible_ssh_user or to set/overwrite custom variables like mysql_root_pass (example.xlsx Sheet2)

{
    "ansible_host": "10.13.37.01",
    "ansible_ssh_user": "mysql-admin",
    "datacenter": "FRA-1",
    "dns_name": "sql01.dev.zornfix.uk",
    "environment": "Development",
    "mysql_root_pass": "Implosionsstrudel123!",
    "project": "zornfix",
    "vm_name": "dev-zornfix-sql01"
}

To check which host variables are set by the inventory/xlsx file, you can run xlsx_inventory.py --host <HOSTNAME>

Usage

Once configured the Inventory script can be used like any other Dynamic Inventory by specifying it as the inventory in your ansible/ansible-playbook commands:

./xlsx_inventory.py --config --file example.xlsx --group-by-col B --hostname-col A --sheet Sheet2
ansible -i xlsx_inventory.py -m ping dev-zornfix-app01

License

This project is licensed under the GPL3 License - see the LICENSE.md file for details

AlanCoding/ansible_xlsx_inventory