04-20-2009 08:24 AM - edited 03-18-2019 10:54 PM
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.
04-22-2009 04:12 AM
i would start here
http://office.microsoft.com/en-gb/excel/HP052091111033.aspx
09-22-2010 04:35 PM
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
09-23-2010 01:15 AM
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 BEGINDECLARE @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...
09-24-2010 11:35 AM
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
03-09-2016 07:14 AM
If you need to use this formula in French, you just have to replace HEX2DEC to HEXDEC , DEC2HEX to DECHEX and MID to STXT
02-28-2013 08:17 AM
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
11-30-2015 02:09 PM
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)
03-21-2016 12:09 PM
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))
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide