close

Writing Python Script To Building Ansible Dynamic Inventory With Querying Data From Mysql DB

*Building Ansible  Dynamic Inventory Using Python Scripting:

– Limitation of static inventories:

+ Easily writing and convenient for using with small managed hosts.

+ Manually and difficult keep up to date in large infrastructure.

+ Hard to maintain short-live cloud instances.

– Dynamic Inventory script is executable program to build dynamic inventories automatically.

+Useful to get information from external central management source like monitory system (Zabbix), LDAP, Configuration Management Databases (CMDBs), Private/Public cloud.

+Can be written by any language which provide output as JSON format.

– Convert inventory INI-format file to JSON by using command:

# ansible-inventory -i <inventory INI file> –list

– Easiest to simply reuse dynamic inventory script on GitHub.

Example: Create Python Dynamic Script For Building Inventory From Mysql DB

– Create database and table to store groups/hosts data

CREATE DATABASE `ansible` CHARACTER SET utf8 COLLATE utf8_general_ci;

use ansible;

CREATE TABLE AS_GROUPS(

group_id int NOT NULL AUTO_INCREMENT,

group_name varchar(36) NOT NULL,

PRIMARY KEY (group_id )

);

CREATE TABLE AS_HOSTS(

host_id int NOT NULL AUTO_INCREMENT,

host_name varchar(66) NOT NULL,

host_group int NOT NULL,

PRIMARY KEY (host_id)

);

INSERT INTO AS_GROUPS (group_name)

VALUES

(‘webserver’),

(‘dbserver’),

(‘ftp-server’);

INSERT INTO AS_HOSTS(host_name,host_group)

VALUES

(‘192.168.9.3’,1),

(‘192.168.9.4’,1),

(‘192.168.9.5’,2),

(‘192.168.9.6’,3);

– Create user and grant privileges for ansible control node query to database

– Install pymysql module using pip

– Writing Python script for querying mysql db and return list group/hosts with JSON format:

#!/usr/bin/python
import pymysql
import json

def main():
        print(example_inventory_query())

def get_Connection():
        connection = pymysql.connect(host=’192.168.9.5′,user=’ansible’,password=’123456′,\
                        db=’ansible’,charset=’utf8mb4′,cursorclass=pymysql.cursors.DictCursor)
        return connection

def example_inventory_query():
        data={}
        data[“_meta”]={}
        data[“_meta”][“hostvars”]={}
        data[“all”]={}
        data[“all”][“children”]=[]

        mysql_con=get_Connection()
        sql=”select * from AS_HOSTS INNER JOIN AS_GROUPS ON host_group=group_id”
        try:
                sql=”select * from AS_GROUPS”
                with mysql_con.cursor() as cursor:
                  cursor.execute(sql)
                  for row in cursor:
                     data[“all”][“children”].append(row[‘group_name’])

                sql=”select group_name from AS_GROUPS”
                with mysql_con.cursor() as cursor:
                   cursor.execute(sql)
                   for row in cursor:
                      grpname=row[‘group_name’]
                      data[grpname]={}
                      data[grpname][“hosts”]=[]
                      sql_host=”select * from AS_HOSTS INNER JOIN AS_GROUPS ON host_group=group_id WHERE group_name='”+grpname+”‘”
                      with mysql_con.cursor() as cursor_new:
                        cursor_new.execute(sql_host)
                        for row_new in cursor_new:
                           data[grpname][“hosts”].append(row_new[‘host_name’])
        finally:
                mysql_con.close()
        with open(‘inventory.json’, ‘w’) as outfile:
                json.dump(data, outfile)
        with open(‘inventory.json’, ‘r’) as infile:
                data_json = infile.read().replace(‘\n’, ”)
        return data_json


if __name__ == ‘__main__’:
        main()
 

– Download python bulding dynamic inventory from mysql db on my GitHub link:

https://github.com/vominhtri1991/building_inventory_frommysql.git

– Testing python building inventory hosts by using command ansible-inventory

– Create inventory file get source from building dynamic script on ansible.cfg

– List dynamic inventory already add to ansible and testing connection with module ping

Tags : AutomationContainerDevOpsK8sKubernetesLinux-Unix

Leave a Response

error: Content is protected !!