0

I have an avaya CMS script that is setup to extract team data so it can be imported into Power BI. The script is working fine, but I have to go in and manually edit the file in Notepad ++ each month so that it exports to a new file name and gets the correct dates reporting. I am trying to write a Powershell script that will automatically update the relevant information in the Avaya script and run the Avaya script to output my data. The powershell script that I wrote appears to work, it outputs an Avaya script that matches my original working script exactly with every character matching when viewed in notepad++. However the script that is output won't run in Avaya. Also I noticed that the file size is 6KB when the original is 3KB. But when viewed in Notepad there is not a single difference. Is something in my powershell script breaking the Avaya script? Putting the scripts below for reference.

Power Shell Script:

$content = Get-Content -path "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\power bi export file test.acsauto" -Raw
$month = get-date -format "MM"
$day = get-date -format "dd"
$year = get-date -format "yyyy"
$today = get-date
$lastday = [DateTime]::DaysInMonth($today.year, $today.Month)
$datesstart = $content.LastIndexof("Dates")
$datesstart = $datesstart +8
$datessub = $content.Substring($datesstart,21)
$quote = write-output '"'
$datesnew = Write-Output "$month/1/$year-$month/$lastday/$year$quote"
$content.replace($datessub,$datesnew) > "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\powershell scripting target.acsauto"
$content = Get-Content -path "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\powershell scripting target.acsauto" -Raw
$pathstart = $content.lastindexof("Stats\")
$pathstart = $pathstart +6
$pathsub  = $content.substring($pathstart,7)
$pathnew = write-output "$month-$year"
$content.replace($pathsub,$pathnew) > "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\powershell scripting target.acsauto"
$retro=(get-date).date.addmonths(-1)
$lastmonth = $retro.tostring("MM")
$lastmonthyear = $retro.year
$content = Get-Content -path "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\powershell scripting target.acsauto" -Raw
$lmpathnew = write-output "$lastmonth-$lastmonthyear"

$endoflastmonth = (Get-Date -Day 1).AddDays(-1).ToString("dd")
$lmdatenew = write-Output "$lastmonth/1/$lastmonthyear-$lastmonth/$endoflastmonth/$lastmonthyear"
$content.replace($pathnew,$lmpathnew).replace($datesnew,$lmdatenew) > "C:\Users\MM24363\OneDrive - MassMutual\QA\Tools scripts etc\last month.acsauto"

Avaya Script

'LANGUAGE=ENU
'SERVERNAME=170.6.243.18
Public Sub Main()

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: Field Group Daily Summary: Export Data"
'## Parameters.Add "Report: Historical: Designer: Field Group Daily Summary: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\Field Group Daily Summary","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "-180","_Top"
'## Parameters.Add "255","_Left"
'## Parameters.Add "28335","_Width"
'## Parameters.Add "16575","_Height"
'## Parameters.Add "default","_TimeZone"
'## Parameters.Add "The report Historical\Designer\Field Group Daily Summary was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "Erroll Team","Agent Group"
'## Parameters.Add "6/1/2022-6/30/2022","Dates"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "C:\Users\mm24363\OneDrive - MassMutual\QA\Avaya Reportin\june export.csv","_Output"
'## Parameters.Add "9","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "True","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "False","_DurSecs"

   On Error Resume Next

   cvsSrv.Reports.ACD = 1
   Set Info = cvsSrv.Reports.Reports("Historical\Designer\Field Group Daily Summary")

   If Info Is Nothing Then
      If cvsSrv.Interactive Then
          MsgBox "The report Historical\Designer\Field Group Daily Summary was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
      Else
          Set Log = CreateObject("ACSERR.cvsLog") 
          Log.AutoLogWrite "The report Historical\Designer\Field Group Daily Summary was not found on ACD 1."
          Set Log = Nothing
      End If
   Else

       b = cvsSrv.Reports.CreateReport(Info,Rep)
       If b Then
    
          Rep.Window.Top = -180
          Rep.Window.Left = 255
          Rep.Window.Width = 28335
          Rep.Window.Height = 16575        
    

                        Rep.TimeZone = "default"


    
          Rep.SetProperty "Agent Group","Erroll Team"
    
          Rep.SetProperty "Dates","07/1/2022-07/31/2022"
    
    
    

          b = Rep.ExportData("C:\Users\mm24363\OneDrive - MassMutual\Brians Folder\Field Individual Avaya Stats\07-2022 export.csv", 9, 0, True, True, False)

    

    

          Rep.Quit

    

              If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
          Set Rep = Nothing
       End If

   End If
   Set Info = Nothing
'## cvs_cmd_end

End Sub
Zoidberg
  • 3
  • 2
  • My first thought is that it is an encoding issue. You can check [this page](https://community.idera.com/database-tools/powershell/powertips/b/tips/posts/get-text-file-encoding) for a function that will tell you what the encoding of the file is. Then rather than just `> file.txt` you can use `Set-Content file.txt -encoding ` to use the same encoding. Should probably use the right encoding when reading the file too. – TheMadTechnician Jul 25 '22 at 21:49
  • The Notepad++ Encoding menu is another way to see what the encoding of the original working file is. – Tom Jul 25 '22 at 22:01
  • @TheMadTechnician thank you, this was the issue. Powershell had no issue reading the file into a string so it never occurred to me there might be an encoding issue. The working script was using UTF-8 but powershell was outputting in UTF-16 LE BOM. I added a line at the start of the script to change the default to UTF-8 and now the output scripts run as expected. Thank you. – Zoidberg Jul 26 '22 at 11:34

3 Answers3

0

Without running your script, I'd suggest your back out of the variables .setProperty one at a time until you find what is breaking the code. I've found that date (date format or date as a string) will break the scripts.

Charles
  • 1
0

Try modifying the standard acsauto file and run it in a'cmd' file as a scheduled task after midnight to get yesterday's data. I've been doing it this way for years. I use Escel VBA to import the output txt file into Excel.

Here's what I do:

  1. declaring variables even though no option explicit

Public Sub Main() dim yyyy, mm, dd, file1, file2, rpt, dpath1, dpath2, msg, stdt

On Error Resume Next

'**************

  1. Insert this type code after 'on error resume next' to formulate paths and yesterday's date-named output files

'hard-code output files' paths

dpath1 ="yourpath1"

dpath2="yourpath2"

'************************ ' formulate the date name for yesterday

dy=-1 'yesterday

stdt=dateadd("d",dy,Date)

yyyy=cstr(year(stdt))

mm=cstr(month(stdt))

if len(mm)=1 then mm="0" & mm

dd=cstr(day(stdt))

if len(dd)=1 then dd="0" & dd

file1=dpath1 & "spsk_1-99_" & yyyy & mm & dd & ".txt"

file2=dpath2 & "spsk_1-99_" & yyyy & mm & dd & ".txt"

'msgbox file1 'un-comment when testing

'endsub 'un-comment when testing so you don't get cms mad

'************************

  1. report stuff for yesterday's date = dy

       Rep.SetProperty "Dates", dy

'

'pulling data for multiple skills "1-99" for hours 00:00-23:30

      Rep.SetProperty "Splits/Skills","1-99"

'
Rep.SetProperty "Times","00:00-23:30" '
Rep.ReportView.Add "G0,0,0;0,2,0","Grid1" '
'sending output as text to two files, tab delimited, no quotes and time durations as seconds

      b = Rep.ExportData(file1, 9, 0, True, True, True)

' b = Rep.ExportData(file2, 9, 0, True, True, True)

      Rep.Quit

' If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID

John O'Brien
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 18 '22 at 03:59
0

@TheMadTechnician thank you, this was the issue. Powershell had no issue reading the file into a string so it never occurred to me there might be an encoding issue. The working script was using UTF-8 but powershell was outputting in UTF-16 LE BOM. I added a line at the start of the script to change the default to UTF-8 and now the output scripts run as expected. Thank you.

Zoidberg
  • 3
  • 2