Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Importing And Analyzing Event Logs

By Gheorghe Ciubuc,

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?

Total article views: 12777 | Views in the last 30 days: 6
 
Related Articles
FORUM

event viewer

event viewer

FORUM

What is Event Viewer and Log viewer

Difference Event Viewer and Log viewer

FORUM

Error in Event Viewer

Error in Event Viewer - SQLServer2005

FORUM

Event viewer errors on server?

Why and what for Event viewer errors created?

FORUM

EventId 17055 in Event Viewer

SQL is noting down the above event in event viewer

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones