Sitecore DMS stores IP addresses as a varbinary(16) in the Visits table in the analytics database.
This is just perfect but it is impossible to read the IP addresses as a normal human being. To help out with this I found the following SQL function which can convert an IPv4 address to a binary.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4) AS BEGIN DECLARE @bin AS BINARY(4) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) RETURN @bin END |
So let say that you want to delete all visits from localhost, simply run the following SQL statement using Sql Server Management Studio.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE [ANALYTICS DATABASENAME]; IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fnBinaryIPv4') BEGIN DECLARE @sql NVARCHAR(MAX); SET @sql = N'CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4) AS BEGIN DECLARE @bin AS BINARY(4) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) RETURN @bin END'; EXEC sp_executesql @sql; END go Delete from [Visits] where [IP] = dbo.fnBinaryIPv4('127.0.0.1'); |
Converting the binary back to human readable format is done like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15) AS BEGIN DECLARE @str AS VARCHAR(15) SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) ); RETURN @str END; go |
So to get a human readable list of all IP’s in the Analytics database write something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [ANALYTICS DATABASENAME]; IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fnDisplayIPv4') BEGIN DECLARE @sql NVARCHAR(MAX); SET @sql = N'CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15) AS BEGIN DECLARE @str AS VARCHAR(15) SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + ''.'' + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + ''.'' + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + ''.'' + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) ); RETURN @str END'; EXEC sp_executesql @sql; END select dbo.fnDisplayIPv4([IP]) from Visits |
That was it, hope it helps someone out there.
Anders Laub Christoffersen
Anders has been working with Sitecore for over a decade and has in this time been the lead developer and architect on several large scale enterprise solutions all around the world. Anders was appointed the title of Sitecore Technical MVP in 2014 and has been re-appointed the title every year since then.
- Web |
- More Posts