Audit Log Consolidation

ALCComputers: This table stores the computer names in the events.

Field	Datatype	Description
Id	int (4)	Primary Key (PK) of the ALCComputers table
ComputerName	nvarchar (255)	The name of the computer

ALCEventCategories: This table stores the names of categories from event log entries.

Field	Datatype	Description
Id	tinyint (1)	Primary Key (PK) of the ALCEventCategories table
Description	nvarchar (50)	The name of the event category

ALCScanDetails: This table stores the scan information after every scan of all the domain controllers.

Field	Datatype	Description
Id	int (4)	The Primary Key (PK) of the ALCScanDetails table
ScannedComputerId	int (4)	Foreign Key (FK) references [table ALCComputers] It represents the scanned Domain Controller
EventLogType	nvarchar (50)	Stores the event log type
ScannedRecordCount	int (4)	The number of events scanned in the last scan
ScanError	int (4)	In case of any error, stores the error number
NextScanRecordNo	int (4)	Stores the record number for the next scan
NextScanRecordTime	int (4)	Stores the time for the next scan
ScanStartDateTime	datetime (8)	Records the start of last scan date time
ScanEndDateTime	datetime (8)	Records the end of last scan date time

ALCSecurityEvents: This table stores the details of all the security events.

Field	Datatype	Description
RecordId	decimal (9)	Primary Key (PK) of the ALCSecurityEvents table
Version	tinyint (1)	The OS version
EventId	int (4)	Stores the Event Id
EventType	tinyint (1)	Stores the Event Type
EventCategory	tinyint (1)	Foreign Key (FK) references [table [ALCEventCategories]
DateTime	datetime (8)	The event generation date and time
UserId	int (4)	Foreign Key (FK) references [table ALCUsers]
ComputerId	int (4)	Foreign Key (FK) references [table ALCComputers]
LocalFields	nvarchar (3000)	Stores the event description strings
GlobalFields	ntext (16)	-For Internal Usage-
LocalFieldCount	tinyint (1)	Stores the number of description strings

ALCUsers: This table stores the user name and their corresponding security identifiers (SIDs).

Field	Datatype	Description
Id	int (4)	Primary Key (PK) of the ALCUsers table
ScannedComputerId	int (4)	Stores the computer Id from where the event was fetched
Sid	varchar (50)	Stores the Sid in string format
Name	nvarchar (50)	Stores the name in the domain\username format

dvScanDetails: This view enables you to get information about all the scanned domain controllers.

View Query:

SELECT SD.Id AS ScanId, C.ComputerName AS ScannedComputer,

SD.EventLogType, SD.ScannedRecordCount, SD.ScanError,

SD.NextScanRecordNo, SD.NextScanRecordTime,

SD.ScanStartDateTime, SD.ScanEndDateTime

FROM ALCComputers C INNER JOIN

ALCScanDetails SD ON C.Id = SD.ScannedComputerId

dvSecurityEvents: This view enables you to get the details of all the security events.

View Query:

SELECT ALCSecurityEvents.Version, ALCSecurityEvents.EventId,

ALCSecurityEvents.EventType,

T3.Description AS EventCategory,

ALCSecurityEvents.DateTime, T4.Sid, T4.Name AS UserName,

T2.ComputerName AS Computer,

ALCSecurityEvents.LocalFields,

ALCSecurityEvents.GlobalFields,

ALCSecurityEvents.LocalFieldCount

FROM ALCSecurityEvents INNER JOIN

ALCComputers T2 ON

ALCSecurityEvents.ComputerId = T2.Id INNER JOIN

ALCEventCategories T3 ON

ALCSecurityEvents.EventCategory = T3.Id INNER JOIN

ALCUsers T4 ON ALCSecurityEvents.UserId = T4.Id

SELECT count(ALL EventId) FROM dvSecurityEvents WHERE (DateTime BETWEEN '2004-07-20 14:49:24' AND '2004-08-20 14:49:24') AND (EventId=624)

SELECT count(ALL EventId) FROM dvSecurityEvents WHERE (DateTime BETWEEN '2004-07-20 14:49:24' AND '2004-08-20 14:49:24') AND (EventId=531)

Audit Log Consolidation (ALC) Database Schema

This topic attempts to explain the ALC database schema—the current structure of the tables and database views in the database; thus empowering you to create custom queries, which are not possible through ALC Reports.

Note: BindView recommends that you do not modify the tables in the ALC database manually.

Based on the above information, you can create custom queries to suit your individual needs and extract relevant information directly from the ALC database.