Hi all,
I got a strange issue that i cannot get CDR report by running the powershell script after patching CUCM from 11.5.1 su2 to su7. The script worked fine before the upgrade and CAR services are running. I can get CDR data using the username that i used for exporting the CDR report. Please take a look at the following script and the error message. Any help would be appreciated.
Here is the script:
Start-Transcript -Path H:\HelpDeskMissedCallStats\Output.txt
Function notify()
{
param([string]$totalCalls, [string]$totalMissedCalls, [string]$totalAnsweredCalls, $from, $to, [string]$totalOnCallMissedCalls, [string]$totalOnCallCalls, [string]$totalOnCallAnsweredCalls)
$emailAddress = "x@x"
$Email = New-Object System.Net.Mail.MailMessage
$Email.From = "x@x"
$Email.To.Add($emailAddress)
$Email.To.Add($emailAddress1)
$Email.To.Add($emailAddress2)
$Email.To.Add($emailAddress4)
$Email.IsBodyHtml = $true
$Email.Body += "<h3>Help Desk:</h3>"
$Email.Body += "<p>Total Calls: $totalCalls </p>"
$Email.Body += "<p>Total Missed: $totalMissedCalls <p>"
$Email.Body += "<p>Total Answered: $totalAnsweredCalls </p>"
$Email.Body += "<h3>On Call:</h3>"
$Email.Body += "<p>Total Attempted Reaches: $totalOnCallCalls </p>"
$Email.Body += "<p>Total Missed or Rejected: $totalOnCallMissedCalls <p>"
$Email.Body += "<p>Total Answered / Taken: $totalOnCallAnsweredCalls </p>"
$Email.Subject = "IT Call Statistics - $from - $to"
$smtpServer = 'xxx'
$smtpObject = New-Object Net.Mail.SmtpClient($smtpServer)
$smtpObject.Send($Email)
}
$ie1 = new-object -ComObject "InternetExplorer.Application"
$ie1.Visible = $true
$RetrievePage1 = "https://cucm.x.x:8443/car/"
$ie1.navigate($RetrievePage1)
while($ie1.Busy){ Start-Sleep -Milliseconds 5000 }
$ie1.quit()
$ie = new-object -ComObject "InternetExplorer.Application"
$ie.Visible = $true
$RetrievePage = "https://cucm.x.x:8443/car/"
$ie.navigate($RetrievePage)
while($ie.Busy){ Start-Sleep -Milliseconds 5000 }
$lastweek1 = (Get-Date).AddDays(-7)
$lastweek2 = Get-Date
$fromDate = $lastweek1.ToString("MM dd yyyy")
$toDate = $lastweek2.ToString("MM dd yyyy")
$f_split = $fromDate.split()
$t_split = $toDate.split()
$output = "H:\HelpDeskMissedCallStats\CallData" + $fromDate + " - " + $toDate + ".txt"
#right here there is a chance the authentication with CUCM fails.
# need to close IE the first time, then re-open. Manual testing revealed
#the second run always works after the URL has been opened once.
$links = $ie.Document.IHTMLDocument3_getElementsByTagName("a")
foreach($link in $links)
{
#Write-Output($link.innerText)
if($link.innerText -like "*Export CDR/CMR*")
{
Write-Output($links.innerText)
$submit = $link
}
}
Start-Sleep -Milliseconds 5000
$submit.Click()
while($ie.Busy){ Start-Sleep -Milliseconds 5000 }
$tags = $ie.Document.IHTMLDocument3_getElementsByTagName("select")
#this sections sets up some of the date ranges
foreach($tag in $tags)
{
if($tag.Name -eq "cboMonthFrom")
{
$tag.Value = $f_split[0]
}
if($tag.Name -eq "cboMonthTo")
{
$tag.Value = $t_split[0]
}
if($tag.Name -eq "cboYearFrom")
{
$tag.Value = $f_split[2]
}
if($tag.Name -eq "cboYearTo")
{
$tag.Value = $t_split[2]
}
}
$tags = $ie.Document.IHTMLDocument3_getElementsByTagName("input")
#this section sets up more date ranges and preferences for the export and requests it.
foreach ($tag in $tags)
{
#sets from date day
if($tag.Name -eq "txtDateFrom")
{
$tag.Value = $f_split[1]
}
#sets to date day
if($tag.Name -eq "txtDateTo")
{
$tag.Value = $t_split[1]
}
#uncheck cmr records
if ($tag.Name -eq "chkCmr")
{
$tag.Click()
}
#submit the request
if ($tag.Name -eq "cmdExportDump")
{
Start-Sleep -Milliseconds 10000
$tag.Click()
}
}
while($ie.Busy){ Start-Sleep -Milliseconds 100000 }
$tags = $ie.Document.IHTMLDocument3_getElementsByTagName("a")
#finds the CDR dump, saves it to a file in the same directory as the script
foreach ($tag in $tags)
{
if($tag.innerText -eq "CDR Dump")
{
$url = $tag.href
Invoke-WebRequest -Uri $url -OutFile $output
}
}
$lines = Import-Csv $output
[int]$total = 0
[int]$missed = 0
[int]$CallOuttotal = 0
[int]$CallOutmissed = 0
foreach($line in $lines)
{
[string]$calledNumber = $line.("originalCalledPartyNumber")
[string]$userID = $line.("finalCalledPartyUnicodeLoginUserID")
[string]$callOutline = $line.("destMobileDeviceName")
$duration = $line.("destMobileCallDuration")
if($callOutline -like "*CRC IT - Call Out*")
{
if($duration -eq 0)
{
$CallOutmissed = $CallOutmissed + 1
}
$CallOuttotal = $CallOuttotal + 1
}
[int]$CallOutanswered = $CallOuttotal - $CallOutmissed
if($calledNumber -like "*7194727")
{
if($userID -like "\ ")
{
$missed = $missed + 1
}
if($userID -like "")
{
$missed = $missed + 1
}
$total = $total + 1
}
[int]$answered = $total - $missed
}
$answered = $answered - $CallOutanswered
$missed = $missed - $CallOutmissed
$total = $total - $CallOuttotal
notify $total $missed $answered $fromDate $todate $CallOutmissed $CallOutTotal $CallOutanswered
$ie.quit()
Stop-Transcript
Error that is produced:
Windows PowerShell transcript start
Start time: 20200429090103
Username: CRC\CUCM.SCRIPT.CallStat
RunAs User: CRC\CUCM.SCRIPT.CallStat
Machine: XXX (Microsoft Windows NT 10.0.14393.0)
Host Application: C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.EXE .\Numbers.ps1
Process ID: 17908
PSVersion: 5.1.14393.3053
PSEdition: Desktop
PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.14393.3053
BuildVersion: 10.0.14393.3053
CLRVersion: 4.0.30319.42000
WSManStackVersion: 3.0
PSRemotingProtocolVersion: 2.3
SerializationVersion: 1.1.0.1
**********************
Transcript started, output file is H:\HelpDeskMissedCallStats\Output.txt
You cannot call a method on a null-valued expression.
At H:\HelpDeskMissedCallStats\Numbers.ps1:99 char:1
+ $submit.Click()
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At H:\HelpDeskMissedCallStats\Numbers.ps1:99 char:1
+ $submit.Click()
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
>> TerminatingError(Import-Csv): "Could not find file 'H:\HelpDeskMissedCallStats\CallData04 22 2020 - 04 29 2020.txt'."
Import-Csv : Could not find file 'H:\HelpDeskMissedCallStats\CallData04 22 2020 - 04 29 2020.txt'.
At H:\HelpDeskMissedCallStats\Numbers.ps1:190 char:10
+ $lines = Import-Csv $output
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand
Import-Csv : Could not find file 'H:\HelpDeskMissedCallStats\CallData04 22 2020 - 04 29 2020.txt'.
At H:\HelpDeskMissedCallStats\Numbers.ps1:190 char:10
+ $lines = Import-Csv $output
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand
**********************
Windows PowerShell transcript end
End time: 20200429090134
