Most of the DBA’s have many tasks belonging to the System Administrator (SA) in a Windows 2K network
- either there is a single person in a IT Department or in the case of small company.
One of this tasks is to read and analyze the Event Viewer Logs daily to see if there some
problems, errors etc. As we know the operating system has the way to announce to
the SA when an special event appear in system. More, if we want to take a history of events we can save this logs in a text
file (example: open Event Viewer, click Security Log, Action, Save Log File As…).
The maximum log size is 512 K in Windows 2K that make a text file with ~2,500 rows for reading.
Let’s think about a scenario: The company has 10 Windows 2K file servers .The network works but the logs are filled in 1
days. In this case SA have to read a file with ~25,000 rows to have a conclusion about
how the machines are working.
Like MS-DBA we can use SQL Server 2000 tools to make an image on this repository of Event Viewer events.
The steps for this goal are:
1. Automate creating the text log file in Windows environment.
2. Run a scheduled DTS Package with following tasks:
To copy the text file log in a SQL Server database insert a Transform Data Task:Text File
with Source=Log file text(Ap2.txt) and Destination =SQL Server table (Ap2) with following design:
Create Table [Ap2Rez] ( [Col001] [varchar] (255) , -- Date in Event Viewer Log [Col002] [varchar] (255) , -- Time in Event Viewer Log [Col003] [varchar] (255), -- Source] in Event Viewer Log [Col004] [varchar] (255), -- Type in Event Viewer Log [Col005] [varchar] (255) , -- Category in Event Viewer Log [Col006] [varchar] (255), -- EventID in Event Viewer Log [Col007] [varchar] (255), -- User in Event Viewer Log [Col008] [varchar] (255) , -- Computer in Event Viewer Log [Col009] [varchar] (456) ) . -- Description in Event Viewer Log
To adjust SQL Server table resulted (Ap2) that has an anomaly (Col009 is too big and a part of it is introduced in Col001)
Insert an Execute SQL Task that runs a script (or a procedure) for append the rows in a table Ap2Rez2 with following design:
[Ap2Rez2] ( [IDRow] [int] IDENTITY (1, 1) NOT NULL , [_Date] [datetime] NULL , --is Col001 + Co002 [_Source] [varchar] (255) , [_Type] [varchar] (255, [_Category] [varchar] (255), [_EventID] [int] NULL , [_User] , [_Computer] [varchar] (255) , [_Description] [varchar] (1000). --is Col009 + Col001 just in case
Click here to view the entire script.
3. Run a scheduled DTS Package to reprocess an Incrementally Updating OLAP cube made in following way:
Cube called EventViewer.
The Fact Table Source :Ap2Rez2 .
The Measure:Event Id with Aggregate Function Count.
The structure of dimensions:
Shared Dimensions on Ap2Rez2 | Levels | Member Key Column | Member Name Column |
Time | Year | DatePart(year,"dbo"."Ap2Rez2"."_Date") | DatePart(year,"dbo"."Ap2Rez2"."_Date") |
Month | DatePart(month,"dbo"."Ap2Rez2"."_Date") | convert(CHAR, DateName(month,"dbo"."Ap2Rez2"."_Date")) | |
Day | convert(CHAR,"dbo"."Ap2Rez2"."_Date", 112) | rtrim(convert(CHAR, DateName(day,"dbo"."Ap2Rez2"."_Date")))+' th' | |
Hour | DatePart(hour,"dbo"."Ap2Rez2"."_Date") | right(convert(varchar(19),"dbo"."Ap2Rez2"."_Date",0),7) | |
Computer | Computer | "dbo"."Ap2Rez2"."_Computer" | "dbo"."Ap2Rez2"."_Computer" |
User | User | "dbo"."Ap2Rez2"."_User" | "dbo"."Ap2Rez2"."_User" |
Type | Type | "dbo"."Ap2Rez2"."_Type" | "dbo"."Ap2Rez2"."_Type" |
After reprocessing we can have an image on network activity like this.
Normally, only the SA (DBA) can browse data .
The DBA can use this cube to see if they can balance the SQL Server activity.
For example :in replication a Distributor can be put on a Windows server with lowest activity or can be viewed the unsafe Windows server that can affect SQL Server databases.
I think that can be put some questions about using this cube in a network:
1. How can we build an OLAP cube to see the track of an illegal attack in a network?
(I suppose it can be linked on the order of events).
2. If it can be developed a whole tools based on OLAP cube engine, can be it attached on a new version of Windows Operating System?
