I came across a task of importing my subscriber's list to the autoresponder
service. Some of my subscriber's data didn't contain ip address, simply because at the time of collection, the thought of its necessity didn't cross my mind so I decided to populate the list with fake IP addresses. Of course, the task was to make these addresses as little "fake" as possible, meaning they had to be ip address from the Real US range.
Since it took some time and effort to figure out how exactly accomplishes it, I thought it would be a good idea to post it, in case someone else might come across the same need. Now I am not claiming that the method I used is absolutely the best one out there, but I think it will be a good place to start.
First I looked for a list of US ip ranges. I found one and I am attaching it with this post as a text file. Now if you look at several records from this file it structured as follow:
60.254.128.0/18
163.60.0.0/16
192.103.43.0/24
202.72.96.0/20
202.76.240.0/21
203.77.184.0/21
203.144.48.0/20
203.187.128.0/19
Here the construct before the slash represents an ip address, while as the number after slash represents the variation of the last digit in IP address. In the example of the first records, this means that the entire range of possible IPs is the following:
60.254.128.0/60.254.128.1/60.254.128.2/60.254.128.3/60.254.128.4/60.254.128.5/
60.254.128.6/60.254.128.7 and so on until it reaches 60.254.128.18
So I imported this text file into Sequel table that I called US_IP_ZONE with the following structure:
CREATE TABLE [dbo].[Us_ip_zone](
[ip_start] [varchar](20) NULL,
[ip_end] [int] NULL,
[done] [bit] NOT NULL CONSTRAINT [DF_Us_ip_zone_done] DEFAULT ((0))
) ON [PRIMARY]
Here the field [ip_start] stored ip address while [ip_end] stored the variation digit. The third [done] had to do with subsequent scan and I will touch up on it later.
Now, surly I didn't want to poulate my table with all the ips from the same record sequentually. This would siounds suspicious. Instead decided to take a random number from 0 to 18 (again I am taking the first record as an example) and construct the last digit of ip address based on this selection. For istance if the randome number would be 7 the resulting ip address will be 60.254.128.7, if it would be 12 then 60.254.128.12, if 15 then 60.254.128.15 etc.
In terms of sequel statement this objective is achieved with the following query:
select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0)) ,@ip_start = ip_start
from
(
select top 1 *
from us_ip_zone where done = 0
) t
Here the internal select separates a top record from the [US_IP_ZONE] table. The external select cuts out the existing address up to the last period and replaces the last digit with the number randomly selected from range. The @ip_start variable populated in order to update the update this record as "used" or "done" in this loop, so that next internal select will give me the next record. Here's how the entire script looks like...
declare @ID int
declare @ip_address varchar(15)
declare @ip_start varchar(15)
declare @found bit
declare cr cursor for
select id from emails_no_ip
where recepient_IP is null
open cr
fetch next from cr into @ID
while @@fetch_status = 0
begin
select @found = 0
while @found = 0
begin
select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0))
,@ip_start = ip_start
from (
select top 1 *
from us_ip_zone where done = 0
) t
if exists(select 1 from emails_exported where recepient_IP = @ip_Address)
CONTINUE
else
begin
if exists(select 1 from emails_no_ip where recepient_IP = @ip_Address)
CONTINUE
else
begin
update emails_no_ip set recepient_IP = @ip_Address where ID = @ID
break
select @found = 1
end
end
end
update us_ip_zone set done = 1 where ip_start = @ip_start
fetch next from cr into @ID
end
close cr
deallocate cr
As you can see first I am generating the perspective ip, then I check whether this ip exists among real collected ips in the table [emails_exported]. Then I check whether the perspective ip exists in the table that I attempt to populate [emails_no_ip] to make sure that this ip hasn't been used before. Now in case either table already contains the ip I loop again and generate another perspective ip from the same source record, until the ip is indeed unique. Once uniqueness of perspective IP is ensured the scrip updates the [recepient_IP] field in the target table.
The last statement of the loop updates the current record from [US_IP_ZONE] table so that next loop will have a different source IP record to deal with.
That's all folks.
service. Some of my subscriber's data didn't contain ip address, simply because at the time of collection, the thought of its necessity didn't cross my mind so I decided to populate the list with fake IP addresses. Of course, the task was to make these addresses as little "fake" as possible, meaning they had to be ip address from the Real US range.
Since it took some time and effort to figure out how exactly accomplishes it, I thought it would be a good idea to post it, in case someone else might come across the same need. Now I am not claiming that the method I used is absolutely the best one out there, but I think it will be a good place to start.
First I looked for a list of US ip ranges. I found one and I am attaching it with this post as a text file. Now if you look at several records from this file it structured as follow:
60.254.128.0/18
163.60.0.0/16
192.103.43.0/24
202.72.96.0/20
202.76.240.0/21
203.77.184.0/21
203.144.48.0/20
203.187.128.0/19
Here the construct before the slash represents an ip address, while as the number after slash represents the variation of the last digit in IP address. In the example of the first records, this means that the entire range of possible IPs is the following:
60.254.128.0/60.254.128.1/60.254.128.2/60.254.128.3/60.254.128.4/60.254.128.5/
60.254.128.6/60.254.128.7 and so on until it reaches 60.254.128.18
So I imported this text file into Sequel table that I called US_IP_ZONE with the following structure:
CREATE TABLE [dbo].[Us_ip_zone](
[ip_start] [varchar](20) NULL,
[ip_end] [int] NULL,
[done] [bit] NOT NULL CONSTRAINT [DF_Us_ip_zone_done] DEFAULT ((0))
) ON [PRIMARY]
Here the field [ip_start] stored ip address while [ip_end] stored the variation digit. The third [done] had to do with subsequent scan and I will touch up on it later.
Now, surly I didn't want to poulate my table with all the ips from the same record sequentually. This would siounds suspicious. Instead decided to take a random number from 0 to 18 (again I am taking the first record as an example) and construct the last digit of ip address based on this selection. For istance if the randome number would be 7 the resulting ip address will be 60.254.128.7, if it would be 12 then 60.254.128.12, if 15 then 60.254.128.15 etc.
In terms of sequel statement this objective is achieved with the following query:
select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0)) ,@ip_start = ip_start
from
(
select top 1 *
from us_ip_zone where done = 0
) t
Here the internal select separates a top record from the [US_IP_ZONE] table. The external select cuts out the existing address up to the last period and replaces the last digit with the number randomly selected from range. The @ip_start variable populated in order to update the update this record as "used" or "done" in this loop, so that next internal select will give me the next record. Here's how the entire script looks like...
declare @ID int
declare @ip_address varchar(15)
declare @ip_start varchar(15)
declare @found bit
declare cr cursor for
select id from emails_no_ip
where recepient_IP is null
open cr
fetch next from cr into @ID
while @@fetch_status = 0
begin
select @found = 0
while @found = 0
begin
select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0))
,@ip_start = ip_start
from (
select top 1 *
from us_ip_zone where done = 0
) t
if exists(select 1 from emails_exported where recepient_IP = @ip_Address)
CONTINUE
else
begin
if exists(select 1 from emails_no_ip where recepient_IP = @ip_Address)
CONTINUE
else
begin
update emails_no_ip set recepient_IP = @ip_Address where ID = @ID
break
select @found = 1
end
end
end
update us_ip_zone set done = 1 where ip_start = @ip_start
fetch next from cr into @ID
end
close cr
deallocate cr
As you can see first I am generating the perspective ip, then I check whether this ip exists among real collected ips in the table [emails_exported]. Then I check whether the perspective ip exists in the table that I attempt to populate [emails_no_ip] to make sure that this ip hasn't been used before. Now in case either table already contains the ip I loop again and generate another perspective ip from the same source record, until the ip is indeed unique. Once uniqueness of perspective IP is ensured the scrip updates the [recepient_IP] field in the target table.
The last statement of the loop updates the current record from [US_IP_ZONE] table so that next loop will have a different source IP record to deal with.
That's all folks.
Last edited:
