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()
        WriteLogFile(“*** Populating Call Log table into Dataset ***”)
        OleDbDataAdapter1.FillSchema(PhonelogDataSet, SchemaType.Source, “call_log”)
        WriteLogFile(“*** Populating Processed log files table into Dataset ***”)
        OleDbDataAdapter2.FillSchema(PhonelogDataSet, SchemaType.Source, “proc_logfiles”)
        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.
        ‘    ‘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(“—————————————” & _
        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)
        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
                            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
                                    ‘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)
                                        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)
                                    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
                                ‘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)
                                        ‘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”
                                            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 _
                            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
                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
    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
        Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1)
        UpdCnt = OleDbDataAdapter1.Update(PhonelogDataSet, “call_log”)
        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
        Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter2)
        UpdCnt = OleDbDataAdapter2.Update(PhonelogDataSet, “proc_logfiles”)
        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
        ‘ Does file exist?
        If My.Computer.FileSystem.FileExists(name) Then
            Return True
            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”))
    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
        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.

Leave a Reply

Your email address will not be published. Required fields are marked *