BelgiumPythonMeetup/inventory.py at master · iCart/BelgiumPythonMeetup · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
#!/usr/bin/env python
import psycopg2, psycopg2.extras, json
from collections import defaultdict
from pprint import pprint

db =  psycopg2.connect(dbname = '...',
                                        user = '...',
                                        password = '...',
                                        host = '...',
                                        port = 5432,
                                        )

cur = db.cursor(cursor_factory=psycopg2.extras.RealDictCursor)


############## Servers in their groups #############
cur.execute(""" SELECT S.primary_name as server_name, R.name as group
                FROM server as S
                LEFT JOIN server_role as SR ON S.id=SR.srv_id
                LEFT JOIN role as R ON SR.role_id=R.id
            """)

inventory = defaultdict(lambda: { 'hosts': [], 'vars': {}} )

rows = cur.fetchall()
ignored_servers = set()
for row in rows:
    if row['group'] == 'ansible_ignored':
        ignored_servers.add(row['server_name'])

for row in rows:
    if row['server_name'] not in ignored_servers:
        inventory[row['group']]['hosts'].append(row['server_name'])


############## Host variables #############
hostvars = defaultdict(dict)
# ansible_vars
cur.execute(""" SELECT S.id as id, S.primary_name as server_name, V.key, V.value
                FROM server as S
                LEFT JOIN ansible_vars as V ON S.id=V.srv_id
            """)

rows = cur.fetchall()
for row in rows:
    hostvars[row['server_name']]['srv_id'] = row['id']
    hostvars[row['server_name']][row['key']] = row['value']
    hostvars[row['server_name']]['network_ether_interfaces'] = []

# ssh port
cur.execute(""" SELECT S.primary_name as server_name, S.ssh_port as ssh_port
                FROM server as S
            """)

rows = cur.fetchall()
for row in rows:
    hostvars[row['server_name']]['ansible_ssh_port'] = row['ssh_port']

# network devices
cur.execute(""" SELECT S.primary_name as server_name, host(I.ip) as ip, I.type, I.device, netmask(I.ip) as mask
                FROM server as S
                LEFT JOIN ip as I ON S.id=I.srv_id
                WHERE type = 'LAN_OVH'
            """)

rows = cur.fetchall()
for row in rows:
    hostvars[row['server_name']]['network_ether_interfaces'].append({
            'device' : row['device'],
            'bootproto': 'static',
            'address': row['ip'],
            'netmask': row['mask']
        })

inventory['_meta'] = { 'hostvars' : hostvars}


# pprint(dict(hostvars))
#pprint(dict(inventory))
print(json.dumps(inventory))