trying to access an access db

[Chaucer]

any suggestions on how to access an access db through securecrt and vbscript?

[wasodg2]

Could you be more specific, there are many way to access MS Access VIA VBScript. How does it pertaion to CRT?

[Chaucer]

I'm going to be polling for data through a telnet session and that information will be stored into an access db.

[Chaucer]

btw, I did figure out some of it. This will open a db and cycle through the entries.


# $language = "VBScript"
# $interface = "1.0"

Sub Main

crt.screen.Synchronous = True

DataConn = "C:\mac.mdb"
Set Conn = CreateObject("ADODB.Connection")
ConStr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & DataConn & ";"
Conn.open(ConStr)
Set rs = CreateObject("ADODB.Recordset")

strSQLQuery = "SELECT * FROM store"
rs.Open strSQLQuery, conn, 3, 3

do While Not rs.EOF

Msgbox rs.fields.item("mac") & " " & rs.fields.item("switch")
rs.MoveNext
loop

crt.screen.Synchronous = False

End Sub

[Chaucer]

Finished product.


# $language = "VBScript"
# $interface = "1.0"

' Written by Chaucer
' Program Description: Log into a switch and grab all the arp entries. Store
' this information into an access database for future reference.

Option Explicit

' Globals because I'm to lazy to both with passing variables
Dim ipaddress, strSQLQuery, rs, Conn

Sub Main

crt.screen.Synchronous = True

' Variables
Dim username, password
Dim result, i, dbarray(), rs_count
Dim DataConn, ConStr

' Set login
username = "xxx"
password = "xxx"
ipaddress = ""

'Connection to database
DataConn = "C:\mac.mdb"
Set Conn = CreateObject("ADODB.Connection")
ConStr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & DataConn & ";"
Conn.open(ConStr)
Set rs = CreateObject("ADODB.Recordset")

result = Inputbox("Would you like to get a total count of mac address (1) or to get data (2) ?")

' Just get the total count per switch and display it.
If result = 1 Then

strSQLQuery = "SELECT DISTINCT switch FROM store"
rs.Open strSQLQuery, conn, 3, 3

rs_count = rs.RecordCount

ReDim dbarray(rs_count)

For i = 0 To rs_count - 1
dbarray(i) = rs.Fields.Item("switch")
rs.MoveNext
Next

rs.Close

For i = 0 To rs_count - 1
strSQLQuery = "SELECT * FROM store WHERE switch='" & dbarray(i) & "'"
rs.Open strSQLQuery, conn, 3, 3

MsgBox dbarray(i) & " - Total MAC Addresses: " & rs.RecordCount

rs.Close
Next
' Else go out and grab new data.
ElseIf result = 2 then

ipaddress = Inputbox("What ip address do you want to connect to? (type N to disconnect)")

Do while not ipaddress = "N"

' Log In
crt.session.Connect("/telnet " & ipaddress)

crt.screen.WaitForString "sername:", 5
crt.screen.Send username & vbcr

crt.screen.WaitForString "assword:", 10
crt.screen.Send password & vbcr

crt.screen.WaitForString "#", 3

crt.screen.clear
'Start the information grab
crt.screen.send "show mac-address-table" & VbCr

Do
' Tab down to get all macs
result = crt.screen.WaitForStrings("#", "--More--", 10)

If result = 1 then
writedb
crt.screen.Waitforcursor 1
crt.screen.send "exit" & vbcr
elseif result = 2 then
writedb
crt.screen.send chr(32)
else
msgbox "error"
crt.screen.Waitforcursor 1
crt.screen.send "exit" & vbcr
end if

Loop until result = 1 or result = 0

crt.screen.Waitforcursor 5
ipaddress = Inputbox("What ip address do you want to connect to? (type N to disconnect)")

Loop

Else
' Exit program

End If

crt.screen.Synchronous = False

End Sub

' Subroutine to put info into db
Sub writedb

Dim crtstring, mysplit, i, mac_string

For i = 1 to 46

' Get line
crtstring = crt.screen.get(i, 1, i, 60)

' Look for particular string and if positive match, do something
If Instr(crtstring, "ddress") then

Elseif Instr(crtstring, "-") then

Else
mysplit = Split(crtstring, " ")

mac_string = Replace(mysplit(0),".", "")

strSQLQuery = "SELECT * FROM store where mac='" & mac_string & "'"
rs.Open strSQLQuery, conn, 3, 3

if rs.recordcount = 0 then

rs.AddNew
rs.Fields.item("mac") = mac_string
rs.Fields.item("macraw") = mysplit(0)
rs.Fields.item("switch") = ipaddress
rs.Fields.item("vlan") = mysplit(16) & mysplit(17)
rs.Update

else

End If
rs.close

End If
next

End Sub


sample data:

show mac-address-table
Dynamic Address Count: 479
Secure Address Count: 0
Static Address (User-defined) Count: 0
System Self Address Count: 52
Total MAC addresses: 531
Maximum MAC addresses: 8192
Non-static Address Table:
Destination Address Address Type VLAN Destination Port
------------------- ------------ ---- --------------------
0000.0c07.ac01 Dynamic 1 GigabitEthernet0/1
0000.0c07.ac01 Dynamic 2 GigabitEthernet0/1
0000.0c07.ac01 Dynamic 20 GigabitEthernet0/1
0000.0c07.ac02 Dynamic 1 GigabitEthernet0/1
0000.0c07.ac03 Dynamic 1 GigabitEthernet0/1
0000.aa66.80ce Dynamic 1 GigabitEthernet0/1
0000.aa6b.7408 Dynamic 1 GigabitEthernet0/1
0000.e8e3.6de8 Dynamic 1 GigabitEthernet0/1