Active Directory change tracking

• Download FixEventRecordIDbug.zip - 2.1 KB
• Download source - 3.3 MB
• Download ADchangeTracker_release.zip - 3.4 MB

On GitHub also: https://github.com/snorrikris/ADchangeTracker

Introduction

For any company that uses Microsoft Active Directory (AD) for authentication it is either a requirement or is neccessary to have good overview of what is happening in that environment. Managing the AD can become a huge challenge for system administrators - this solution presented here is intended to help a little bit with that job.

This software provides a security auditing solution for the Active Directory. In a Microsoft Windows computer system environment it can be a bit of a challenge to find out who changed what and when in the Active Directory. Provided of course that changes to the AD are even logged to the Security Log on all Domain Controller servers.

This solution has three main parts;

1. SQL Server database that stores the AD change events.
2. Windows Service that runs on all writable domain controllers. This service subscribes to all events logged to the Security Log, filters the AD change events and sends them to the SQL server for processing. This service places a very low load on the Domain Controller server - it uses less than 3MB memory when running and very little CPU time. It is also very unintrusive because it only subscribes to events from the security log - it does not even communicate with the Active Direcory itself in any way.
3. SQL Server Reporting Services (SSRS) report - this provides viewing and searching of the AD events in the database. Click on the image below to see details.

Note - no programming experience is required to install this software - just download the ADchangeTracker_release.zip file and follow the detailed instructions provided. You will need to have access to the Active Directory Domain Controllers of course (as Domain Administrator), the SQL server (as SysAdmin) and the Reporting server (as System Administrator).

I've had this software running on two production domain controllers (Windows Server 2012 R2) for more than a month (at the time of writing) without problems.

System requirements

• Active Directory 2008 R2 or later.
• SQL server 2008 R2 or later.
• Sql Server Reporting Services 2008 R2 or later.

This software has been tested on Windows Server 2012 R2 Active Directory, SQL server 2012 standard edition - it should work on 2008 R2 but that has not been tested.

You should be able to use SQL Express edition but that edition does not include Reporting Services (or SQL Agent). It is possibe to use SQL Management Studio to view and search the data in the database instead of SSRS.

Background

Although many excellent software solutions for Active Directory auditing exist out there in the marketplace - none that I found were free (except this one: [^]) so I decided to create my own. After a bit of research I came to the conclusion that processing the AD changed events [^] logged to the security log would provide the information that I needed about changes to the Active Directory.

How it works

When the ADchangeTracker service starts the first thing it does is reading it's config file (the ADchangeTracker.cfg file is located in the same folder as the ADchangeTracker.exe). In the config file are the very few settings the service needs; SqlConnString - SQL server connection string, AcceptedEventIDs - list of accepted event ID's, IgnoredEvents - list of object classes of events in the range 5136...5141 that will be ignored (that means not stored in the database), VerboseLogging - on or off (when on - all events received will be logged) and DaysToKeepOldLogFiles - how many days to keep log files.

The ADchangeTracker service (when running on the Domain Controller) calls EvtSubscribe function [^] to register a subscription to all events logged to the Security Log (of the Domain Controller it is running on). The operating system will call a callback function in our code for each event logged. Note - when the service runs for the first time it will receive all events that have been logged to the security log this can take a few minutes, the number of events depends on the Security Log size - 100MB size is approx. 500.000 events, the service bookmarks each event that has been processed so it can continue from where it left of last time.

Each event received is rendered as XML data. Then we extract the EventRecordID (a sequential number generated by the event log) and the EventID [^]  (pugixml [^] library code is used for the XML parsing because it is very fast and small). We also need the Object class if that information is available in the event data. We use the EventID and Object class for filtering events we want to process. The EventRecordID is only used when we log to the service log file.

If the event passed through filtering we send it to the SQL server by calling usp_ADchgEventEx stored procedure in the AD_DW database, passing the event XML as the only parameter data. If the call succeeded we will save this event as a bookmark into the Security log. If the service stops at this point in time or loses connection to the SQL server, it can continue from the last event processed. The bookmark is saved as a file in %PROGRAMDATA% folder. (e.g. C:\ProgramData\ADchangeTracker\Bookmark.bin).

The service log files are stored in that folder also. A new log file is created each day and the last 15 days (set in config file) are kept.

The usp_ADchgEventEx stored procedure in the SQL server will extract information from the XML code and store in columns in a single row in the ADevents table. XQuery is used to extract the data from the event XML code. [^] [^] [^] The ADevents table has SourceDC and EventRecordID as Primary key - preventing duplicate events to be inserted into the table. Rather unusually the primary key is not the clustered index but we use EventTime as the clustered index key - this is done to improve query performance because queries will (almost) always be limited to a period in time. The usp_ADchgEventEx stored procedure will first of all check if the event it is processing already exists in the table and if so simply return success to the caller, meaning that the event has already been processed.

The table below explains what data to expect in each column of the ADevents table for each EventID we process.

It's important to know that the EventXML column stores rather a lot of data. Average 2K per event. Some AD change events can have more that 50K of XML data but we filter (most?) of those out. To prevent the database from growing to large we will need to delete old data priodically. This can be done by scheduling a job on SQL agent to run weekly for example that will delete old data. Or just do it manually occasonally. In any case you need to decide for how long period you want to have data available. The purpose of keeping the XML data is simply to be able to view it in case you need more information than is available in the ADevents table columns. You might also consider keeping the column data but throw away the XML data. So for example you could keep 1 year of column data but only 1 month of XML data.

-- Delete old XML data
UPDATE [AD_DW].[dbo].[ADevents] SET EventXml = NULL WHERE EventTime < '2015-05-01'

-- Delete old AD events data
DELETE [AD_DW].[dbo].[ADevents] WHERE EventTime < '2015-01-01'

About the code

The source code provided was created using Visual Studio 2013 in C++, an Installshield LE setup project is included also.

This software is a standard NT service application, for the service code I used this example from Microsoft [^] as reference.

When you read the following section - have the project open in Visual Studio - just a suggestion :)

ADchangeTracker.cpp

_tmain function

The main function is called when Service Control Manager (SCM) starts the ADchangeTracker service or if run from the command line. First thing we do is intialize the log to file system, read the config file information and store it in the global theService object that is declared in EventProcessing.cpp.

If command line parameters -install or -uninstall are present those are processed and then we exit.

Next we call StartServiceCtrlDispatcher function to start the service main function. This call will not return until the service has stopped. If that call fails with error code ERROR_FAILED_SERVICE_CONTROLLER_CONNECT we assume the application was run from the command line, display a message and exit.

EventProcessing.cpp

This file contains the CEventProcessing class. Only one instance of this object exists - it is the global theService object. The service main entry point and the service control handler entry point are in this file.

ServiceMain member function

The ServiceMain member function is called when the ADchangeTracker service starts. First thing we do is register a service control handler with the Service Control Manager. Next we report our status as SERVICE_START_PENDING to the SCM. Then we initialize COM as multithreaded. Now we check if minimum required settings are present (from the Config file). Next we create two signal events that will be used to signal when the service needs stopping (m_hEvent_ServiceStop) or if we lost connection to the SQL server (m_hEvent_SqlConnLost). The ADO SQL connection object is initialized at this point. Finally we signal SERVICE_RUNNING to the SCM and call the Start member function. Unless something went wrong in the startup phase then we need to signal SERVICE_STOPPED and return. Note the call to Start will not return until the service is stopping.

Start member function

The Start member function is called from ServiceMain function if initialization succeeded. First we try to connect to the SQL server and if that succeeds we start the Security Log event subscription. But if we can't connect to the SQL server we will not start subscribing to events. We need to wait until the connection is live. Here we enter a while(true) loop - waiting for either of the two signal events (m_hEvent_ServiceStop, m_hEvent_SqlConnLost) to become signaled.

If we lose the SQL connection we need to stop the event subscription. We will wait approx. 60 sec. before we retry to connect to the SQL server. Once connection is reestablished we start the event subscription again - resuming from where we left of last time in the log.

ProcessEvent member function

This function handles the callbacks from the operations system for each event logged to the Security Log. First we render the event as XML data. Then call the FilterAndSendEventToSql function. Following that we check if the SQL connection has been lost.

FilterAndSendEventToSql member function

This function extracts EventRecordID, EventID and ObjectClass from the XML code.  Next we check if the EventID is in the list of accepted ID's, if it is we check if the ObjectClass is in the ignore list - if it is not then we forward this event to the SQL server.

Points of Interest

I did learn quite a few things during the development of this project - at the top of the list is I suppose learning about XQuery and XPath. As you might have noticed I'm not very keen on copy/pasting the code in this artice - but for fun I would like to put the code for the usp_ADchgEventEx stored procedure here - as it was the hardest to write.

CREATE PROCEDURE [dbo].[usp_ADchgEventEx]
    @XmlData nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x XML = @XmlData;

    -- Get EventRecordID and SourceDC from XML data.
    DECLARE @EventRecordID int;
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @EventRecordID = @x.value('(/Event/System/EventRecordID)[1]', 'int');
    DECLARE @SourceDC nvarchar(128);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @SourceDC = @x.value('(/Event/System/Computer)[1]', 'nvarchar(128)');

    -- Early exit if event already processed (exists in table).
    IF EXISTS(SELECT EventRecordID FROM dbo.ADevents 
        WHERE EventRecordID = @EventRecordID AND SourceDC = @SourceDC)
        RETURN;

    DECLARE @EventID int;
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @EventID = @x.value('(/Event/System/EventID)[1]', 'int'); -- AS EventID

    DECLARE @ObjClass nvarchar(128), @Target nvarchar(256) = '', @Changes nvarchar(256) = '';

    -- Set @ObjClass depending on EventID:
    SELECT @ObjClass = 'user' WHERE @EventID IN (4740, 4738, 4725, 4724, 4723, 4722, 4720, 4767);
    SELECT @ObjClass = 'unknown' WHERE @EventID IN (4781);
    SELECT @ObjClass = 'group' WHERE @EventID IN (4728, 4732, 4733, 4756);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @ObjClass = @x.value('(/Event/EventData/Data[@Name="ObjectClass"])[1]', 'nvarchar(64)')
        WHERE @EventID IN (5136, 5137, 5139, 5141);

    -- Set @Target depending on EventID:
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Target = @x.value('(/Event/EventData/Data[@Name="SubjectDomainName"])[1]', 'nvarchar(64)') + '\' 
            + @x.value('(/Event/EventData/Data[@Name="TargetUserName"])[1]', 'nvarchar(64)')
        WHERE @EventID IN (4740);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Target = @x.value('(/Event/EventData/Data[@Name="TargetDomainName"])[1]', 'nvarchar(64)') + '\' 
            + @x.value('(/Event/EventData/Data[@Name="TargetUserName"])[1]', 'nvarchar(64)')
        WHERE @EventID IN (4738, 4725, 4724, 4723, 4722, 4720, 4728, 4732, 4733, 4756, 4767);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Target = @x.value('(/Event/EventData/Data[@Name="TargetDomainName"])[1]', 'nvarchar(64)') + '\' 
            + @x.value('(/Event/EventData/Data[@Name="OldTargetUserName"])[1]', 'nvarchar(64)')
        WHERE @EventID IN (4781);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Target = @x.value('(/Event/EventData/Data[@Name="ObjectDN"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (5136, 5137, 5141);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Target = @x.value('(/Event/EventData/Data[@Name="OldObjectDN"])[1]', 'nvarchar(128)')
    WHERE @EventID IN (5139);

    -- Set @Changes depending on EventID:
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Changes = 'Calling computer: ' 
            + @x.value('(/Event/EventData/Data[@Name="TargetDomainName"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (4740);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Changes = 'NewTargetUserName: ' 
            + @x.value('(/Event/EventData/Data[@Name="NewTargetUserName"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (4781);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Changes = 'MemberName: ' 
            + @x.value('(/Event/EventData/Data[@Name="MemberName"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (4728, 4756);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Changes = 'MemberSID: ' 
            + @x.value('(/Event/EventData/Data[@Name="MemberSid"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (4732, 4733);
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
        SELECT @Changes = 'NewObjectDN: '
            + @x.value('(/Event/EventData/Data[@Name="NewObjectDN"])[1]', 'nvarchar(128)')
        WHERE @EventID IN (5139);
    IF @EventID = 5136
    BEGIN
        DECLARE @OpType nvarchar(32);
        WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
            SELECT @OpType = @x.value('(/Event/EventData/Data[@Name="OperationType"])[1]', 'nvarchar(32)');
        IF @OpType = '%%14674' 
            SET @OpType = 'Value Added';
        ELSE IF @OpType = '%%14675' 
            SET @OpType = 'Value Deleted';

        WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/win/2004/08/events/event')
            SELECT @Changes = '(' + @OpType + ') ' 
                + @x.value('(/Event/EventData/Data[@Name="AttributeLDAPDisplayName"])[1]', 'nvarchar(128)') 
                + ': ' + @x.value('(/Event/EventData/Data[@Name="AttributeValue"])[1]', 'nvarchar(128)');
    END

    -- Insert new row into ADevents table.
    ;WITH XMLNAMESPACES (
      default 'http://schemas.microsoft.com/win/2004/08/events/event'
    )
    ,[Event] AS
    (
    SELECT @x.value('(/Event/System/TimeCreated/@SystemTime)[1]', 'datetime2') AS EventTime
        ,@x.value('(/Event/EventData/Data[@Name="SubjectDomainName"])[1]', 'nvarchar(64)') + '\' 
            + @x.value('(/Event/EventData/Data[@Name="SubjectUserName"])[1]', 'nvarchar(64)') AS ModifiedBy
        ,@x AS EventXml
    )
    INSERT INTO dbo.ADevents
        SELECT @SourceDC, @EventRecordID, e.EventTime, @EventID AS EventID, 
        @ObjClass AS ObjClass, @Target AS [Target], @Changes AS [Changes], e.ModifiedBy, 
        e.EventXml 
    FROM [Event] e
END

History

V1.0 First release.

9. Jan. 2016 small bug fix added.