Category Archives: Coding

Forefront TMG IP Issue

I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:

  1. I have certain blocks of IP addresses blocked on the Cisco firewall and occasionally a user will repeatedly hit a website that sits in one of those blocks.  I will then have to find out who is doing this as the firewall will always just report the address of the proxy server for the traffic.
  2. Occasionally a user will download malware and I’ll need to find out where they got it from so that the source of the traffic can go into the block list in point #1.

Either way I end up running a report on a system at the site which has SQL Server Management Studio installed on it with a query something like the below:

SELECT [ClientIP]
      ,[ClientUserName]
      ,[ClientAgent]
      ,[ClientAuthenticate]
      ,[logTime]
  FROM [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog]
 WHERE [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog].[DestHostIP] = ‘d3bd0e17-FFFF-0000-0000-000000000000’

You’ll note that the address on the bottom line is rather unintelligible since it uses a semi-IPv6 format used by TMG.  To work around this I had been doing hand translation using the calculator in Windows to figure out what the address I am looking for should be.  However, I grew tired of that and wrote a program that will handle the translation/conversion to and from a regular IPv4 address and Forefront TMG 2010 address (because of some apparent vague interest, I’ve packed a zip with just the executable (no code) here).

I want to note though, that since it was casually put together I didn’t code it to handle any exceptions.  The source code is included though, so if you want to code around some fat fingering then feel free.

Exchange 2007 Mailbox Size Limit Alerts

I’d grown spoiled with my MOM pack for Exchange 2003 since I was able to tweak it to alert me (the admin) when a user’s mailbox was being issued size limit warnings.  Several times I was able to catch users stuck with items tucked away out of their view, or the storage of large files that’s better suited to the file server.  After moving to Exchange 2007 though Microsoft removed the event log entry that I used to trigger the MOM condition.  My fix was to create a script that would run on the same schedule as the one used to issue mailbox size warnings.  After combing a variety of pages I came up with the following imperfect, but sufficient script:

function send-email($SmtpServer,$From,$To,$subject,$Body){
$smtp = new-object system.net.mail.smtpClient($SmtpServer)
$mail = new-object System.Net.Mail.MailMessage
$mail.From = $From
$mail.To.Add($To)
$mail.Subject = $subject
$mail.Body = $Body
#$mail.IsBodyHtml = $true
$smtp.Send($mail)
}

get-mailboxstatistics|out-file mstat.txt
get-childitem mstat.txt|select-string -pattern “Issue”|out-file results.txt
$File=get-childitem “results.txt”
$FileContent=get-content “results.txt”
[string]$formattedLength = $file.Length
if($file.Length -gt 5){
send-email email.server.com “server@example.com” “admin@example.com” “Mailbox size alert” $FileContent
}

I apologize for the lack of comments, but what I’m doing is snagging the stats, searching for the word ‘Issue’ to see if a warning or some such was issued, then checking the resulting file size of the search and if it’s oversized (i.e. actually contains something) it’s emailed off to the desired contact.

(UPDATE: this e-mail size alert works for Exchange 2010 as well)

(UPDATE 12/6/2013: this e-mail size alert does not work for Exchange 2013.  I’m working to brew up a solution.)

First Difficult PowerShell

UPDATE (12/5/2013): Well it appears that I might be at the end of the line with the in/out board script.  Due to some backend changes, the EWSUtil.dll no longer works consistently.  Microsoft has some instructions on how to brew your own by extracting XML output from the service that can then be digested into calendar data, but no one at my company uses the in/out board enough to justify the monumental amount of time that would be required to re-code the connector.  If you have Exchange 2007/2010 then feel free to read on, but if you have Exchange 2013 this page may only be good as a reference.

UPDATE (5/20/2013): I had to make a few changes so that the free/busy calendar script would run properly on Windows Server 2012/Exchange 2013.  First, I changed the line(s) in the batch file to [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”].  Secondly I had to change the name of the Exchange server for the $casUrl in the script.  Lastly I had to add the line [Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn] as the first line in all the script files that I call.

UPDATE (12/12/12): An update to PowerShell breaks the way that I have this set up, leading to an error of  “An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, blah, blah blah“.  I did two things after which I got it to work again, but unfortunately I don’t know which one it was that made it work.  The first (and easiest) is to force PowerShell to run as an earlier version (in this case the command line to launch the script is [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -version 2 -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\v14\bin\exshell.psc1” -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”].  The other thing I did (which I think did the trick, but required PowerShell to clean itself out of memory perhaps as it didn’t work right away) was using a related tip here to add the LoadFromRemoteSources to the powershell.exe.config (I had to create the file).  Anyway…

I’ve had some experience with the Windows PowerShell, but it’s been limited to making obvious changes to scripts that have been exported from Scriptomatic.  Examples of this would include automatically adding an account into the local admins group account on every PC in a domain so that ‘downstream’ admin tasks will execute, and pulling the hard drive capacity and utilization from all the servers in an organization for virtualization sizing data.

That changed for me when I migrated my workplace to Exchange 2007.  Previously we used the old collaborative data objects to build an HTML In/Out schedule board.  Manipulating those simple constructs was fairly easy and a lot of coworkers had grown to rely on the web pages exported by the scripts.  Unfortunately CDOs don’t work with Exchange 2007, necessitating redoing the scripts in Windows PowerShell.

Fortunately Glen Scales had already taken the ball 90% of the way with his FreeBusy In/Out Board script (Note: to make any of this work you will need the EWSUtil.dll that Glen references).  Unfortunately, the last 10% proved to be daunting due to my lacking a high level understanding of PowerShell and (even worse) the general opacity of the programing language itself.  There were some minor HTML duties to attend to, which were easy enough, but the most aggravating aspect of Glen’s script was the fact that the names as exported were not in alphabetical order. I figured this had to be easy so I did something like this:

$mbHash = @{$mbHash1 | Sort-Object -property Value}

But then I discovered “no dice” as hash tables are one way with the key and cannot be sorted by value ‘subdata’.  How about resorting it using the old trusty nested ‘For’ loops?  Although this may be possible in some fashion, hash tables cannot be indexed (i.e. $array[1]=1, but $hashtable[1]=’something’ cannot be done), and since my method relied on a sorted array (as the hash table itself could not be sorted directly) and the hash table entries didn’t work right with the various string commands (since the destination would be cast to ‘hash’), and… well you get the picture.  I decided to ‘brute force’ the sorting when I found that the list order in the final table was reliant upon the $key variable later in the script (this had to be ‘discovered’ as there seems to be some Internet rule about NOT putting comments in PowerShell scripts).  My successful plan was to co-op the entries that Glen was feeding it with my own sorted list ($key1):

########### Code that alphabetizes the ‘key’ file used to build the In/Out data grid
$mbn = @()
$key1 = @()

#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object

#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
#This kludgey way was the only way I could convert $mbn to a string array. Is there a better way?  I sure hope!
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt

for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}
########### End of alphabetizing code

I then switched out Glen’s line that read:

foreach($key in $fbents.keys){

With:

foreach($key in $key1){

That was my big change, but I include the rest of the code that includes a number of changes, including a ‘key’ grid, logo functionality, and a date stamp:

[void][Reflection.Assembly]::LoadFile(“EWSUtil.dll”)
########### Pause function for troubleshooting
function Pause ($Message=”Press any key to continue…”){
Write-Host -NoNewLine $Message
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
Write-Host “”
}

$casUrl = https://mail.domain.com/ews/exchange.asmx
$mbHash = @{ }
########### Added ‘ResultSize unlimited to pull in Forest scope rather than just Domain
get-mailbox -ignoredefaultscope -ResultSize unlimited | foreach-object{
if ($mbHash.ContainsKey($_.WindowsEmailAddress.ToString()) -eq $false){
$mbHash.Add($_.WindowsEmailAddress.ToString(),$_.DisplayName)
}
}

$mbs = @()

$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30
$drDuration.StartTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 06:30”))
$drDuration.EndTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 17:00”))

$batchsize = 100
$bcount = 0

#Note: changed bresult to hash table to take advantage of hash table commands
$bresult = @{}

if ($mbHash.Count -ne 0){
foreach($key in $mbHash.keys){
if ($bcount -ne $batchsize){
$mbs += $key
$bcount++
}
else{
$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
$mbs = @()
$bcount = 0
$mbs += $key
$bcount++
}
}
}

$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
########### Dump system address from the tables
$mbHash.remove(“USERNAME@DOMAIN.com”)
$bresult.remove(“USERNAME@DOMAIN.com”)

$frow = $true

########### Begin build of HTML document
$fbdate=Get-Date
$fbBoard = $fbBoard + “<html>”
$fbBoard = $fbBoard + “<head>”
$fbBoard = $fbBoard + “<title>In & Out Board</title>”
$fbBoard = $fbBoard + “</head>”
$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”

### Originally I had put in a shaded color background
#$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF background=graphics/bg.gif text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”

#### Below is the code I used to put company art on the page
#$fbBoard = $fbBoard + “<table width=37% border=0 cellspacing=0 align=center>”
#$fbBoard = $fbBoard + “<tr>”
#$fbBoard = $fbBoard + “<td width=31% height=58><img src=graphics/LOGO.gif width=100 height=75></td>”
#$fbBoard = $fbBoard + “<td width=39% height=58>”
#$fbBoard = $fbBoard + “<div align=center><img src=graphics/bg.jpg width=207 height=65></div>”
#$fbBoard = $fbBoard + “</td>”
#$fbBoard = $fbBoard + “</tr>”
#$fbBoard = $fbBoard + “</table>”
$fbBoard = $fbBoard + “<hr align=center width=80%>”
$fbBoard = $fbBoard + “<p align=center><i>Last updated on: ” + $fbdate + “</p>”

########### Code that alphabetizes the ‘key’ file used to build the
########### In/Out data grid
$mbn = @()
$key1 = @()

#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object

#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt

for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}

########### End of alphabetizing code

foreach($fbents in $bresult){
foreach($key in $key1){
if ($frow -eq $true){
$fbBoard = $fbBoard + “<table><tr bgcolor=`”#95aedc`”>” +”`r`n”
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=200;`” ><b>User</b></td>” +”`r`n”
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=50;`” ><b>” + $stime.ToString(“HH:mm”) + “</b></td>” +”`r`n”
}
$fbBoard = $fbBoard + “</tr>” + “`r`n”
$frow = $false
}
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$valuehash = $fbents[$key]
if ($stime -eq $drDuration.StartTime){

#Note: added missing ‘<tr>’
$fbBoard = $fbBoard + “<tr><td bgcolor=`”#CFECEC`”><b>” + $mbHash[$valuehash[$stime.ToString(“HH:mm”)].MailboxEmailAddress.ToString()] + “</b></td>”  + “`r`n”
}
switch($valuehash[$stime.ToString(“HH:mm”)].FBStatus.ToString()){
“0” {$bgColour = “bgcolor=`”#C0C0C0`””}
“1” {$bgColour = “bgcolor=`”#52F3FF`””}
“2” {$bgColour = “bgcolor=`”#153E7E`””}
“3” {$bgColour = “bgcolor=`”#4E387E`””}
“4” {$bgColour = “bgcolor=`”#98AFC7`””}
“N/A” {$bgColour = “bgcolor=`”#98AFC7`””}
}
$title = “title=”
if ($valuehash[$stime.ToString(“HH:mm”)].FBSubject -ne $null){
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + ” ” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
else {
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + “`” ”
}
}
else {
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
}
if($title -ne “title=”){
$fbBoard = $fbBoard + “<td ” + $bgColour + ” ” + $title + “></td>”  + “`r`n”
}
else{
$fbBoard = $fbBoard + “<td ” + $bgColour + “></td>”  + “`r`n”
}

}
$fbBoard = $fbBoard + “</tr>”  + “`r`n”
}
}
$fbBoard = $fbBoard + “</table>”  + ”  ”
$fbBoard = $fbBoard + “<br>”

##########  Key grid
$fbBoard = $fbBoard + “<center><table align=center cellpadding=’0′ cellspacing=’0′ cols=’2′ width=’80%’ bordercolor=’#FFFFFF’ border=’1′ bordercolorlight=’#FFFFFF’ bordercolordark=’#FFFFFF’>”
$fbBoard = $fbBoard + “<tr valign=’top’>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#52F3FF’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Tentative</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#C0C0C0′>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Free</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#153E7E’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Busy</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#4E387E’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Out of Office</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#000000′>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;No Information</font></th>”
$fbBoard = $fbBoard + “</tr>”
$fbBoard = $fbBoard + “</table></center>”

$fbBoard = $fbBoard +  “<br>”
$fbBoard = $fbBoard +  “<table border=’0′ cellspacing=’10’ height=’12’ width=’100%’>”
$fbBoard = $fbBoard +  “<tr>”
$fbBoard = $fbBoard +  “<a name=inout></a><b><u>More…</u></b><br>Black blocks on the grid indicate that nothing has been scheduled within a couple weeks or so of today’s date. In order to view more detail or days in the future you will need to use the robust features from within Outlook. This can be done most easily by <a href=http://support.microsoft.com/kb/293162>adding a group Calendar</a> (first tip). ”
$fbBoard = $fbBoard +  “</tr>”
$fbBoard = $fbBoard +  “</table>”
$fbBoard = $fbBoard +  “</body>”
$fbBoard = $fbBoard +  “</html>”

$fbBoard | out-file “inout.htm”

To schedule this Exchange 2007 script I put the following command in a batch file and referenced it in the task scheduler:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1” -command “.’fb9to5v2.ps1′”

An example of the output from the full script is here.

Extra Credit! Later I also added code and changed two lines so that I could build multiple web pages showing info for future dates.  I’m sure that it’s possible to feed the days forward desired in as a command line variable, but I don’t have time to work on that at the moment.  The applicable code is:

########### Add a day
$a=[DateTime]::Now.AddDays(1)
[string]$aYear=$a.year
[string]$aDay=$a.day
[string]$aMonth=$a.month
$StartDate=$aYear + “-” + $aMonth  + “-” + $aDay + ” 06:30″
$EndDate=$aYear + “-” + $aMonth  + “-” + $aDay + ” 17:30″
# snag dates for web header (not shown)
[string]$WebDate=$aMonth + “/” + $aDay + “/” + $aYear
$WebDateDay=$a.dayofweek
$b=get-date
$WebDateNow=$b.ToShortDateString()

$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30, changed lines to process time strings
$drDuration.StartTime = [DateTime]::Parse($StartDate)
$drDuration.EndTime = [DateTime]::Parse($EndDate)

I’ll have to leave it to individuals to decide how to integrate the code.

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.

Visual Basic Project – Positive Pay translation program

Last year the company I work for decided to begin using the Positive Pay process offered by Key Total Treasury*.  The issue we had when we were looking to implement was that we had three separate bank accounts and no easy way to program an export within each system to match the format needed for the Positive Pay file used by Key Bank.  Fortunately though, we could get CSV (comma delimited) check reports rather easily out of each system.  Although I knew next to nothing about programming in any of the three systems that we use, I knew that I could cobble something together in Visual Basic that could translate, or convert, the CSV file formats to the semi-proprietary fixed width format required by Key Bank.  After a period of use, I requested permission to make a more generic version of our translator programs that others might make use of.  In my spare time I acquired enough knowledge about XML programming to put together the program below that uses an XML initialization file:

http://cid-f206759e5151f410.skydrive.live.com/self.aspx/PositivePayFiles/positive%20pay%20translator.zip (requires a Live ID I think)

The 2.0 version of the .Net runtime is required, and also although it’s not required I would recommend compiling your own version from the source due to the sensitive nature of the information that the program handles (otherwise the executable is under the ‘bin’ folder).  If you do not have Visual Studio, you can download the free Express version.  If you do not use Windows, Mono’s migration tool states that the program should run under the Mono framework, though I have no experience in that area.

When exporting a CSV from your system the fields will need to be in the following order (unless you change the ‘Read_Exported_File’ method in the program):

  • The first field is the check number
  • The second field is the check date
  • The third field is the check amount
  • The fourth field is the void code
  • The fifth field is the vendor name

With the program is an example of the XML configuration file (ppaysettings.xml) which has the following options:

  • AcctName – generally the bank’s name.  It is used for operator verification
  • AcctNum – the bank account number that will appear in the positive pay file
  • VoidCode – string code in the CSV that designates that a check has been voided (can be blank)
  • NonVoidCode – string code in the CSV that designates that a check has NOT been voided (can be blank)
  • OmitPayee – do not put the customer name in the Positive Pay file.  Required for checks printed with dot matrix printers (True or False only, not blank)

In the not too distant future I should have a more fully vetted version along with some exception conditions that would be related to how wire transfers are handled.

As a note, this version drops checks that are canceled in the same run.  This is typically due to a printer malfunction and since the check is never cut, it is never verified against the positive pay data anyway.

Notes 11/28/2008:

This is an early build and may not be bug free as I haven’t tested it fully.  The information in the Paysettings.xml file is not required, but the file itself is at this time.

*Key Bank/Key Total Treasury does not endorse anything to do with this program