cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
184
Views
0
Helpful
3
Replies
Highlighted
Beginner

Cannot get CDR report by running a poweshell script after CUCM upgrade from 11.5.1 SU2 to SU7

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

Everyone's tags (1)
3 REPLIES 3
Highlighted
Cisco Employee

Re: Cannot get CDR report by running a poweshell script after CUCM upgrade from 11.5.1 SU2 to SU7

It looks like the script is 'scraping' the CUCM web page for the CAR report.  This is notoriously fragile, as changes to the page (which is intended for humans to read/interpret) such as layout/element order/element naming/JS invocation/etc can cause the scraping script to fail unexpectedly - even if there are no changes to the naked eye.

Normally you would want to use a supported API for this kind of situation, as an API would be designed to be interpreted by code reliably, with considerations for backward compatibility between and across releases.  Unfortunately these CAR reports do not provide a public API...

Whoever built this script will likely need to go in and re-reverse engineer the CAR report page to account for the apparent changes in the DOM, and update this script to handle those changes.

(Note, there are supported API mechanisms for retrieving CUCM CDRs, however those provide raw/bulk call details - it can require a lot of effort/experience to write code to retrieve/store/query/analyze and generate reports from that data.  There are a number of Cisco partners who have developed very powerful CDR analysis applications - one of those may work for you out of the box, or they could possibly provide custom reports as well...)

Highlighted
Beginner

Re: Cannot get CDR report by running a poweshell script after CUCM upgrade from 11.5.1 SU2 to SU7

Can i have a solution for opening a paid Case-Based Ticket with DevNet? Thanks
Highlighted
Cisco Employee

Re: Cannot get CDR report by running a poweshell script after CUCM upgrade from 11.5.1 SU2 to SU7

As the script is not using a supported API, unfortunately Developer Support would not be able to assist.  If the person who wrote the script is not able to update it, you may need to find someone with Powershell scripting and 'front-end web development' skills who can take a look.