Python script to connect to Postgesql backend to retrieve inventory from a CMDB
Python script to retrieve inventory and return in a JSON format
Reference
Sample python script
#!/usr/bin/env python
'''
Example custom dynamic inventory script for Ansible, in Python.
'''
import os
import sys
import argparse
import psycopg2
try:
import json
except ImportError:
import simplejson as json
class Inventory(object):
def __init__(self):
self.inventory = {}
self.read_cli_args()
# Called with `--list`.
if self.args.list:
self.inventory = self.example_inventory()
# Called with `--host [hostname]`.
elif self.args.host:
# Not implemented, since we return _meta info `--list`.
self.inventory = self.empty_inventory()
# If no groups or vars are present, return an empty inventory.
else:
self.inventory = self.empty_inventory()
print( json.dumps(self.inventory) );
# Example inventory for testing.
def example_inventory(self):
# DB connection definition
conn = psycopg2.connect(
host = "hostname-ip",
port = 5432,
database = "postgres",
user = "postgres",
password = "postgres"
)
# Top level dictionary to be returned
output_dict={}
# Initialize dictionary variables for the group and its default keys
group_dict = {}
grouphosts = []
groupvars = {}
# Retrieve the inventory_hosts (store_code)
# WHERE store_code condition for testing
# Should filter based on role
cur_hosts = conn.cursor()
cur_hosts.execute(" SELECT DISTINCT inventory_name FROM table \
WHERE role = 'TEST' \
AND AGE( TO_TIMESTAMP(last_online,'YYYY-MM-DD') ) <= '1 day' ")
for row_host in cur_hosts:
grouphosts.append(row_host[0])
# Append group vars
groupvars["ansible_user"] = 'vagrant'
groupvars["ansible_port"] = '22'
groupvars["ansible_ssh_common_args"] = '-o StrictHostKeyChecking=no'
# groupvars["ansible_password"] = 'smart123!'
# groupvars["ansible_become_password"] = 'smart123!'
# Assigning key value pairs back to top level dictionary
group_dict["hosts"] = grouphosts
group_dict["vars"] = groupvars
output_dict["all"] = group_dict
# Initialize dictionary variables for the group and its default keys
meta_dict = {}
hostvars = {}
cur_hosts = conn.cursor()
cur_hosts.execute(" SELECT DISTINCT inventory_name FROM table \
WHERE role = 'TEST' \
AND AGE( TO_TIMESTAMP(last_online,'YYYY-MM-DD') ) <= '1 day' ")
# Create and add dictionary for each inventory_host (store_code)
for row_host in cur_hosts:
hostvar = {}
cur_hostvars = conn.cursor()
cur_hostvars.execute("SELECT DISTINCT ON (inventory_name) ip FROM table \
WHERE role = 'TEST' AND inventory_name = %s \
AND AGE( TO_TIMESTAMP(last_online,'YYYY-MM-DD') ) <= '1 day' \
ORDER BY inventory_name, last_update DESC LIMIT 1", (row_host[0],))
for row_hostvar in cur_hostvars:
hostvar["ansible_host"] = row_hostvar[0]
# Assign the host variables for the current inventory_host being iterated
hostvars[row_host[0]] = hostvar
meta_dict["hostvars"] = hostvars
output_dict["_meta"] = meta_dict
# Close communication with the database
conn.close()
# Dictionary by JSON format to standard output for debugging
# print( json.dumps(output_dict, indent=4) )
return output_dict
# Example JSON output format
# return {
# Example JSON output format
# 'group': {
# 'hosts': ['10.110.14.137'],
# 'vars': {
# 'ansible_user': 'vagrant',
# 'ansible_port': '22',
# 'ansible password': '',
# 'ansible_ssh_private_key_file':
# '~/.vagrant.d/insecure_private_key',
# 'example_variable': 'value'
# }
# },
# '_meta': {
# 'hostvars': {
# '192.168.28.71': {
# 'host_specific_var': 'foo'
# },
# '192.168.28.72': {
# 'host_specific_var': 'bar'
# }
# }
# }
# }
# Empty inventory for testing.
def empty_inventory(self):
return {'_meta': {'hostvars': {}}}
# Read the command line args passed to the script.
def read_cli_args(self):
parser = argparse.ArgumentParser()
parser.add_argument('--list', action = 'store_true')
parser.add_argument('--host', action = 'store')
self.args = parser.parse_args()
# Get the inventory
Inventory()In such a case, using the function called Dynamic Inventory , it is possible to select management from a static file to management using a dynamic database.
However, there is a dynamic inventory for famous cloud related services and products such as AWS, Azure, OpenStack and CloudStack, but if you do not touch those core functions of the cloud, you may not be able to get it . Perhaps.
But it’s OK, making Dynamic Inventory is surprisingly easy.
This time, I would like to make Dynamic Inventory in several ways.
(The code is appropriate because it is motivated.
Try in the following environment.
$ ansible --version
ansible 2.2.0.0
config file =/etc/ansible/ansible.cfg
configured module search path = Default w/o overrides
What is Dynamic Inventory
First, let’s learn what Dynamic Inventory is like.
There are a lot of things written on the Dynamic Inventory page,but I don’t write any technical details, solet’s take a look at the Developing Dynamic Inventory Sources page.
When the external node script is called with the single argument –list , the script must output a JSON encoded hash/dictionary of all the groups to be managed to stdout .Each group’s value should be either a hash/dictionary containing a list of each host/IP, potential child groups, and potential group variables, or simply a list of host/IP addresses, like so:
Looking at this, after all, Dynamic Inventory is
- accepts –list argument
- Output JSON format data to standard
It turns out that a script is fine.
Basic structure
The basic structure of JSON that Dynamic Inventory should output is
{
"databases" : {
"hosts" : [ "host1.example.com", "host2.example.com" ],
"vars" : {
"a" : true
}
},
"webservers" : [ "host2.example.com", "host3.example.com" ],
"atlanta" : {
"hosts" : [ "host1.example.com", "host4.example.com", "host5.example.com" ],
"vars" : {
"b" : false
},
"children": [ "marietta", "5points" ]
},
"marietta" : [ "host6.example.com" ],
"5points" : [ "host7.example.com" ]
}like,
{
# To simply define only the group host
"<group name 1>": ["<host 1>", "<host 2>"],
# When defining vars and children
"<group name 2>": {
"hosts": ["<host 1>", "<host 2>"],
"vars": {
"<variable name 1>": "< Value1 >"
}
"children": ["<childgroup1>", "<childgroup2>"]
}
}
It seems that we can take the structure.
When Dynamic Inventory first appeared, it seemed to correspond only to the method of defining only group hosts, but since version 1.3 , vars and children can be defined together.
Application: hostvars
As an advanced way of writing, hostvars can also be expressed in Dynamic Inventory.
hostvars was originally implemented by passing the –host <host name> argument to the Dynamic Inventory script to implement the function to obtain hostvars for the specified host.
$ ./dynamic_inventory --host moocow.example.com
{
"asdf" : 1234
} $ ./dynamic_inventory --host llama.example.com
{
"asdf": 5678
}
However, in this specification, regarding the use of hostvars,
- Query Dynamic Inventory for host groups with –list
- Identify the execution target host from the execution group of Ansible
- For each execution target host, execute Dynamic Inventory with –host <host name> to obtain hostvars
The number of startups of Dynamic Inventory increases according to the number of execution target hosts, and the number of queries to the data store increases accordingly .
Therefore, the method of specifying hostvars can be minimized by adding “_meta” to the host group information as shown below .
{
# Host group definition
(omitted)
# definition of
hostvars "_meta": {
"hostvars": {
"moocow.example.com": {"asdf": 1234},
"llama.example.com": {"asdf": 5678},
}
}
}
For inventory files
Let’s use the static file inventory first to see the contrast with Dynamic Inventory.
$ cat inventory
[sample-servers]
192.168.100.10 host_var=hoge
192.168.100.20 host_var=fuga [sample-servers: vars]
group_var = hogefuga
At this time, it moves like this.
$ ansible -i inventory sample-servers -m debug -a "msg={{ host_var }}"
192.168.100.20 | SUCCESS => {
"msg": "fuga"
}
192.168.100.10 | SUCCESS => {
"msg": "hoge"
}
$ ansible -i inventory sample-servers -m debug -a "msg = {{group_var}}"
192.168.100.20 | SUCCESS => {
"msg": "hogefuga"
}
192.168.100.10 | SUCCESS => {
"msg": "hogefuga"
}
Let’s make the same movement to our own Dynamic Inventory.
JSON conversion of inventory
As confirmed on the official page, Dynamic Inventory is not a datastore itself, but a script that outputs a host list in JSON format as standard .
So first, let’s look at the final form of JSON, which has the same meaning as that file. Here.sample_json
{
"_meta": {
"hostvars": {
"192.168.100.10": {
"host_var": "hoge"
},
"192.168.100.20": {
"host_var": "fuga"
}
}
},
"sample-servers": {
"hosts": [
"192.168.100.10",
"192.168.100.20"
],
"vars": {
"group_var": "hogefuga"
}
}
}
In other words, you can create a Dynamic Inventory script to emit this JSON.
Inch Dynamic Inventory
First of all, to check the behavior of Dynamic Inventory, let’s flutter and experience Dynamic Inventory.
Yes, create a script that spits out that JSON.
Write the goal JSON just as it is to a file.
$ cat ~/sample_json
(the contents of the previous JSON)
Then catcreate a script that does just that.
$ vi fake_dynamic_inventory
#!/bin/bash cat ~/sample_json
Let’s move on this Dynamic Inventory right away.
$ ansible -i ./fake_dynamic_inventory sample-servers -m debug -a "msg={{ host_var }}"
ERROR! The file ./fake_dynamic_inventory looks like it should be an executable inventory script, but is not marked executable. Perhaps you want to correct this with `chmod +x ./fake_dynamic_inventory`?
The file ./fake_dynamic_inventory looks like it should be an executable inventory script, but is not marked executable. Perhaps you want to correct this with `chmod +x ./fake_dynamic_inventory`?
Hey, I was angry if I didn’t have the right to do it.
Yes , please note that Dynamic Inventory scripts must have execute permission .
$ chmod +x fake_dynamic_inventory
$ ansible -i ./fake_dynamic_inventory sample-servers -m debug -a "msg = {{host_var}}"
192.168.100.10 | SUCCESS => {
"msg": "hoge"
}
192.168.100.20 | SUCCESS => {
"msg ":" fuga "
}
$ ansible -i ./fake_dynamic_inventory sample-servers -m debug -a "msg = {{group_var}}"
192.168.100.20 | SUCCESS => {
"msg": "hogefuga"
}
192.168.100.10 | SUCCESS => {
"msg ":" hogefuga "
}
Sounds perfect. First of all, it turned out that it works properly when feeding the specified JSON to Ansible.
RDBMS: For PostgreSQL
First of all, speaking of the data store, RDBMS will come to PostgreSQL.
Prepare PostgreSQL with Docker or something. Also psql for table creation.
Like this.
# Get PostgreSQL image
$ docker pull docker.io/postgres # start postgres container
$ docker run --name postgres -e POSTGRES_PASSWORD = password -d postgres # install psql
$ sudo yum install postgresql # Connect to PostgreSQL on postgres container
$ psql -h $ (sudo docker inspect --format "{{.NetworkSettings.IPAddress}}" postgres) -U postgres postgres = #
Let’s create a table and put the data into it.
Make the following table.
Although it is a very stupid table with only holes in the specifications, please understand that it is something like concept data.
- groups table
groupname
sample-servers
- groupvars table
groupname
varname
varvalue
sample-servers
group_var
hogefuga
- hosts table
hostname
groupname
192.168.100.10
sample-servers
192.168.100.20
sample-servers
- hostvars table
hostname
varname
varvalue
192.168.100.10
host_var
hoge
192.168.100.20
host_var
fuga
Please run the following DML and DDL statements from psql.
CREATE TABLE groups (
groupname varchar(16) unique
); CREATE TABLE groupvars (
groupname varchar (16) REFERENCES groups (groupname),
varname varchar (16),
varvalue varchar (16)
); CREATE TABLE hosts (
hostname varchar (16) unique,
groupname varchar (16) REFERENCES groups (groupname)
); CREATE TABLE hostvars (
hostname varchar (16) REFERENCES hosts (hostname),
varname varchar (16),
varvalue varchar (16)
);
INSERT INTO groups VALUES ( 'sample-servers' );
INSERT INTO groupvars VALUES ('sample-servers', 'group_var', 'hogefuga');
INSERT INTO hosts VALUES ('192.168.100.10', 'sample-servers');
INSERT INTO hosts VALUES ('192.168.100.20', 'sample-servers');
INSERT INTO hostvars VALUES ('192.168.100.10', 'host_var', 'hoge');
INSERT INTO hostvars VALUES ('192.168.100.20', 'host_var', 'fuga');
Now, let’s query this database from here and consider getting that JSON output.
I’d like to use some programming language, so I’ll use Python this time.
For connecting to PostgreSQL from Python , use psycopg2 .
$ sudo yum install python-psycopg2
Then create the following Python script and try to run it.
The connection destination of PostgresSQL docker inspectis taken as it is, but if you are doing it other than Docker, please write the IP directly.postgresql_dynamic_inventory.py
#!/usr/bin/python
# -*- coding:utf-8 -*- import commands import psycopg2 import json output_dict={} # DB connection definition conn = psycopg2.connect(
host = commands.getoutput('sudo docker inspect --format "{{ .NetworkSettings.IPAddress }}" postgres'),
port = 5432,
database="postgres",
user="postgres",
password="password") cur_group = conn.cursor() cur_group.execute("""SELECT groupname FROM groups""") # Create and add a dictionary for each group for row_group in cur_group:
group_dict={}
grouphosts=[]
groupvars={} cur_hosts = conn.cursor()
cur_hosts.execute("""SELECT hostname FROM hosts WHERE groupname = %s""", (row_group[0],)) for row_host in cur_hosts:
grouphosts.append(row_host[0]) cur_groupvars = conn.cursor()
cur_groupvars.execute("""SELECT varname, varvalue FROM groupvars WHERE groupname = %s""", (row_group[0],)) for row_groupvar in cur_groupvars:
groupvars[row_groupvar[0]]=row_groupvar[1] group_dict["hosts"]=grouphosts
group_dict["vars"]=groupvars
output_dict[row_group[0]]=group_dict cur_hosts = conn.cursor() cur_hosts.execute("""SELECT DISTINCT hostname FROM hosts""") meta_dict={} hostvars={} # Create and add a dictionary for each host for row_host in cur_hosts:
hostvar={} cur_hostvars = conn.cursor()
cur_hostvars.execute("""SELECT varname,varvalue FROM hostvars WHERE hostname = %s""", (row_host[0],)) for row_hostvar in cur_hostvars:
hostvar[row_hostvar[0]]=row_hostvar[1] hostvars[row_host[0]]=hostvar meta_dict["hostvars"]=hostvars output_dict["_meta"]=meta_dict # Dictionary by JSON format to standard output print json.dumps(output_dict, indent=4)
In particular, without any twist, I rotate the for statement to create a dict and spit it out in json format.
There may be a better way, but if you do it normally, you will probably want to apply a for statement to the nested part because it is an RDBMS.
The image of the table is easy to attach because it is an RDBMS, but it seems a little difficult to output it in JSON.
However, because it satisfies the Dynamic Inventory rule of spitting as JSON,
$ chmod +x postgresql_dynamic_inventory.py
$ ansible -i ./postgresql_dynamic_inventory.py sample-servers -m debug -a "msg = {{host_var}}"
192.168.100.20 | SUCCESS => {
"msg": "fuga"
}
192.168.100.10 | SUCCESS => {
"msg": "hoge"
}
There seems to be no problem with the function as Dynamic Inventory.
If you do your best
In this case, we did not devise the implementation and design, so we checked the concept only, so we omitted much trouble.
For example,
- Group parent-child relationship (children) is not defined
- Only simple key: value variables are defined in groupvars
- Only simple key: value variables are defined in hostvars
There is such a place.
I think that parent-child relationship can probably be improved to some extent by adding parent group information to the groups table. It will be troublesome.
However, recent PostgreSQL and MySQL can now have JSON type etc. as data type .
Therefore, if you use them, you can store practically multiple data in one row and one column, and you may be able to solve this problem.
However, using JSON with RDBMS is still developing, and if there is no other use, it may be better to select another NoSQL database that can originally store JSON data as described below. .
NoSQL: For MongoDB
Next, let’s do the same thing with MongoDB.
MongoDB is one of NoSQL, which is positioned as a document type. In short, it can store data such as JSON without schema as it is.
Let’s do it.
$ sudo docker pull docker.io/tutum/mongodb $ sudo docker run -d -p 27017: 27017 -p 28017: 28017 -e MONGODB_USER = "mongo" -e MONGODB_DATABASE = "mongo" -e MONGODB_PASS = "password" tutum/mongodb
Install a mongo client to access MongoDB.
The only standard repository is the one that comes with MongoDB itself./etc/yum.repos.d/mongo.repo
[mongodb-org-3.2]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/3.2/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-3.2.asc
After that, install the mongo client and put the data into MongoDB.
# install mongo client
$ sudo yum install mongodb-org-shell # Connect to MongoDB
$ mongo mongo -u mongo -p password # Add test data
> db.inventory.insert ({"_ meta": {"hostvars": {"192\uff0E168\uff0E100\uff0E10": {"host_var": "hoge"}, "192\uff0E168\uff0E100\uff0E20 ": {" host_var ":" fuga "}}}," sample-servers ": {" hosts ": [" 192\uff0E168\uff0E100\uff0E10 "," 192\uff0E168\uff0E100\uff0E20 "]," vars ": {" group_var ":" hogefuga "}}})
The subtle point here is that MongoDB cannot use a period as a key name by design.
Therefore, if you look closely at the test data, you can see that the period part in the IP address is changed \uff0Eto.mongodb_dynamic_inventory.py
#!/usr/bin/python
# -*- coding:utf-8 -*- import pymongo import json from bson.json_util import dumps # Definition of DB connection conn = pymongo.MongoClient('127.0.0.1', 27017) db = conn.mongo db.authenticate("mongo","password") inventory = db.inventory output_dict = {} # Inventory collection of acquisition output_dict.update(inventory.find()[0]) # Remove object ID del output_dict["_id"] # Dictionary by JSON format to standard output print json.dumps(output_dict, indent=4)
Naturally, it’s a matter of course, but MongoDB only puts the goal JSON, so there is nothing special to do on the program side.
In other words, is it removing object IDs that are implicitly assigned by MongoDB?
Looking at the actual behavior,
$ chmod +x mongodb_dynamic_inventory.py
$ ansible -i ./mongodb_dynamic_inventory.py sample-servers -m debug -a "msg = {{host_var}}"
192.168.100.20 | SUCCESS => {
"msg": "fuga"
}
192.168. 100.10 | SUCCESS => {
"msg": "hoge"
}
No problem.
Handling of periods
As mentioned when preparing test data, objects stored in MongoDB cannot have periods in key names.
Therefore, data is input after encoding, but in fact the above code
$ ./mongodb_dynamic_inventory.py
{
"_meta": {
"hostvars": {
"192\uff0e168\uff0e100\uff0e10": {
"host_var": "hoge"
},
"192\uff0e168\uff0e100\uff0e20": {
"host_var": "fuga"
}
}
},
"sample-servers": {
"hosts": [
"192\uff0e168\uff0e100\uff0e10",
"192\uff0e168\uff0e100\uff0e20"
],
"vars": {
"group_var": "hogefuga"
}
}
}
As such, the encoding remains in the output as Dynamic Inventory.
I think there is a method to scan and replace all keys, but it may work as it is, so I am not doing it because it is troublesome.
I would like to be able to make it even better with the flow of data retrieval from MongoDB to Python.
By the way, some people may have noticed, but the effect of this is that the host name output by Ansible is a little disgusting.
[
192.168.100.10 | SUCCESS => {
"msg": "hoge"
} [This time]
192.168.100.10. | SUCCESS => {
"msg": "hoge"
}