IP Address Grouping

[CRoompa]

CRXI with Oracle Database

I would like to create a report grouping on the IP address.
If have this data: 10.10.10.1, 10.10.10.2, 10.10.10.3, how can I just have it group them all as 10.10.10?
This will need to work whether there is 1-3 numbers between the periods.
Appreciate any help.

 

[raja2008]

If the format always same then you can do this

create a formula :
Right(table.ipaddress,8)

then group baesd on this formula.

Insert-->Group-->From the list of fields pick the Formula

 

[CRoompa]

Sometimes the IP address may be: 101.110.5.123 another time it may be 10.120.110.4
Either way, I want to basically group everything together if the first 3 numbers are the same, and ignore the number after the three decimal...

 

[raja2008]

do you mean 101.110.5.123 and 10.120.110.4 these two will be in the same group since the first three numbers are 101

 

[CRoompa]

No, I am just saying there would be a variety in the number of digits between the periods, depending on the IP Address.

If the IP address looks like these:
101.110.5.123, 101.110.5.21, 101.110.5.35
should be grouped as 101.110.5

If the IP address looks like these:
10.120.110.123 10.120.110.1 10.120.110.75 10.120.110.45
Then it should be grouped as 10.120.110

If the IP address looks like this:
192.168.120.120 192.168.120.1 192.168.120.25
then they should be rolled up to 192.168.120

If the IP address looks like these:
10.10.10.5 10.10.10.35 10.10.1.75
then 10.10.10 would be group with 2 instances of that address.

Does that clarify?

 

[CRoompa]

Actually, I just figured out what I wanted using this:

Left({Table.IPAddress}, InStrRev({Table.IPAddress},".")) +"X"

Thanks raja2008, you got me started down the right path.