asked on

In a table of ip addresses, return a count if ips by netblock

I have a large number of ip addresses (almost 2 million) in a table. It's easy to return a count of the 10 most frequently appearing ips, e.g.:

select distinct top 10 ip, count(ip) from dbo.mytable
group by ip
order by count(ip) desc

However, what I'm more interested in is returning a count of the 10 most frequently appearing netblocks. Does anyone have any tips/suggestions as to how I should proceed?

Thanks in advance!

chapmandew

I'm probably missing something...but are you looking for something like this?

select distinct top 10 ip, netblock, count(ip) from dbo.mytable
group by ip, netblock
order by count(ip) desc

bashka_abdyli

select distinct top 10 ip, count(ip) from dbo.mytable
where something = 'netblocks'
group by ip
order by count(ip) desc

hennessym

ASKER

Sorry, I didn't explain my question adequately. The table does not contain netblock information - only ip addresses. So I guess I'd probably need to first convert each ip to its netblock and then get a count.

Perhaps the question should have read: does anyone have a procedure or function for converting an ip address to its netblock?

bashka_abdyli

what is a netblock ? provide a sample

ASKER CERTIFIED SOLUTION

chapmandew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION