CDR origIpAddr to a readable format in excel

Unanswered Question
Apr 20th, 2009
User Badges:

Hi Guys,


Has anyone found a formula in excel to convert the origIpAddr in the CRD files in to a readable ip address.


I have found this


Step 1 Convert the database display (-1139627840) to a hex value.

The hex value equals 0xBC12A8C0.



Step 2 Reverse the order of the hex bytes, as shown below:

CO A8 12 BC



Step 3 Convert the four bytes from hex to decimal, as shown below:

192 168 18 188



Step 4 The IP address displays in the dotted decimal format:

192.168.18.188




From this doc http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_0_1/cdr-defs/cdrcallinfo.html#wpmkr1079974


But am hoping someone has created a formula to get around this.


  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (4 ratings)
Loading.
allan.mancera Wed, 09/22/2010 - 16:35
User Badges:

Hello guys:


     Any update on how to transform the field in a IP Address?


any update will be greatly appreciated.


Best regards


Ing. Allan O. Mancera

Aaron Harrison Thu, 09/23/2010 - 01:15
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Hi


I've previously (on Windows CCM) used this SQL function called from my queries to extract the IPs (taken from:http://markmail.org/message/eyiwyjj6zofyjlm5):


CREATE function dbo.decimalToIP(@ip INT) RETURNS VARCHAR(15) AS BEGIN 

DECLARE @binip BINARY(4) SET @binip = CONVERT(BINARY(4), at ip)

DECLARE @charip varchar(8) SET @charip = CONVERT(varchar(8), at binip)

DECLARE @1 int, @2 int, @3 int, @4 int

SET @4 = ASCII(SUBSTRING(@charip,1,1))

SET @3 = ASCII(SUBSTRING(@charip,2,1))

SET @2 = ASCII(SUBSTRING(@charip,3,1))

SET @1 = ASCII(SUBSTRING(@charip,4,1))

DECLARE @output varchar(15)

SET @output = CONVERT(varchar, at 1) + '.' + CONVERT(varchar, at 2) + '.' + CONVERT(varchar, at 3) + '.' + CONVERT(varchar, at 4) RETURN @output

END

https://10.65.0.4/cuadmin


If you are talking linux then you may be better doing it in Excel, or you may have to work some magic to get it working on Informix.


Regards


Aaron


Please rate helpful posts...

allan.mancera Fri, 09/24/2010 - 11:35
User Badges:

Hey guys:


     Working with Excel Macros in VBA I finally solved the issue with the combination of the next functions, hope this helps


Using the DEC2HEX command we obtain an HEX number which we have to reverse, so I used the next function to reverse the string:


Function ReverseText(rt As Range)

    Application.Volatile
Dim iLength As Integer
Dim iCount As Integer
Dim ipCount As Integer
Dim LValue  As String

iLength = Len(rt)
ReverseText = ""


If iLength = 7 Then     ' Validate if we have an Hex number in the type 0xXXXXXXX
    LValue = "0" & rt.Value     ' Concatenate a 0 to have an 8 digit Hex number
    iLength = Len(LValue)     ' Recalculate the length to have the 8 Hex digit number
Else
    LValue = rt.Value
End If

For iCount = iLength To 1 Step -2
    If ipCount < 4 Then
        ReverseText = ReverseText & _
        Mid(LValue, iCount - 1, 2)
        ipCount = ipCount + 1
    Else
        iCount = 1
    End If
Next iCount

End Function

After we have the reversed HEX number, I used this next function to normalize the IP address into an standard IPv4 text:


Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
  
   Application.EnableEvents = False
   For Each Cell In Target
      If Not Intersect(Target, [IPAddresses]) Is Nothing Then
         If Len(Cell) > 0 Then Cell = GetNormalizedIPAddress(Cell)
      End If
   Next Cell
   Application.EnableEvents = True

End Sub

Public Function GetNormalizedIPAddress( _
      ByVal Text As String, _
      Optional ByVal ZeroPadOctets As Long = 0 _
   ) As String
  
' Convert text to IP address. Text can be any value or an eight character
' hexidecimal number. Examples:
'
'  0 -> 0.0.0.0
'  255.10 -> 255.10.0.0
'  FFFE0001 -> 255.254.0.1
'  258.-1.0.0 -> 255.0.0.0
'
' Use the parameter ZeroPadOctets to pad each octet with zeroes. Pass a
' positive integer from 1 to 4 to pad that number of octets starting from
' the left. Pass a negative integer from -1 to -4 to pad that number of
' octets starting from the right.

   Dim Nodes As Variant
   Dim Index As Long
   Dim Result As String

   If Len(Text) > 0 Then
      Nodes = Split(Text, ".")
      If UBound(Nodes) = 0 And Len(Nodes(0)) = 8 Then
         ReDim Nodes(0 To 3)
         For Index = 0 To 3
            Nodes(Index) = CStr(CLng("&H" & Mid(Text, Index * 2 + 1, 2)))
         Next Index
      End If
      For Index = 0 To UBound(Nodes)
         If Not IsNumeric(Nodes(Index)) Then Nodes(Index) = 0
         Nodes(Index) = Application.Max(0, Application.Min(255, Nodes(Index)))
      Next Index
      Result = Join(Nodes, ".") & Left(".0.0.0", 6 - UBound(Nodes) * 2)
      Nodes = Split(Result, ".")
      If ZeroPadOctets > 0 Then
         For Index = 0 To 3
            If Index + 1 <= ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
         Next Index
      Else
         For Index = 0 To 3
            If 4 - Index <= -ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
         Next Index
      End If
      GetNormalizedIPAddress = Join(Nodes, ".")
   End If

End Function


And its done.


Best Regards


Ing. Allan O. Mancera

CCVP

Dan Schmitt Thu, 02/28/2013 - 08:17
User Badges:

If you have the comma delimited CDR file open it in excel. Take the decimal value of origIPAddr and use the following formula to convert it to an IP address.


=CONCATENATE(HEX2DEC(MID(DEC2HEX(A1+4294967296),8,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),6,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),4,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),2,2)))


That should give you what youre looking for.


Regards,

Dan Schmitt

Tim Schroeder Mon, 11/30/2015 - 14:09
User Badges:

Here's another handly formua to convert CDR fields. This one converts a dateTime field into something readable. Hopefully someone finds it useful. Tim

=(A1)/86400+DATE(1970,1,1)


kenneyhill Mon, 03/21/2016 - 12:09
User Badges:

A simpler version of a CONCATENATE method in Excel is:


=CONCATENATE(BITAND(H2, 255), ".", BITAND(ROUNDDOWN(H2 / 256, 0), 255), ".", BITAND(ROUNDDOWN(H2 / 256^2, 0), 255), ".", BITAND(ROUNDDOWN(H2 / 256^3, 0), 255))

Actions

This Discussion