[python]Comparing missing ip addresses between original inventory with object group

An inventory list is kept to record hostnames and ip addresses, this inventory list (inventory.xlsx) is currently maintained by automation.

For every virtual machine created, the hostname and its corresponding ip address are recorded in the list, automation will update the ip address into the existing object group in the firewall.

However there is a report that there are certain ip addresses not included in the interesting_group, you need to write a script to leverage Tufin SecureTrack to find out what are the missing ip addresses in the existing group as compared with the inventory list.

In the inventory list there are ip addresses which belong to subnet 192.168.1.0/24, this whole subnet is not the network protected by firewall hence skip during collection.

The script needs to be cautious to skip recording items from interesting_group.

This is a code written in haste, hence not structured, there are repeated items which worth to create functions.

import xml.etree.ElementTree as ET
import requests
from openpyxl import load_workbook
from netaddr import IPNetwork, IPAddress


NSMAP = {'xsi': 'http://www.w3.org/2001/XMLSchema-instance'}
tufin_st_xml = requests.session()
tufin_st_xml.verify = False
tufin_st_xml.headers.update({'Content-type': 'application/xml'})
tufin_st_xml.auth = requests.auth.HTTPBasicAuth('admin', 'password')

ip_collections = []
group_collections = []
base_list = []
row = 2
column = 7
ip_from_inventory = []
filtered_ip = []
filtered_ip_collections = []
missing_ip = []
try:
    response = tufin_st_xml.get('https://secure_track_address/securetrack/api/devices/1/network_objects?type=group&show_members=true&name=interesting_group')
    xml_response = ET.fromstring(response.text)
    for xml_item in xml_response.findall('.//ip'):
        ip_collections.append(xml_item.text)
    for xml_type in xml_response.findall("./network_object[@xsi:type='networkObjectGroupDTO']", NSMAP):
        if 'interesting_group' not in xml_type.find('display_name').text:
            group_collections.append(xml_type.find('display_name').text)
except Exception as e:
    print(e)
    exit(1)

if group_collections:
    for group in group_collections:
        print(group)
        try:
            response = tufin_st_xml.get('https://secure_track_address/securetrack/api/devices/1/network_objects?type=group&show_members=true&name={}'.format(group))
            xml_response = ET.fromstring(response.text)
            for xml_item in xml_response.findall('.//ip'):
                ip_collections.append(xml_item.text)
        except Exception as e:
            print(e)
            exit(1)

for ip_item in ip_collections:
    if IPAddress(ip_item) not in IPNetwork('192.168.1.0/24').iter_hosts():
        filtered_ip_collections.append(ip_item)
wb = load_workbook('inventory.xlsx')
while wb['Sheet1'].cell(row, column).value is not None:
    ip_from_inventory.append(wb['Sheet1'].cell(row, column).value)
    row += 1

for ip_item in ip_from_inventory:
    if IPAddress(ip_item) not in IPNetwork('192.168.1.0/24').iter_hosts():
        filtered_ip.append(ip_item)

missing_ip = set(filtered_ip).difference(filtered_ip_collections)
print(missing_ip)