Category Archives: Network Communications

The Amateur Nortel BCM Log File Parser

Coding, Network Communications

(UPDATE:  I’ve recoded this in a much better format, go here to check it out)

I feel bad about having never packaged this program into a more usable state, something along the lines of say PsychoStats, but for phone records.  Anyway below is what I have, if someone can make use of it without having to resort to one of those outrageously expensive packages, so much the better.

First, the VB code:

Imports System
Imports System.IO
Imports System.Text
Imports System.Collections
Imports System.Data.OleDb
‘Imports System.Data.Odbc
‘Imports System.Data.SqlClient
‘Imports System.Data
Module Module1
    Dim logpath_glbl As String = “<PATH TO BCM LOGFILES>”
    Dim logfile_path As String = “c:\phonelog.log”
    ‘Array of file names brought in from the dataset so that they can be
    ‘ compared to the file name being processed
    Dim ProcFileArray(3000) As String
    ‘Number of file names in the ‘already processed files’ list
    Dim MaxRows As Integer = 0
    Dim PhonelogDataSet As New DataSet
    Dim OleDbSelectCommand1 As String = _
        “Provider=SQLNCLI.1;Data Source=MUSE;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=phonelog”
    Dim OleDbConnection1 As New OleDbConnection(OleDbSelectCommand1)
    Dim OleDbDataAdapter1 As New OleDbDataAdapter(“SELECT call_log.* FROM call_log”, OleDbConnection1)
    Dim OleDbDataAdapter2 As New OleDbDataAdapter(“SELECT proc_logfiles.* FROM proc_logfiles”, OleDbConnection1)

    Sub Main()
        Call CreateLogFile()
        OleDbConnection1.Open()
        WriteLogFile(“*** Populating Call Log table into Dataset ***”)
        OleDbDataAdapter1.FillSchema(PhonelogDataSet, SchemaType.Source, “call_log”)
        OleDbDataAdapter1.Fill(PhonelogDataSet)
        WriteLogFile(“*** Populating Processed log files table into Dataset ***”)
        OleDbDataAdapter2.FillSchema(PhonelogDataSet, SchemaType.Source, “proc_logfiles”)
        OleDbDataAdapter2.Fill(PhonelogDataSet)
        OleDbConnection1.Close()
        Call src_List()
    End Sub
    Private Sub CreateLogFile()

        ‘logfile_path = (prof_path & “\desktop\ToledoTrans-” & TextBox_datecode.Text & “.log”)
        Dim fi As FileInfo = New FileInfo(logfile_path)
        ‘Command used to flush existing log file if appending is not desired.
        ‘Try
        ‘    ‘Delete any existing logfile
        ‘    fi.Delete()
        ‘Catch e As Exception
        ‘    Console.WriteLine(“The process failed: {0}”, e.ToString())
        ‘End Try
        ‘Get date and time for the header
        Dim nowdate As Date = Now
        Dim DateString As String = nowdate.ToString(“d”)
        Dim TimeString As String = nowdate.ToString(“T”)
        ‘Write a header on the log file
        Using sw As StreamWriter = File.AppendText(logfile_path)
            ‘sw.Write is used if a CR/LF is not desired
            ‘sw.Write(“This is the “)
            sw.WriteLine(“—————————————” & _
“—————————–“)
            sw.WriteLine(DateString & (Chr(9)) & TimeString)
            sw.WriteLine(“—————————————” & _
            “—————————–“)
            sw.Close()
        End Using

    End Sub
    Private Sub WriteLogFile(ByVal LogData As String)
        ‘writes individual lines to the log file
        Using sw As StreamWriter = File.AppendText(logfile_path)
            sw.WriteLine(LogData)
            sw.Close()
        End Using
    End Sub
    Private Sub AutoTrans(ByVal FileProc() As String, ByVal linecnt As Integer)
        ‘Method extracts legible records from the Nortel logs and calls
        ‘ AutowriteDB method to commit the records to the DB line by line
        ‘Variables which track the number of lines that were successfuly committed to the database
        Dim UpdEntryCnt As Integer = 0
        Dim UpdLogCnt As Integer = 0
        ‘Variable used to see if a log file has already been processed
        Dim FileResult As Integer = 1
        ‘Variables used to track the number of fields in a row
        Dim field_cnt As Integer = 0
        Dim FieldTest As String
        ‘What row is currently being read
        Dim row_cnt As Integer = 0
        ‘Signals if the program has detected a call
        Dim hit As Integer = 0
        ‘Variable which holds the full line to be written
        Dim NumVars As Integer = 7
        Dim hitline(NumVars) As String
        ‘Track number of files processed or not
        Dim TotalAlreadyProcessed As Integer = 0
        Dim TotalProcessed As Integer = 0
        ‘Used for ‘totals’ message after processing
        Dim ProcessString As String

        For FileIndex As Integer = 0 To linecnt
            row_cnt = 0
            ‘Test to see if file to be accessed has already been processed.
            FileResult = GetDoneFiles(FileProc(FileIndex))
            If FileResult = 0 Then
                Using xlatefileReader As New _
        Microsoft.VisualBasic.FileIO.TextFieldParser(logpath_glbl & FileProc(FileIndex))
                    xlatefileReader.TextFieldType = FileIO.FieldType.Delimited
                    xlatefileReader.Delimiters = New String() {”   “}
                    Dim currentRow As String()
                    While Not xlatefileReader.EndOfData
                        Try
                            field_cnt = 0
                            row_cnt = row_cnt + 1
                            currentRow = xlatefileReader.ReadFields()
                            If hit = 0 Then
                                ‘”——–” Indicates a new call entry in the log
                                If currentRow(0) = “——–” Then
                                    ‘For any variables not handled by the program, they will persist
                                    ‘ as *UNKNOWN*
                                    For i As Integer = 0 To NumVars
                                        hitline(i) = “*UNKNOWN*”
                                    Next i
                                    ‘Count the number (field_cnt) of strings (FieldTest)
                                    ‘in the current row.  Only counts of 5 (or 6) will be processed.
                                    For Each FieldTest In currentRow
                                        field_cnt = field_cnt + 1
                                    Next
                                    ‘We need to detect variable field counts since Nortel sucks at writing
                                    ‘ out logs in a consistent fashion (some lines have additional white
                                    ‘ space at the end, some don’t).  Wrong dialed number (i.e. someone
                                    ‘called a phone number in the company block that is unassigned) have CNT of 8.
                                    If field_cnt = 1 Or field_cnt = 2 Or field_cnt = 3 Or field_cnt = 4 Or field_cnt = 8 Then
                                        ‘These (3, 4 and 8 counts) are status fields and are disregarded
                                        ‘ For some reason, an irregular field length wasn’t being caught by
                                        ‘ the program by the final Else statement.  I added 1 and 2 as well.
                                        ‘ The irregular field length was caused by a reboot of the PBX.
                                    ElseIf field_cnt = 5 Or field_cnt = 6 Then
                                        hit = 1
                                        ‘Set DATE and TIME
                                        hitline(0) = currentRow(1)
                                        hitline(1) = currentRow(2)
                                        ‘Set the SRC number to the STN ID, if the number is INCOMING
                                        ‘ it will be changed to the CALLING NUMBER, other wise it will
                                        ‘ remain the SRC number for the OUTGOING
                                        hitline(3) = currentRow(4)
                                        ‘Set the DST number to the Line=, if the number is Outgoing it
                                        ‘ will be changed to DIGITS DIALED, or will be set to the CALLING
                                        ‘ NUMBER for proper INCOMING calls.  For oddball INCOMING calls,
                                        ‘ it will remain this string.
                                        hitline(4) = currentRow(3)
                                    Else
                                        Dim errmsg As String
                                        errmsg = “Abnormal Row Length of ” & field_cnt & ” Found At Line ” & row_cnt
                                        errmsg = errmsg & (Chr(13) & Chr(10)) & ” in file ” & logpath_glbl & FileProc(FileIndex)
                                        errmsg = errmsg & (Chr(13) & Chr(10)) & currentRow(0) & “-” & currentRow(1) & “-” & currentRow(2) & “-” & currentRow(3)
                                        WriteLogFile(errmsg)
                                    End If

                                End If
                                ‘If the new call line was detected, add the detected lines below until a
                                ‘ CALL RELEASED or TRANSFER is detected
                            ElseIf hit = 1 Then
                                For Each FieldTest In currentRow
                                    field_cnt = field_cnt + 1
                                Next
                                ‘Both the CALLING NUMBER and DIGITS DIALED contain
                                ‘ five strings on the line (as detected by VB)
                                If field_cnt = 5 Then
                                    ‘Set the SRC number on INCOMING calls to the
                                    ‘ CALLING NUMBER (currentrow(4))
                                    If currentRow(3) = “CALLING NUMBER” Then
                                        hitline(2) = “INCOMING”
                                        hitline(3) = currentRow(4)
                                    Else
                                        ‘Set the DST on OUTGOING calls to the DIGITS DIALED
                                        If currentRow(3) = “DIGITS DIALED” Then
                                            hitline(2) = “OUTGOING”
                                            hitline(4) = currentRow(4)
                                        End If
                                    End If
                                End If

                                ‘Required to detect odd lines with no ‘Calling number’
                                If field_cnt > 2 Then
                                    If currentRow(1) = “INCOMING CALL” Then
                                        hitline(2) = “INCOMING”
                                    End If
                                End If

                                ‘Set the DST number on INCOMING calls to the DNIS NUMBER
                                If field_cnt = 6 Then
                                    If currentRow(3) = “DNIS NUMBER” Then
                                        hitline(4) = currentRow(5)
                                    End If
                                End If
                                ‘Drop lines where we don’t want logging
                                ‘ Could probably be better accomplished with an array  and Switch, but my needs are limited
                                If hitline(3) = “15554443333” Or hitline(4) = “15554443333” _
                                Or hitline(3) = “15554443333*” _
                                Or hitline(4) = “15554443333*” _
                                Or hitline(3) = “15554442222” _
                                Or hitline(4) = “15554442222” _
                                Or hitline(3) = “15554442222#” _
                                Or hitline(4) = “15554442222#” _
                                Or hitline(3) = “15554441111” _
                                Or hitline(4) = “15554441111” Then
                                    hit = 0
                                End If
                                If field_cnt = 2 Then
                                    ‘When CALL RELEASED is detected, the line is written out and the ‘hit’ reset to 0
                                    If currentRow(1) = “FROM TRANSFER” Then
                                        hitline(2) = “FROM TRANSFER”
                                        hitline(4) = “XFER NUMBER”
                                    ElseIf currentRow(1) = “CALL RELEASED” Or currentRow(1) = “TRANSFERRED” Then
                                        hitline(5) = currentRow(0)
                                        If currentRow(1) = “CALL RELEASED” Then
                                            hitline(6) = “CALL RELEASED”
                                        Else
                                            hitline(6) = “CALL TRANSFERRED”
                                        End If
                                        Dim i As Integer = AutowriteDB(hitline(0), hitline(1), hitline(2), hitline(3), hitline(4), hitline(5), hitline(6), FileProc(FileIndex))
                                        UpdEntryCnt = UpdEntryCnt + i
                                        hit = 0
                                    End If
                                End If
                            End If

                        Catch ex As _
                        Microsoft.VisualBasic.FileIO.MalformedLineException
                            WriteLogFile(“Line ” & ex.Message & _
                            “is not valid and will be skipped.”)
                        End Try
                    End While
                End Using
            End If
            ‘If file wasn’t found in the Processed Files index, then add it
            If FileResult = 0 Then
                Dim i As Integer = NoteFile(FileProc(FileIndex))
                UpdLogCnt = UpdLogCnt + i
                TotalProcessed = TotalProcessed + 1
            Else
                TotalAlreadyProcessed = TotalAlreadyProcessed + 1
            End If
            ‘Reset the result
            FileResult = 1
        Next FileIndex
        ProcessString = “Files in directory which have already been processed: ” & TotalAlreadyProcessed
        ProcessString = ProcessString & (Chr(13) & Chr(10))
        ProcessString = ProcessString & “Files processed in this pass: ” & TotalProcessed
        ProcessString = ProcessString & (Chr(13) & Chr(10))
        ProcessString = ProcessString & “Entrys successfully added to the database: ” & UpdEntryCnt
        ProcessString = ProcessString & (Chr(13) & Chr(10))
        ProcessString = ProcessString & “Processed log files added to the database: ” & UpdLogCnt
        WriteLogFile(ProcessString)
    End Sub
    Private Function AutowriteDB(ByVal f1 As String, ByVal f2 As String, ByVal f3 As String, ByVal f4 As String, ByVal f5 As String, ByVal f6 As String, ByVal f7 As String, ByVal ProcdFile As String) As Integer
        ‘Method commits a phone record to the database

        ‘Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1)
        Dim DsNewRow As DataRow
        Dim calldate As DateTime
        ‘Troubleshooting integer
        Dim UpdCnt As Integer = 0
        ‘Save date and time as a DateTime
        calldate = DateTime.Parse(f1 & ” ” & f2)
        ‘Create a new row in the dataset that holds the ‘call_log’ table
        DsNewRow = PhonelogDataSet.Tables(“call_log”).NewRow()
        DsNewRow.Item(“call_date”) = f1
        DsNewRow.Item(“call_time”) = f2
        DsNewRow.Item(“call_direction”) = f3
        DsNewRow.Item(“call_src”) = f4
        DsNewRow.Item(“call_dest”) = f5
        DsNewRow.Item(“call_length”) = f6
        DsNewRow.Item(“call_status”) = f7
        DsNewRow.Item(“call_datetime”) = calldate
        ‘Add the new changed row to the dataset and commit the change to the database
        PhonelogDataSet.Tables(“call_log”).Rows.Add(DsNewRow)
        OleDbConnection1.Open()
        Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1)
        UpdCnt = OleDbDataAdapter1.Update(PhonelogDataSet, “call_log”)
        OleDbConnection1.Close()
        Return (UpdCnt)
    End Function
    Private Function NoteFile(ByVal lfname As String) As Integer
        ‘Method adds processed files to the processed files table in the
        ‘ database to avert repeated processing
        Dim DsNewRow As DataRow
        ‘Troubleshooting integer
        Dim UpdCnt As Integer = 0
        ‘Create a new row in the dataset that holds the ‘proc_logfiles’ table
        DsNewRow = PhonelogDataSet.Tables(“proc_logfiles”).NewRow()
        DsNewRow.Item(“logfile”) = lfname
        ‘Add the new changed row to the dataset and commit the change to the database
        PhonelogDataSet.Tables(“proc_logfiles”).Rows.Add(DsNewRow)
        OleDbConnection1.Open()
        Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter2)
        UpdCnt = OleDbDataAdapter2.Update(PhonelogDataSet, “proc_logfiles”)
        OleDbConnection1.Close()
        Return (UpdCnt)
    End Function
    Function IsValidFileNameOrPath(ByVal name As String) As Boolean
        ‘ Determines if the name is Nothing.
        If name Is Nothing Then
            Return False
        End If

        ‘ Determines if there are bad characters in the name.
        For Each badChar As Char In System.IO.Path.GetInvalidPathChars
            If InStr(name, badChar) > 0 Then
                Return False
            End If
        Next
        ‘ Does file exist?
        If My.Computer.FileSystem.FileExists(name) Then
            Return True
        Else
            Return False
        End If

        ‘ If file does not meet any criteria, return false
        Return False

    End Function
    Private Sub src_List()
        ‘This method will pull in the list of files and find the ones
        ‘ that have not already been processed (performed in PopulateProcFileArray method)

        ‘Find the files matching the specified pattern
        ‘ note: not year 3000 compatible since the .2* is the beginning of the year 🙂
        Dim pattern As String = “Record.2*”
        Dim ToBProcFiles(3000) As String
        Dim LineCnt As Integer = 0

        ‘Get the pattern without stuff in parens.
        ‘ This came with the program, in case the files being searched
        ‘ for have a description in the program (ex. ‘(*.csv)’)
        If pattern.IndexOf(“(“) >= 0 Then
            pattern = pattern.Substring(0, pattern.IndexOf(“(“))
        End If
        ‘ Get the files
        Dim dir_info As New DirectoryInfo(logpath_glbl)
        Dim file_infos() As FileInfo
        file_infos = dir_info.GetFiles(pattern)
        ‘Read in a list of files that match the pattern
        For Each file_info As FileInfo In file_infos
            ToBProcFiles(LineCnt) = file_info.Name
            LineCnt = LineCnt + 1
        Next file_info
        ‘Build the array which will hold all the files which
        ‘ have already been processed
        Call PopulateProcFileArray()
        ‘Decrement one since last loop will increment one too many
        ‘ variable used in translation method to order ‘For’ loop
        LineCnt = LineCnt – 1
        AutoTrans(ToBProcFiles, LineCnt)
    End Sub
    Private Sub PopulateProcFileArray()
        Dim count As Integer
        ‘Populate the data set for dectecting files which have already been processed
        OleDbDataAdapter2.Fill(PhonelogDataSet, “proc_logfiles”)
        ‘Populate Array to be used for the comparison and remove trailing
        ‘ spaces that are added to the string when it’s put into the DB
        MaxRows = PhonelogDataSet.Tables(“proc_logfiles”).Rows.Count
        MaxRows = MaxRows – 1
        For count = 0 To MaxRows
            ‘remove whitespace
            ProcFileArray(count) = RTrim(PhonelogDataSet.Tables(“proc_logfiles”).Rows(count).Item(“logfile”))
        Next
    End Sub
    Private Function GetDoneFiles(ByVal filename As String) As Integer
        Dim count As Integer
        Dim detector As Integer = 0
        ‘Check to see if the file currently in line to be processed
        ‘ has already been processed. Set to ‘1’ if detected.
        For count = 0 To MaxRows
            If filename = ProcFileArray(count) Then
                detector = 1
                count = MaxRows
            End If
        Next
        Return detector
    End Function

End Module

I’ll follow that up with some notes:

  • I’m sorry, but I do not entirely recall how I structured the database that I was leading the data into.  From what I recall, I can export the SQL commands to create the tables as I’ve had to do that before when purging data (dumping tables and reimporting the desired data is a crude, but effective method for me since I’m not working with that much data), but I don’t have time to play with that at the moment.
  • I had originally built an ASP front end page that responsible parties could use to peruse the data.  I was going to post that code as well, but I noticed that for some reason it is not working.  I can read the data fine in Excel, so I know it’s there but I’d imagine my unworking code won’t be of much help to anyone.
  • The original incarnation of this program was a GUI program that turned the log files into CSVs, though I don’t think that I have the code for that anymore.  It would be easy enough for someone to recreate if they take the base methods and dump the database stuff and replace it with writing the strings out to a CSV file instead.

More details to follow if I get more time to work on this.

Nortel BCM Failure

Network Communications

This chart (PDF link) on the reliability of the Nortel BCM just isn’t true.  It lists a MTBF of 43.1 years with a an average chance of failure per year of 2.3%.  We’ve been using the Nortel BCM 400 for four and half years and we are now undergoing our second replacement.  Therefore I’d put the mean time between failures at a (generous) three years and an average chance of failure per year of 33%.  Plan for your situation accordingly.

Is it AT&T, Parma, or Bad Luck?

Network Communications

The company I work for moved their corporate offices (with which I am affiliated) a little more than a year ago from Cleveland, Ohio to just down the road in Parma, Ohio.  After the move we switched from a single, dedicated T-1 circuit to a series of MPLS circuits that utilize managed (i.e. AT&T owned) Cisco routers.  At our two other MPLS sites, their circuits have been up constantly for the past year while the corporate offices in Parma are now experiencing their third major outage.  Since many major IT services originate from our corporate office this is an inconvenience to say the least.

I was always cool to MPLS since I never cared for the throughput of it’s precursor, frame relay, however AT&T made a strong case which included nice technical capabilities (class of service mostly) and high cost savings at least in the short term.   I’d guess that I’d be mostly satisfied with the service were not for it’s horrible reliability which negates whatever benefit the product might have.  I don’t think my expectations are too high; our Internet circuit, which is still in Cleveland, has only had one major outage in the last ten years (though to be fair that’s over the course of four different circuits, but still).

Insult on injury is the fact that AT&T bought Ameritech some years back and the tech support line for the MPLS circuit will constantly refer to the call being referred to the ‘local telco’.  THE LOCAL TELCO IS YOU AT&T!  I’m amazed that after all this time they haven’t thought of some sort of intra-company integration that might get me an update on my call sometime before thirty minutes after the problem has been fixed.  It’s a shame because I remember the Ameritech days when I would actually get to talk to the person who was fixing my issue rather than someone in a far off land staring at a terminal.

Every time this happens I know it’s not my fault, but I was the one who sold it to my company.  MPLS in Parma?  Thumbs down.  Alternatives?  None really, especially if you need class of service on the line.

Update 5/29/2009: I want to tip my hat to our current rep who did us right on pushing for and obtaining a full credit that will help cover the costs of this outage.  This will definitely make it easier to pitch AT&T products to management in the future, thanks!

ROM Updates

Network Communications, Server Support

For the past ten years I’ve been responsible for the Nortel switches at my current employer but with this last upgrade our vendor talked me into getting HP Procurves instead.  I don’t regret the decision as they were quite affordable, but I’m underwhelmed by the feature set on the routers compared to our older Nortel 460s.  The VLANs don’t seem to set up as smoothly and the initial ROM version that they shipped with did not include an interface on the web management console for updating the firmware.  “That’s okay”, I figured “I’ll update via TFTP like I had for years with my Cisco and Nortel stuff”. As it turns out that’s a no-go as well, the initial ROM version only ships with the ability to update over the serial port.  I’m puzzled as to how HP can ship a product in 2009 that doesn’t include features that have been included for the last ten years in even the cheapest networking gear.

It wasn’t long after my adventure with the switches that I had to update the ILO card on our AiO StorageWorks equipment (1200r if I recall correctly).  I need to point out that first of all the ILO card that comes with the AiO is a vastly inferior to the ILO2 card that comes on the later model Proliants (in fact, it’s labeled as a ‘LO100i’ card and isn’t an ILO at all).  It goes without saying that the equipment didn’t ship with the latest firmware on the ILO card, but the update had to be performed using basically the same mechanism that I used to update Compaq equipment in the mid-nineties: reboot onto an image.  It was USB at least and not floppy, but still, what’s the point of a remote management adapter if you have to be physically present to update it?

Last on my list is my Barracuda spam firewall.  I made the mistake of updating the firmware last night at around 10:30 at night.  The web based update screen provides a VERY poor indication of how the update is advancing.  The unhelpful gauge on the ‘update’ web page eventually locked up completely making it epically unhelpful.  Is it still updating?  Who knows?  Is it okay to reboot?  I hope so because that’s exactly what I did at 11:30 last night.  Barracuda’s web site was as useless as always and reminded me of when our device wasn’t properly authenticating e-mail addresses against our Active Directory LDAP setup.  The fix in that case was to point it at a Global Catalog AD server instead of just any AD server, but just like with my ROM issue Barracuda’s support and web site provided no clues, just the standard bunk that comes packed in with the device itself.  I guess since their tech support never solves any problems, they wouldn’t have any service call details to post to their site.