November 14, 2005 at 7:51 am
I'm thinking through this solution like this:
"Select the most active DestinationIP (host) that recieves greater than 100 unlike (non-matching) ports hits within 1 minute of time."
This would be a port scan.
Thanks for you patience..........
November 15, 2005 at 7:50 am
Kevin,
Because this is a SQL server board, I assume there is a table of some sort you are querying? Please provide DDL and sample data for the table.
Thanks
Wayne
November 15, 2005 at 8:29 am
Yes, I had the SQL info before I messed it up during my last message "Edit" here.
tbl_Records -> col_DateTime col_DestIP col_SrcIP col_Port
I'm looking for the most active (1 minute or less) col_DestIP (host) and the col_Port (ports) accessed and ofcourse the col_SrcIp (hacker IP) that made the attempt.
December 7, 2005 at 4:01 pm
Create a table (results) to capture your results. I'm assuming you would be checking a time period. My example checks every minute starting midnight of 10/1/2005 and ending the minute before midnight 10/2. Since systems like this probably have 100s of millions of records to sift through, make sure your date column is indexed. The looping of this method will also keep you from putting a lock on your entire table at once as it will only be looking at a minute worth of data at a time.
The update statement at the end of the while loop will tell you the start time of the minute you are analyzing.
If any of this is incorrect I apologize as I just whipped it together real quick.
create table results
(
col_DestIP varchar(20),
col_port int,
col_srcip varchar(20),
start_minute datetime
)
declare @date datetime
select @date = '10/1/2005 00:00:00.000'
while @date < '10/2/2005'
begin
insert into results
select col_DestIP, col_Port, col_SrcIP, null
from tbl_Records
where col_DestIP in (select col_destip
from tbl_records
where col_datetime >= @date
and col_datetime < dateadd(mi, 1, @date)
group by col_destip
having count(distinct col_port) > 99)
and col_datetime >= @date
and col_datetime < dateadd(mi, 1, @date)
update results
set start_minute = @date
where start_minute is null
select @date = dateadd(mi, 1, @date)
end
December 7, 2005 at 8:36 pm
This solution uses each hit as a starting point to look at the next one minute of time for hits on different ports to the same host and source.
This could be written as a Single SQL statement, but as the #HighHitHosts virutal table would need to be derived twice, a temporary table is probably a better solution.
See the post "Join from temp table" for when using a temporary table can increase performance at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=241763#bm241772
Create Table HostPortHits
(DestinationIp varchar(15)NOT NULL -- Longest value 123.456.789.012
,SourceIp varchar(15)NOT NULL
,HitTsdatetimenot null
,HitPort integernot null
)
go
-- Generate ~12K rows
Insert into HostPortHits
(DestinationIp ,HitTs ,HitPort,SourceIp)
select Hosts.IPAddress
,DATEADD(ss, sequences.seq , current_timestamp)
,CAST( RAND(seq) * 100 as integer) -- ports 0 thru 99
,Source.ip
from (select '192.168.1.1' union all select '192.168.1.2') as Hosts (IPAddress)
, (select '66.94.234.13' union all -- yahoo.com
select '207.46.130.108' union all -- microsoft.com
select '64.78.230.102' -- sqlservercentral.com
) as Source (ip)
,master.dbo.sequences as sequences
where sequences.seq between 0 and 2000
go
Alter table HostPortHits
add constraint HostPortHits_P primary key (DestinationIp, SourceIp, HitTs, HitPort)
go
-- Get the Host with more than 100 hits from the same Source to Different Ports
select StartScan.DestinationIp
, StartScan.SourceIp
,Count(*) as HitCount
INTO#HighHitHosts
FROMHostPortHits as StartScan
JOINHostPortHits as EndScan
ONEndScan.DestinationIp= StartScan.DestinationIp
ANDEndScan.SourceIp= StartScan.SourceIp
and EndScan.HitPort != StartScan.HitPort -- Different Port
andEndScan.HitTs
between StartScan.HitTs
and DATEADD(mi, 1 , StartScan.HitTs ) -- add 1 minute
GROUP BY StartScan.DestinationIp
, StartScan.SourceIp
HAVING Count(*) > 100
select#HighHitHosts.*
from #HighHitHosts
JOIN(selectMAX(HitCount)
FROM #HighHitHosts
) as HWM (HitCount)
on #HighHitHosts.HitCount = HWM.HitCount
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply