def test_time_in_sql():
''' Test whether updating the time works when you manually
update it with execute SQL. This was done because, for some
reason, the 'set_dependents_as_updated' method was failing
while this wasn't. (Ended up that I had forgotten to actually
update the devices table among everything else...)
'''
with fakeDevice() as f:
db= io_sql.device_db()
record= db.get_device_record('device_id', f['index'])
oldtime= record['updated']
print(oldtime)
del(db)
sleep(.5)
db= io_sql.device_db()
db.execute_sql('''
UPDATE devices
SET updated = now()
where device_id = %s;
''', (f['index'], ), fetch= False)
record2= db.get_device_record('device_id', f['index'])
print('Index: ', f['index'])
print('New Time: ', record2['updated'])
assert oldtime != record2['updated']
def fakeDevice():
''' Helper function to create a fake device and add
it to the database, then clean it up afterwards'''
db = io_sql.device_db()
device = populated_cisco_network_device()
assert not db.exists(unique_name=device.unique_name,
device_name=device.device_name)
index = db.add_device_nd(device)
# Make sure the device was created
assert db.exists(device_id=index)
assert device.device_id == index
# Close the connection to prevent too many connections accumulating
db.conn.commit()
del (db)
yield {'index': index, 'device': device}
# After we're done with it, delete it
db = io_sql.device_db()
db.delete_device_record(index)
def locate(macs):
ddb= device_db()
mp = MacParser()
# If just one mac was passed, make sure it works
if not isinstance(macs, list):
macs= [macs]
for mac in macs:
t = PrettyTable(['Device Name', 'Interface', 'CDP Neighbors'])
t.align = 'l'
print('MAC: ', mac)
# Normalize the MAC
mac = util.ucase_letters(mac)
manuf= mp.get_manuf(mac)
comment= mp.get_comment(mac)
print('Manufacturer: ', manuf, ', ', comment)
locations= ddb.locate_mac(mac)
if len(locations) == 0:
print('No matches found')
else:
for match in locations: t.add_row(match)
print(t, '\n')
def test_devicedb_get_record():
'''The SQL database columns should match up with the names of
attributes in the base network device_class'''
db= device_db()
# Create a fake device and add it to the database
with fakeDevice() as f:
record= db.get_device_record('device_id', f['index'])
assert isinstance(record, psycopg2.extras.DictRow) , 'Record is type [{}]'.format(type(record))
print('Database Columns:\n', [k for k in record.keys()])
print('Class Attributes:\n', [item for item in dir(f['device']) if not item.startswith("__")])
# Make sure that the records in the database have a matching class variable
# and that the values are the same
for k, v in record.items():
# We dont care about 'updated'
if k == 'updated': continue
assert hasattr(f['device'], k)
assert getattr(f['device'], k) == v
def test_fake_device():
db = io_sql.device_db()
with fakeDevice() as f:
assert db.exists(device_id=f['index'])
assert not db.exists(device_id=f['index'])
def run_find_unknown_switches(filter_device_name=[],
filter_interface_name=[],
filter_manufacturer=[],
min_macs=3):
db = device_db()
where_clause = ''
for x in filter_device_name:
where_clause += " AND devices.device_name not like '%{}%' ".format(x)
for x in filter_interface_name:
where_clause += " AND interface_name not like '%{}%' ".format(x)
results = db.execute_sql('''
-- Get all interfaces which have more than one MAC address attached to it but no CDP Neighbors
SELECT
devices.device_name,
interfaces.interface_id,
interface_name,
count(mac_address) as macs
FROM
interfaces
JOIN mac on interfaces.interface_id = mac.interface_id
JOIN devices on devices.device_id=mac.device_id
FULL OUTER JOIN neighbors on neighbors.interface_id=mac.interface_id
WHERE
-- Remove some common false positives
devices.device_name not like '%ven%' AND
interface_name not like '%sup%' AND
interface_name not like '%ort%' AND
interface_name not like '%lan%'
{}
GROUP BY
devices.device_name,
interface_name,
interfaces.interface_id
HAVING
-- Select only interfaces with more than 3 MACs and no CDP neighbors
count(mac_address) >= {} AND
count(neighbors) = 0
ORDER BY devices.device_name, macs DESC;
'''.format(where_clause, min_macs))
output = _generate_table(results)
print(output)
for interf in results:
manufs = _get_entry_manufacturers(interf, filter_manufacturer, db)
if manufs is None: continue
output += '\n\n Device Report: {} - {}\n'.format(interf[0], interf[2])
output += manufs
_write_report(output)
def run_audit(csv_path):
'''
Given a CSV of subnets and MAC addresses, search the database
for all MACs on subnets which match those in the CSV. Compare
each MAC and output a new csv with any matching MAC's listed
by confidence (number of matching characters, starting from the
OUI.
This can be used, for example, for a Wireless Rogue SSID audit,
for which the MAC address of the radios is known and you want to
find out which rogue AP's are physically connected to your network.
'''
if config.cc.modified is False:
config.parse_config()
# Open the input CSV
entries= _open_csv(csv_path)
csv_subnets= sort_csv_by_subnet(entries)
print ('CSV Len: ', len(csv_subnets))
device_db = io_sql.device_db()
results=[]
mp = MacParser(update=True)
# Iterate over each subnet where a rogue was detected
for subnet in sorted(csv_subnets):
print('Subnet: ', subnet)
# Iterate over each mac in the subnet
for mac in device_db.macs_on_subnet(subnet):
# Iterate over each mac in the CSV subnet and
# find matches
for csv_row in csv_subnets[subnet]:
x= evaluate_mac(mac, csv_row['mac'])
if x > 50:
csv_row= dict(csv_row)
csv_row['confidence'] = x
csv_row['wired_mac'] = mac
csv_row['Manufacturer'] = mp.search(mac)
results.append(csv_row)
results= sorted(results, key=lambda x: x['confidence'], reverse=True)
if len(results) == 0: return False
write_csv(results)
write_report(results)
def test_set_dependents_as_updated():
with fakeDevice() as f:
db= io_sql.device_db()
record= db.get_device_record('device_id', f['index'])
oldtime= record['updated']
print(type(oldtime))
print(oldtime)
del(db)
sleep(.5)
config.cc.debug= True
config.cc.verbosity= 6
db= io_sql.device_db()
db.set_dependents_as_updated(device_id= f['index'])
newrecord= db.get_device_record('device_id', f['index'])
print(type(newrecord['updated']))
print(newrecord['updated'])
assert oldtime != newrecord['updated']
def test_update_device():
with fakeDevice() as f:
db= io_sql.device_db()
record= db.get_device_record('device_id', f['index'])
assert f['device'].device_name == record['device_name']
# Change the device name
f['device'].device_name= 'something'
db.update_device_entry(f['device'], device_id= f['index'])
record= db.get_device_record('device_id', f['index'])
assert f['device'].device_name == 'something'
def test_process_duplicate_device():
# Connect to the database
ddb = io_sql.device_db()
# Create a fake device and make sure it doesn't exist
device = helpers.populated_cisco_network_device()
assert not ddb.exists(unique_name=device.unique_name,
device_name=device.device_name)
ddb.add_device_nd(device)
assert ddb.exists(unique_name=device.unique_name)
duplicate = core.process_duplicate_device(device, ddb)
assert duplicate
def test_devicedb_exists_functions():
db= io_sql.device_db()
device= helpers.populated_cisco_network_device()
assert not db.exists(unique_name= device.unique_name,
device_name= device.device_name)
index= db.add_device_nd(device)
# Test the different types of exists statements
assert db.exists(device_id= index)
assert db.exists(device_name= device.device_name)
assert db.exists(unique_name= device.unique_name)
db.delete_device_record(index)
# Test the different types of exists statements
assert not db.exists(device_id= index)
assert not db.exists(device_name= device.device_name)
assert not db.exists(unique_name= device.unique_name)
def write_report(rows):
from datetime import datetime
ddb= device_db()
path= os.path.join(config.cc.run_path, 'mac_audit_report.txt')
with open(path, 'w') as outfile:
outfile.write(textwrap.dedent('''\
Title: Rogue Device Report
Time: {}
Note: In the neighbor table for each match, the interface with no
neighbor or a neighbor which is an obvious non-network
device (such as a phone) is the most likely interface to be
directly connected to the matched MAC.
'''.format(datetime.now().strftime(config.cc.pretty_time))))
for x in rows:
# Get the neighbors
locations= ddb.locate_mac(x['wired_mac'])
result= '\n\n'
result+= '{:12}: {}\n'.format('Matched Mac', x.pop('mac'))
result+= '{:12}: {}\n'.format('Wired Mac', x.pop('wired_mac'))
result+= '{:12}: {}\n'.format('Confidence', x.pop('confidence'))
result+= '{:12}: {}\n'.format('Manufacturer', x.pop('Manufacturer'))
result+= '\n'.join(['{:12}: {}'.format(k, v) for k, v in sorted(x.items())])
result+= '\n\nWhere this MAC was seen:\n'
t = PrettyTable(['Device Name', 'Interface', 'Neighbors'])
t.align = 'l'
for match in locations: t.add_row(match)
result+= str(t) + '\n'
outfile.write(result)
print('Finished writing report to [{}]'.format(path))