Sitecore DMS visits from specific IP addresses

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.

So let say that you want to delete all visits from localhost, simply run the following SQL statement using Sql Server Management Studio.

Converting the binary back to human readable format is done like this:

So to get a human readable list of all IP’s in the Analytics database write something like this:

That was it, hope it helps someone out there.

Anders Laub

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 has been nominated a Sitecore Technical MVP three years in a row for 2014, 2015 and 2016. Anders is now working as a Sr. Solutions Architect at Sitecore in Copenhagen.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*
Website