4 Replies Latest reply on May 20, 2014 1:19 AM by karthago

    Web Reporter SQL database / delete IP addresses

    karthago

      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?

        • 1. Re: Web Reporter SQL database / delete IP addresses
          sroering

          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.

          • 2. Re: Web Reporter SQL database / delete IP addresses
            karthago

            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

            • 3. Re: Web Reporter SQL database / delete IP addresses
              sroering

              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.

              1 of 1 people found this helpful
              • 4. Re: Web Reporter SQL database / delete IP addresses
                karthago

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