cancel
Showing results for 
Search instead for 
Did you mean: 
karthago
Level 9

Web Reporter SQL database / delete IP addresses

Hi, we have Web Reporter 5.2.1.02 running with SQL Server 2012 database. Unfortunately we imported some Web Gateway access.log files which didn't need to be stored.

How can we select and delete log records from the Web Reporter database? Is it possible to select an IP adress (range) and delete the corresponding records (IDs etc.) from all tables?

0 Kudos
4 Replies
sroering
Level 13

Re: Web Reporter SQL database / delete IP addresses

Sorry, you can delete by log source or records older than a date, but you cannot delete by ip address.

Doing it manually would be problematic.  IP addresses are stored as strings and IPV6 hex format. For example, 192.168.1.0 would look like this in string format "0000:0000:0000:0000:0000:ffff:c0a8:0100"

192 = c0

168 = a8

1 = 01

0 = 00

So doing IP ranges only works if the range also happens to be alpha-numericly sequencial.

If you make a mistake and delete the wrong data, there is no undo button.  So I wouldn't try this unless you are really confident that you know what you are doing.

0 Kudos
karthago
Level 9

Re: Web Reporter SQL database / delete IP addresses

Hi, thanks for your thoughts, at the moment we are using the following SQL function to calculate ipv4 addresses from the Web Reporter database:

CREATE FUNCTION [dbo].[f_ConvertIPV6to4]

(

@AddrIPV6 varchar(50)

)

RETURNS varchar(17)

AS

BEGIN

declare @ipv4 varchar(17)

set @ipv4 = (

select

cast(convert(int, convert(varbinary, '0x' + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(':', @AddrIPV6)) - 3, 4), 1, 2), 1)) as varchar(3)) + '.' +

cast(convert(int, convert(varbinary, '0x' + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(':', @AddrIPV6)) - 3, 4), 3, 2), 1)) as varchar(3)) + '.' +

cast(convert(int, convert(varbinary, '0x' + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(':', @AddrIPV6)) + 2, 4), 1, 2), 1)) as varchar(3)) + '.' +

cast(convert(int, convert(varbinary, '0x' + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(':', @AddrIPV6)) + 2, 4), 3, 2), 1)) as varchar(3))

)

return @ipv4

END

GO

So it should be possible to filter for single ip addresses at least. My concern is that we leave unnecessary records in various tables, if we delete it just from one table (e.g. "scr_dim_ipaddress").

Could you send me the official documentation how the tables are joined?

Thanks

sroering
Level 13

Re: Web Reporter SQL database / delete IP addresses

There isn't any official documentation because modifying or quering the DB isn't supported. Here is a quick summary.

scr_fct_web = summary data

scr_fct_exact_access = detailed data

summary data is aggregated into hourly buckets. For example, you can see that jdoe when to mcafee.com 4 times during the 12 oclock hour on May 19th, but you do not know the minutes or seconds.

All other tables are dimension tables off of those two datasets. If you look at the column names, it should be obvious how they link.

A piece of warning, if you delete the wrong data, your results won't be accurate and you could cause a lot of irreversable damage.

0 Kudos
karthago
Level 9

Re: Web Reporter SQL database / delete IP addresses

Ok, thanks, we'll leave it as it is.

0 Kudos