#!/usr/bin/env python
#
#

from pysnmp.entity.rfc3413.oneliner import cmdgen
from pysnmp.proto.rfc1902 import OctetString

import sys
import MySQLdb

dbuser="root"
dbpass=""
dbhost="localhost"
dbname="stats"
nodes="/usr/local/users/nodes-users.txt"

db=MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbname, port=int(3306))
cursor = db.cursor()

def snmpwalk(host, oid):
  errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().bulkCmd(
    cmdgen.CommunityData('my-agent', 'public', 1),
    cmdgen.UdpTransportTarget((host, 161)),
    15, 0, # nonRepeaters, maxRepetitions
    oid
  )
  return varBinds

def get_clients(host, version):
  versions = {}
  versions[0] = '.1.3.6.1.4.1.2021.72.4.1.2.9.97.114.112.45.117.115.101.114.115'
  clients = []
 
  lines = snmpwalk(host, versions[version])

  for line in lines:
    try:
      data = str(line[0][1]).split()
      if data[2]:
        clients.append(data)
    except:
      continue

  return clients

def insertintodb ( cursor, entries, host, type ):

  for entry in entries:

    mac   = entry[0]
    start = entry[1]
    end   = entry[2]
    inf   = entry[3]
    oui   = entry[4]

    try:
      sql="insert into connect (mac, inf, start, end, node, type, oui) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s');" % ( mac, inf , start, end, host, type, oui);
      cursor.execute(sql)
    except:
      continue

def gethosts ( file ) :
  f = open( file, 'r')
  return f.readlines()

hosts = gethosts ( nodes )

for host in hosts:
  host = host.strip()
  leases = get_clients ( host, 0 )
  insertintodb ( cursor, leases, host, 0 )
#  leases = get_clients ( host, 1 )
#  insertintodb ( cursor, leases, host, 1 )