#!/usr/bin/env python
#
#

year=2012
from pysnmp.entity.rfc3413.oneliner import cmdgen
from pysnmp.proto.rfc1902 import OctetString

import sys
import MySQLdb
import time
import calendar

month={}
for k,v in enumerate(calendar.month_abbr):  
  month[v]=k

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

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

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

def get_clients(host, version):
  versions = {}
  versions[2] = '.1.3.6.1.4.1.2021.73.4.1.2'
  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 alter_date( data):
  clients = []
  for line in data:
    m, d = int(month[line[0]]), int(line[1])
    t=(year, m, d, 0, 0, 0, 0, 0, 0)
    contime = int(time.mktime(t))
    clients.append(( contime, line[2]))

  return clients

def insertintodb ( cursor, leases, host, type ):
  for lease in leases:
    ip    = lease[1]
    host  = lease[2]
    type  = type
    time  = lease[0] + (3600 * 3)

    try:
      sql="insert into users (time, ip, node, type) values ('%s', '%s', '%s', '%s');" % ( time, ip , host, type)
      cursor.execute(sql)
    except:
      continue

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

def get_ips ():
  clients = {}
  f = open ( '/usr/local/users/client-ips.txt')
  for line in f.readlines():
    ip = line.strip().split()
    clients[ip[1]] = ip[0]
  f.close
  return clients

def alter_host(leases):
  new_lease = []
  for line in leases:
    try:
      new_lease.append((line[0], line[1], ips[line[1]]))
    except KeyError:
      continue
   
  return new_lease 

hosts = gethosts ( nodes )
ips = get_ips()

for host in hosts:
  try:
    host = host.strip()
    leases = get_clients ( host, 2 )
    leases = alter_date ( leases )
    leases = alter_host ( leases )
    insertintodb ( cursor, leases, host, 2 )
  except TypeError:
    continue