Ulsoor BSNL Project Group 3

Status of the Current project ; Updated 5th march 2016

1) we were give with 2 sample xls file of in which first excel sheet contained ' Outer Vlan ' of the customer



Second Excel Sheet contained Mpdslam Details of the Outer Vlan as shown below



we had to Extract take Outer VLAN no as an input from Excel Sheet 1 and Search for the following

1. Identify which BGE that OVLAN belonged to from Excel Sheet 1
2. Then Open the second Excel sheet open up the respective BGE page and identify the DSLAM IP from the OVLAN no
3. Once DSLAM IP is identified , use this DSLAM IP as a Next variable and pass that to TELNET login

Process 1-to 3 had to be automated





          Current Status of of now 
Issues Identified in the raw data

1. the 1st Excel sheet given contained unfilled BNG/BGE column as shown below




                            
For effective Search we had to fill in this partially filled data so we have managed to do it with a python script which fills in the unfilled colums and creates a new excel data as shown below

Script to Fill in the incomplete data


import xlrd
import xlwt

workbook = xlrd.open_workbook('port bind req_050216.xlsx')
worksheet = workbook.sheet_by_index(0)
workbook1 = xlwt.Workbook()
sheet = workbook1.add_sheet('Sheet_1')

for i in range(0,112):
    for j in range(0,8):
        sheet.write(i,j,worksheet.cell(i,j).value)



var = worksheet.cell(1,8).value
for i in range(2,112):
    if worksheet.cell(i,8).value == xlrd.empty_cell.value:
        sheet.write(i,8,var)
    else:
        var = worksheet.cell(i,8).value
        sheet.write(i,8,var)


workbook1.save('portbind.xls')


This Script Solved the first Issue


Working of our Code


Code to Automate all the three Process 1-to-3 as stated previously

Code:

#!/usr/local/bin/python
import getpass
import sys
import telnetlib

import xlrd
import xlwt

workbook = xlrd.open_workbook('portbind.xls')
worksheet = workbook.sheet_by_index(0)

workbook2 = xlrd.open_workbook('MPdslamDetails_4-09-14 (1).xls')


ovlan = str(input("Enter the OVLAN of customer"))+".0"

for i in range(0,112):

    if str(worksheet.cell(i,5).value) == ovlan:
        print "BNG of customer is "+worksheet.cell(i,8).value
        worksheet2 = workbook2.sheet_by_name(str(worksheet.cell(i,8).value))
        for j in range(1,100):
            if str(worksheet2.cell(j,6).value) == ovlan:
                print "DSLAM IP is "+ str(worksheet2.cell(j,5).value)
                print "Location is: "+ str(worksheet2.cell(j,1).value)
                print "Status of Identified DSLAM :"+ str(worksheet2.cell(j,8).value)
                print "Telnet Login in Progress for DSLAM IP :"+ str(worksheet2.cell(j,5).value)




 Out put We successfully Obtained was:
Sample Output while we tested with the Sample Excel Files:



Verified to check if the Output is error free :

Input gives as OVLAN no: 2014


It identified that BNG/BGE of Customer is JNR-1 as seen in the screen shot above




2.It identified that OVLAN 2014 of JNR-1 BNG has DSLAM IP of 10.224.64.14


as verified above it did identify the DSLAM IP exactly , and it also stats which Location is that DSLAM at and cureent status of that DSLAM

3. once DSLAM as been Identified a telnet Login Is called to Login to the Respective DSLAM IP
This Process of Login to DSLAM has not yet automated as of now

Script to call Telnet Login :

import getpass
import sys
import telnetlib

HOST = "localhost"
print "Telnet login granted ."
user = raw_input("Enter Remote User name : ")
password = getpass.getpass()

tn = telnetlib.Telnet(HOST)

tn.read_until("login: ")
tn.write(user + "\n")
if password:
    tn.read_until("Password: ")
    tn.write(password + "\n")

tn.write("ls\n")
tn.write("exit\n")

print tn.read_all()