IE 11 Enterprise Mode Tracking - Part 1

Internet Explorer 11 comes with this great new feature, Enterprise Mode, and it saves the day when it comes to most browser related upgrade issues. At least in my case it did a really good job of mitigating them. Unfortunately for me the documentation didn't do a great job of showing me how to make sense of it all,  especially when it came to how I was supposed to collect all the data from the web endpoint in the 'enable' key. (https://msdn.microsoft.com/en-us/library/dn640699.aspx) Basically you can put a URL in the 'enable' registry key which instructs the browser to post some data to a URL when ever a user enables, or disables, enterprise mode. That is great! Well wait, what do we need to setup to collect the data again?

There are a few posts out in the interwebs that describe how to do this already.  Some people use a web page to write to a text file, and Microsoft even released a way for us to collect this data with ConfigMgr (https://msdn.microsoft.com/en-us/library/dn833204.aspx). Great problem solved!  Well unfortunately ConfigMgr may not be everywhere I need it, or maybe I don't want to collect all this data in my 'already too big for my own good' CM database.  I thought I would share what I have done to make my life a little easier when someone asks "who is using enterprise mode and for which URLs?".  So let's setup an IIS site with a page that collects the data and then writes the data into a database for us. Basically we setup IIS, setup the database, then drop an aspx file into our website to write the data to the database for us. **Small disclaimer here: I am a systems admin and not an application developer. So please test this, use the appropriate data locations, and please change the passwords.  Also, let me know what i messed up!**

First off, we need a web server. For the purpose of this article I setup Server 2012 R2 with SQL Express as a starting point. Next I ran the following commands to setup the IIS Role on the server.

DISM /online /enable-feature /featurename:NetFx4 /all
DISM /online /enable-feature /featurename:IIS-WebServerRole
DISM /online /enable-feature /featurename:IIS-ASPNET45 /all
DISM /online /enable-feature /featurename:WCF-HTTP-Activation

Next, lets make a directory for our IIS site and database, then create and configure the IIS site using the commands below.

::Make our web dir
mkdir c:\ie11em
::Make our SQL dir
mkdir c:\sql
::Stop the Default IIS site
c:\windows\system32\inetsrv\appcmd stop site /site.name:"Default Web Site"
::Create our IE11EM IIS site
c:\windows\system32\inetsrv\appcmd.exe add site /name:IE11EM /id:2 /physicalPath:c:\ie11em /bindings:http/*:80:
::Enable windows auth (so we can capture user names)
c:\windows\system32\inetsrv\appcmd set config /section:windowsAuthentication /enabled:true
::Disabled Anonymous Auth
c:\windows\system32\inetsrv\appcmd set config /section:anonymousAuthentication /enabled:false

Now to setup the database, lets open up SQL Management Studio and run the following script.  This will also create the SQL auth account for the webpage to write to the database.  You will need to enable SQL auth on your SQL instance prior to running this, in SQL Mgmt Studio right click on your server name and select properties.  Then select the Security node, and set the Server Authentication option to 'SQL Server and Windows Authentication mode'.  (aka Mixed Mode)  Then restart you sql instance for the changes to take effect.

/************************** Start IE11EM.SQL **************************/
USE [master]
GO
/****** Object: Database [IE11EntModeDB] ******/
CREATE DATABASE [IE11EntModeDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'IE11EntModeDB', FILENAME = N'c:\SQL\IE11EntModeDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'IE11EntModeDB_log', FILENAME = N'c:\SQL\IE11EntModeDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 8GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [IE11EntModeDB] SET COMPATIBILITY_LEVEL = 110
GO

USE [IE11EntModeDB]
GO

/****** Object: User [svc.ie11entmode] ******/
CREATE LOGIN [svc.ie11entmode] WITH PASSWORD = 'P@ssW0rd!';
GO
CREATE USER [svc.ie11entmode] FOR LOGIN [svc.ie11entmode] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [svc.ie11entmode]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [svc.ie11entmode]
GO
/****** Object: Table [dbo].[IEEntMode] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IEEntMode](
[key] [int] IDENTITY(1,1) NOT NULL,
[Source] [nvarchar](128) NULL,
[Url] [nvarchar](2048) NULL,
[Mode] [nvarchar](5) NULL,
[User] [nvarchar](64) NULL,
[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_IEEntMode_TimeStamp] DEFAULT (getdate()),
CONSTRAINT [PK_IEEntMode] PRIMARY KEY CLUSTERED
(
[key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: View [dbo].[vwIE11EnterpriseModeCountSummary] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwIE11EnterpriseModeCountSummary]
AS
SELECT Url, Mode, COUNT(Mode) AS tCount
FROM dbo.IEEntMode
WHERE (Url <> '')
GROUP BY Url, Mode

GO
/****** Object: View [dbo].[vwIE11EnterpriseModeDomainDetail] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwIE11EnterpriseModeDomainDetail]
AS
SELECT LEFT(Url, CHARINDEX('/', Url, 9) - 1) AS Domain, Mode
FROM dbo.IEEntMode
WHERE (Url <> '')
GO

USE [master]
GO

ALTER DATABASE [IE11EntModeDB] SET READ_WRITE
GO
/************************** End IE11EM.SQL **************************/

Alright, almost there. The last step is to place the aspx page in our web directory. Save the code below as ie11em.aspx into the c:\ie11em directory created earlier.  Make certain to substitute your server's name in the connection string where you see "%YourServerNameHere%" listed.

/************************** Start ie11em.aspx **************************/

<html><head><title>IEEM Logging</title></head><body>

<%
'Dim objFSO, objTextFile, csvFile

Response.write("'" & Request.ServerVariables("REMOTE_HOST") & "','" & Request.ServerVariables("AUTH_USER") & "','" & Request.Form("URL") & "','" & Request.Form("EnterpriseMode") & "'")

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Server=%YourServerNameHere%; Database=IE11EntModeDB; User Id=Svc.ie11entmode; password=P@ssW0rd!";)

Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT IEEntMode ([Source],[User],[Url],[Mode]) VALUES ('" & Request.ServerVariables("REMOTE_HOST") & "','" & Request.ServerVariables("AUTH_USER") & "','" & Request.Form("URL") & "','" & Request.Form("EnterpriseMode") & "')"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

cmd = Nothing
sqlConnection1 = Nothing

%>
</body></html>

You can now test your IE 11 data collection site by putting "https://%YourServerNameHere%/ieem.aspx" in your browser.  If everything is working you will see '192.168.1.24','domain\username','','' appear in the browser, where 192.168.1.24 is the IP of the system you opened the browser on, and the domain\username is the user you are logged in with.  Since we are not passing the same variable strings the browser does, you will see blanks in the last 2 values.  Once you plug this URL value in the registry key to enable Enterprise Mode you will also see the URL passed from the browser, and a binary On or Off value.  (HKLM\Software\Policies\Microsoft\Internet Explorer\Main\EnterpriseMode enable="https://%YourServerNameHere%/ieem.aspx")  

Once you have updated the registry, restarted IE, and you have enabled Enterprise Mode on a few sites.  Use the query below to see the data you are collecting.

SELECT [key],[Source],[Url],[Mode],[User] ,[TimeStamp] FROM [dbo].[IEEntMode]

Next up in part 2:  A few SSRS reports to rollup the data and a runbook to rollout the sitelist to, you guessed it, the website above.

Until next time, cheers!