Tag Archives: BCM

Nortel BCM Log Parser

I had originally coded this project in VB and never got it into a distributable format.  For example, I had all the file paths and database connection strings hard coded.  After educating myself a bit on C# I recoded the application in that language, and have the program using LINQ instead of (oof!) OLE.  I also now have it coded to accept values specific to a user’s environment via an XML settings file.

What the program does at this point is that it reads the call detail recording logs that are stored in a directory and commits them to an MS SQL/SQLexpress database.  For my own purposes I have all the default settings set on the BCM for recording the log files, so if you’ve changed any of these settings this program may not work.  As well, I have my logs FTP’d every day to a server share that I can access via UNC (i.e. \\server\logfiles).

I have a little setup menu within the program so that you can create the database, set the settings, and then write the settings out to an XML file for future use. Using the XML settings file the program can be scheduled to run if the XML settings file is given to the program as a variable (ex, NortelLogParser c:\settings.xml).

In the future I hope to develop a web front end for the database, but for the time being you’ll have to use Excel or some other tool to query the database for the information that you want.

Click here to download.

UPDATE: I just noticed (12/14/2011) that the field detection is still a little buggy, and a feature that I added after the fact of associating a record with the file it came out of lags by a day.  The program is still better than nothing, but I do have to patch it up a bit when I get some time.

Remote Voicemail on the BCM

For quite a while we have relied on dialing ‘**’ when dialing into the BCM to check our voicemail. However, our snazzy new touchscreen cellphones don’t handle ‘clicking’ the asterisk in a quick, consecutive fashion. I looked around on the Internet for an alternative, but then realized that I could just use the number that is used by the voicemail system as it’s ‘forward’ destination (ours is 300, but yours may be different).

SSL on the Nortel BCM

All of these concepts are probably familiar to those in the know, but I wasn’t able to put the pieces together until I upgraded to Windows 7 and found that without a properly working SSL configuration Windows 7 wasn’t going to load up the BCM system administration utility.

The documentation for the Nortel BCM states to go to the ‘Maintenance’ section, and then ‘Maintenance Tools’ (well it doesn’t say that but I found it anyway), and then ‘Upload a Certificate and Private Key’.  However, where do I get these?  I knew that the certification would come from my Windows based CA that runs in the domain, but there wasn’t a tool to generate a certificate request on the BCM.  My clue was that a private key, the key used to generate the request, had to be uploaded as well.  I then used the version of openssl on the BCM to do the work, though in hindsight it probably would have been easier to use a newer version installed elsewhere. 

First, upon doing a version check of openssl I noticed that the working directory that it was looking for (‘c:\openssl\ssl’) didn’t exist.  I manually created the directory and did the work from there.  Eventually I discovered that the ‘openssl.cnf’ file (that was called something else and buried elsewhere on a different drive) that shipped with the BCM was lacking and I ended up brewing my own with the following settings:

[ req ]
default_bits        = 2048
default_keyfile         = privkey.pem
encrypt_rsa_key        = no
default_md        = sha1
distinguished_name        = req_distinguished_name
x509_extensions         = root_ca_extensions

[ req_distinguished_name ]
0.organizationName = Organization Name (company)
organizationalUnitName = Organizational Unit Name (department, division)
emailAddress = Email Address
emailAddress_max = 40
localityName = Locality Name (city, district)
stateOrProvinceName = State or Province Name (full name)
countryName = Country Name (2 letter code)
countryName_min = 2
countryName_max = 2
commonName = Common Name (hostname, IP, or your name)
commonName_max = 64

[ root_ca_extensions ]
basicConstraints        = CA:true

I then executed a command along the lines of the following and filling out the ‘form’ that comes up:

openssl req -new -newkey rsa:1024 -nodes -keyout bcmkey.pem -out bcmreq.pem

I then FTP’d (bad form, but I already said that a different method would have been better) the two files up to my file server.  I put the ‘req’ file through my Windows CA (‘Base 64 encoded’, and unlike the HP ILO card I didn’t need the whole chain) to get the web server certificate and I then uploaded them both up the BCM and viola, the SSL warning error messages were gone and the manager was happy under Windows 7.

The Amateur Nortel BCM Log File Parser

(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

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.