0

I had a macro in Access database (32 bit) that used to download data from Avaya CMS but due to some software changes I cannot use it anymore. I considered using Powershell (run in 32 bit mode) to get the required data but it doesn't work for whatever reason...

The macro in Access Database used to look like this:

Option Compare Database
Option Explicit
    Dim cvsApp As cvsApplication
    Dim cvsCon As cvsConnection
    Dim cvsSrv As cvsServer
    Dim cvsacd As cvsacd
    Dim cvsCatalog As cvsCatalog
    Dim cvsRpt As cvsReport
    Dim b As Object
    Dim Info As Object
    Global Const myAppName As String = "CMS Agent Data"
    Global Const myPath As String = "\\Data\Avaya\TempFiles\"

Sub AvayaLogin()
    Set cvsApp = CreateObject("acsup.cvsApplication")
    If cvsApp.CreateServer("login", "password", "", "server", False, "ENU", cvsSrv, cvsCon) Then
        If cvsCon.Login("login", "password", "server", "ENU") Then
        End If
    End If
End Sub

Sub AvayaLogout()
    On Error Resume Next
     'Closes out all Avaya
    cvsCon.Logout
    cvsCon.Disconnect
    Set cvsSrv = Nothing
    Set cvsCon = Nothing
    Set cvsApp = Nothing
End Sub

Sub AbnCallsData()
On Error Resume Next

Dim MyStartDate, MyStartTime(4), MyStopDate, MyStopTime(4), LastUpdateDate, LastUpdateTime, TimeZoneCounter As Integer, NNow, CNow
Dim TEXT1 As String
Dim FSO
Const TimeZone = 4

'Mop up previous days with this query
LastUpdateDate = DMin("Date", "SQL_LastUpdate")
LastUpdateTime = DMin("Time", "SQL_LastUpdate")
If LastUpdateDate = Date Then
    LastUpdateDate = DMax("Date", "SQL_LastUpdate")
    LastUpdateTime = DMax("Time", "SQL_LastUpdate")
End If
NNow = Format(Now(), "yyyy/mm/dd hh:nn:ss")
    MyStartDate = LastUpdateDate
    MyStartTime(1) = "00:00:00"
     MyStopTime(1) = "10:00:00"
    MyStartTime(2) = "10:00:00"
     MyStopTime(2) = "12:00:00"
    MyStartTime(3) = "12:00:00"
     MyStopTime(3) = "15:00:00"
    MyStartTime(4) = "15:00:00"
     MyStopTime(4) = "23:59:59"

    DoCmd.OpenForm "FrmPleaseWait"
    'Forms!FrmUpdatecms.Visible = False
    DoCmd.RepaintObject acForm, "FrmPleaseWait"

Do Until MyStartDate = Date + 1
    MyStopDate = MyStartDate

    For TimeZoneCounter = 1 To TimeZone
        If LastUpdateDate = Date And Format(LastUpdateTime, "hh:mm:ss") > MyStopTime(TimeZoneCounter) Then
        'Skip first few time zones if applicable
        Else
            CNow = Format(MyStartDate, "yyyy/mm/dd") & " " & MyStartTime(TimeZoneCounter)
            'End if after last time zone
            If NNow < CNow Then
                cvsRpt.Quit
                Exit For
            End If

            If LastUpdateDate = Date And Format(LastUpdateTime, "hh:mm:ss") >= MyStartTime(TimeZoneCounter) And Format(LastUpdateTime, "hh:mm:ss") <= MyStopTime(TimeZoneCounter) Then
'               Go back 30 minutes from latest record for same day
                MyStartTime(TimeZoneCounter) = (Format(LastUpdateTime - 1 / 48, "hh:mm:ss"))
            End If

            cvsSrv.Reports.ACD = "1"
            Set cvsCatalog = cvsSrv.Reports

    'OPENS THE AVAYA REPORT FOR CALL RECORDS
            Set cvsRpt = New cvsReport
            'cvsCatalog.CreateReport cvsCatalog.Reports.Item("Historical\Other\Call Records"), cvsRpt
            Set Info = cvsSrv.Reports.Reports("Historical\Designer\Call Records SL")
            b = cvsSrv.Reports.CreateReport(Info, cvsRpt)

                 'INPUTS THE REPORT CRITERIA
                 'STARTDATE
                If cvsRpt.SetProperty("Start Date", MyStartDate) Then
                Else
                End If
                'STARTTIME
                If cvsRpt.SetProperty("Start Time", MyStartTime(TimeZoneCounter)) Then
                Else
                End If
                'STOP DATE
                If cvsRpt.SetProperty("Stop Date", MyStopDate) Then
                Else
                End If
                'STOP TIME
                If cvsRpt.SetProperty("Stop Time", MyStopTime(TimeZoneCounter)) Then
                Else
                End If

                'Exports the Avaya report
                cvsRpt.FastLoad = True
                cvsRpt.ExportData myPath & "CallRecords" & ".CSV", 44, 0, True, True, True
                'Copy to SQL Server Box
                Set FSO = CreateObject("Scripting.FileSystemObject")
                If (FSO.FileExists(TSQLPath & "CallRecords" & ".CSV")) Then FSO.DeleteFile (TSQLPath & "CallRecords" & ".CSV")
                If (FSO.FileExists(myPath & "CallRecords" & ".CSV")) Then FSO.CopyFile myPath & "CallRecords" & ".CSV", TSQLPath & "CallRecords" & ".CSV"

                'Update Data in TSQl Server
                Call Update_SQL1

                If (FSO.FileExists(TSQLPath & "CallRecords" & ".CSV")) Then FSO.DeleteFile (TSQLPath & "CallRecords" & ".CSV")

                cvsRpt.Quit
            End If
        Next TimeZoneCounter

        MyStartDate = MyStartDate + 1
        Loop
        DoCmd.Close acForm, "FrmPleasewait"
        Call Update_SQL2
        Call AvayaLogout
End Sub

I have transformed it into something like this in Powershell:

$cvsApp = New-Object -ComObject "ACSUP.cvsApplication"
$cvsCon = New-Object -ComObject "ACSCN.cvsConnection"
$cvsSrv = New-Object -ComObject "ACSUPSRV.cvsServer"
$Rep = New-Object -ComObject "ACSREP.cvsReport"

function Get-CallRecordDates {
some sql queries to get the dates I need to run it for
}

$dates = Get-CallRecordDates

$cvsCon.bAutoRetry = $true

$cvsApp.CreateServer("", "", "", "", $False, "ENU", [ref] $cvsSrv, [ref] $cvsCon) #Thank you HAL9256
$cvsCon.Login("", "", "", "ENU")
$cvsSrv.Reports.ACD = "1"

for($i=0; $i -lt $dates.Table.Rows.Count; $i++)
{
    $maindate = Get-Date "$(Get-Date $dates.Table.Rows.Item($i).Date -Format 'dd/MM/yyyy') $(Get-Date $dates.Table.Rows.Item($i).Time -Format 'HH:mm')"

    if($dates.Item($i).Commentary -eq "Last"){
        $startdatetime = $maindate.AddHours(-2)
        $enddatetime = Get-Date
        }else{
        $startdatetime = $maindate.AddHours(-1)
        $enddatetime = $maindate.AddHours(1)
    }

    $startdate = Get-Date $startdatetime -Format "dd/MM/yyyy"
    $starttime = Get-Date $startdatetime -Format "HH:mm"
    $enddate = Get-Date $enddatetime -Format "dd/MM/yyyy"
    $endtime = Get-Date $enddatetime -Format "HH:mm"

    $Info = $cvsSrv.Reports.Reports("Historical\Designer\Call Records SL")

    If($cvsSrv.Reports.CreateReport($Info,$Rep)){
        $Rep.TimeZone = "default"
        $Rep.SetProperty("Start Date",$startdate)
        $Rep.SetProperty("Start Time",$starttime)
        $Rep.SetProperty("Stop Date",$enddate)
        $Rep.SetProperty("Stop Time",$endtime)
        $Rep.FastLoad = $True
        $Rep.ExportData("C:\Users\me\Desktop\CallRecords$($i).csv", 44, 0, $True, $True, $True)
    }
    $dates = Get-CallRecordDates
}
$Rep.Quit()
$cvsCon.Logout()
$cvsCon.Disconnect()

The object $cvsSrv just doesn't see the connection at all. It fails on line $cvsSrv.Reports.ACD = "1" saying "The property 'ACD' cannot be found on this object. Verify that the property exists and can be set." Any lines where the script is using $cvsSrv just returns error.

I tried looking online but I feel like I'm the only person on Earth who tried to marry Avaya and Powershell...

Any help will be appreciated.

Jarek Szczyg
  • 33
  • 2
  • 8
  • Can you provide the output of `$cvsSrv.Reports | Get-Member`? – AdminOfThings Oct 03 '19 at 14:55
  • You may have to pass the variables [by reference](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_ref?view=powershell-6). i.e. `$cvsApp.CreateServer("login", "password", "", "server", $False, "ENU", [ref] $cvsSrv, [ref] $cvsCon)` as the message implies that `$csvSrv` is never assigned any value. – HAL9256 Oct 03 '19 at 15:00
  • @AdminOfThings there is nothing inside ```$cvsSrv.Reports```, I get error "Get-Member : You must specify an object for the Get-Member cmdlet." – Jarek Szczyg Oct 03 '19 at 15:07
  • @HAL9256 You might be on to something because I noticed that when I assign ```$cvsSrv = New-Object -ComObject "ACSUPSRV.cvsServer"``` a process "acsSRV.exe" shows up on my process list. Then a second one shows up when it gets to the line ```$cvsApp.CreateServer``` – Jarek Szczyg Oct 03 '19 at 15:08
  • @HAL9256 the "$cvsSrv" works now, thank you. Although, I get error on ```$cvsSrv.Reports.CreateReport($Info,$Rep)```. The error message says ```Invalid procedure call or argument``` but at least I'm getting somewhere. – Jarek Szczyg Oct 03 '19 at 15:17
  • Great! It's the same issue. See my answer below for more explanation. – HAL9256 Oct 03 '19 at 23:50

3 Answers3

0

The message implies that $csvSrv is never assigned any value

When you create the COM objects they are empty. When you call the function, and pass it the variables in as parameters, you have to pass the variables by reference in order for the function to take the object and fill it. i.e.

$cvsApp = New-Object -ComObject "ACSUP.cvsApplication"
$cvsCon = New-Object -ComObject "ACSCN.cvsConnection"
$cvsSrv = New-Object -ComObject "ACSUPSRV.cvsServer"
$Rep = New-Object -ComObject "ACSREP.cvsReport"

...

$cvsApp.CreateServer("login", "password", "", "server", $False, "ENU", [ref] $cvsSrv, [ref] $cvsCon)

And similarly, (from comments) you will have to do the same with the $Rep object:

...

$Info = $cvsSrv.Reports.Reports("Historical\Designer\Call Records SL")

...

If($cvsSrv.Reports.CreateReport($Info, [ref] $Rep)){
    $Rep.TimeZone = "default"
    ...

Notice, in this case you don't have pass $Info by reference. This is because you are directly assigning the return value from the function to the $Info variable. But for $Rep since it is being passed as a parameter, you will have to pass it by [ref] in order to use it.

HAL9256
  • 12,384
  • 1
  • 34
  • 46
  • It is still giving me ```Invalid procedure call or argument``` error. I will go back to the original code as I skipped the "csvCatalog" object thinking I don't need it but maybe I do? – Jarek Szczyg Oct 04 '19 at 08:24
  • I think the problem is this referencing. I'm creating objects at the beginning of my query but in the VBA only one Object is created ``Set cvsApp = CreateObject("acsup.cvsApplication")`` can you assign a Comobject to a variable without creating object for it in powershell? Because even when I specify ``CreateReport($Info, [ref] $Rep)``, when I look in process manager a new instance of ``acsRep.exe`` shows up for a second. – Jarek Szczyg Oct 04 '19 at 13:02
  • Typically you only create the one object (e.g. `cvsApp`) and do all your work from there. You are right, by doing `New-Object -ComObject ...` you are creating a new COM object instance, not defining a variable type. You might get around it by instead passing empty generic "objects" by reference, and they may come back as the proper "acsup.x" object. – HAL9256 Oct 04 '19 at 14:52
0

After many trials and errors, I couldn't make it work so I gave up and tried a different approach. Here's the entire script that I run to upload data from Avaya to SQL Server using PowerShell:

if(Get-Module -ListAvailable -Name SqlServer) {} else {Install-Module -Name SqlServer -AllowClobber -Confirm:$False -Force} #installs SqlServer module if not installed

function Get-CallRecordDates {
Invoke-Sqlcmd -Query “SELECT [Date],[Time],[Commentary] FROM [CallRecords].[dbo].[ForSQLload] order by [Date],[Time]” -ServerInstance "myserver"
}

$dates = @(Get-CallRecordDates)

#declare where the csv files will be saved
$csv = "G:\SQLDataLoad\Avaya"

#declare where the .acsauto file will be saved
$acsauto = "G:\SQLDataLoad\Avaya\CallRecordsJS.acsauto"

#These will format the acsauto file just right
$blockstart = "'LANGUAGE=ENU
'SERVERNAME=AvayaServerAddress
Public Sub Main()

On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports(`"Historical\Designer\Call Records SL`")

If Info Is not Nothing Then
"

$blockend = "If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing

End If
Set Info = Nothing

End Sub"

#Start creating the content for acsauto file
$contentacsauto = $blockstart

#looping through the missing records. If there are none, it will only download the latest data.
for($i=0; $i -lt $dates.Count; $i++)
{
    $maindate = Get-Date "$(Get-Date $dates.Item($i).Date -Format 'dd/MM/yyyy') $(Get-Date $dates.Item($i).Time -Format 'HH:mm')"

    if($dates.Item($i).Commentary -eq "Last"){
        $startdatetime = $maindate.AddHours(-3)
        $enddatetime = Get-Date
        Write-Host "Adding time range to the script; from $startdatetime to $enddatetime `nIt will be exported to $csv\CallRecords-$i.csv`nTime is $(Get-Date) now."
        }else{
        $startdatetime = $maindate.AddHours(-1)
        $enddatetime = $maindate.AddHours(1)
        Write-Host "Adding time range to the script as some CallID is missing in that period; from $startdatetime to $enddatetime `nIt will be exported to $csv\CallRecords-$i.csv"
    }

    #formating the dates and times for the parameters
    $startdate = Get-Date $startdatetime -Format "dd/MM/yyyy"
    $starttime = Get-Date $startdatetime -Format "HH:mm"
    $enddate = Get-Date $enddatetime -Format "dd/MM/yyyy"
    $endtime = Get-Date $enddatetime -Format "HH:mm"

    $MiddleBlock = "
If cvsSrv.Reports.CreateReport(Info,Rep) Then
Rep.TimeZone = `"default`"
Rep.SetProperty `"Start Date`",`"$startdate`"
Rep.SetProperty `"Start Time`",`"$starttime`"
Rep.SetProperty `"Stop Date`",`"$enddate`"
Rep.SetProperty `"Stop Time`",`"$endtime`"
b = Rep.ExportData(`"$csv\CallRecords-$i.csv`", 44, 0, True, True, True)
Rep.Quit
End If
"

    $contentacsauto = $contentacsauto + $MiddleBlock
}

$contentacsauto = $contentacsauto + $blockend

$contentacsauto | Out-File $acsauto -Encoding ascii

& $acsauto

$stop = (Get-Date).AddMinutes(10)

while (((Get-ChildItem $csv -Filter "CallRecord*.csv").Count -lt $i) -and ($(Get-Date) -lt $stop)) {Start-Sleep -Seconds 2}

if($(Get-Date) -ge $stop) {
    Write-Host "This thing timed out as it took longer than 10 minutes to export, at $(Get-Date)"
    Get-Process "acs*" | Stop-Process -PassThru | Out-Null}
else{
Write-Host "Saved $((Get-ChildItem $csv -Filter "CallRecord*.csv").Count) file(s) at $(Get-Date)"

Start-Sleep -Seconds 2

$csvfiles = Get-ChildItem $csv -Filter "CallRecord*.csv"

$callrecords = Import-Csv -Path $csvfiles | Sort-Object 'Call ID','Segment' -Unique

Remove-Item $csvfiles -Force | Out-Null

Invoke-Sqlcmd -Query "delete from CallRecords.dbo.TempCallDataTemp" -ServerInstance "myserver"

Write-SqlTableData -InputData $callrecords -ServerInstance "myserver" -DatabaseName "CallRecords" -SchemaName "dbo" -TableName "TempCallDataTemp" -force
}

Basically I create a "CallRecords.acsauto" file that I then run, wait for all of the files to be ready (with a check, in case it froze for 10 minutes it shuts it down), import them and upload to sql server. I then call a bunch of sql commands to format it and copy it to the main table.

Jarek Szczyg
  • 33
  • 2
  • 8
0

After hours - this is working:

#APPLICATION
$cvsApp = New-Object -ComObject "ACSUP.cvsApplication"
$cvsApp.CreateServer( 'login' , 'pass' , '' , 'address' , $FALSE , 'ENU' , $NULL )
$cvsApp.Servers.Item( 1 ).Reports.ACD = 1

#REPORT
$cvsInfo = $cvsApp.Servers.Item( 1 ).Reports.Reports.Item('path\to\report\in\cms')
$cvsRep = $NULL
$cvsApp.Servers.Item( 1 ).Reports.CreateReport( [ref]$cvsInfo , [ref]$cvsRep )
$cvsRep.TimeZone = "default"
$cvsRep.SetProperty( "Date" , "2022-09-20" )
$cvsRep.ExportData( "C:\test.txt" , 124 , 0 , $TRUE , $FALSE , $TRUE )

#DESTROYERS
$cvsApp.Servers.Item( 1 ).ActiveTasks.Remove( $cvsRep.TaskID )
$cvsApp.Servers.Remove( $cvsApp.Servers.Item( 1 ).ServerKey )
[System.Runtime.Interopservices.Marshal]::ReleaseComObject( $cvsInfo )
Remove-Variable 'cvsInfo'
[System.Runtime.Interopservices.Marshal]::ReleaseComObject( $cvsRep )
Remove-Variable 'cvsRep'
[System.Runtime.Interopservices.Marshal]::ReleaseComObject( $cvsApp )
Remove-Variable 'cvsApp'

The code may be prettier, but I wanted to share the main idea. Exception and error handlers are welcome too.

user3725657
  • 29
  • 5