RE: SQL Queries of Windows Event Logs

["Joe Quigley" <jquigley () iir-central com>]

Hello, 

A couple of people have asked for more information. 

1. Auditing is turned on, via GPO, for logon events (success and
failure) and account management. 

2. I am interested in events that could indicate inappropriate use of
the network. Some examples would be security events 517, 624, 632, 636,
642, 644, 660, 675, 676, 680, and 681. I would like to try and filter
some of the "background noise" out of the reports, if possible. An
example of the "noise" would be a single 675 error from one machine.
That event will occur when a Kerberos ticket expires, but should not
happen repetitively pre machine. If it happens more then once, I'd like
to see it in my daily report. 

3. Database Structure. I import each type of log (security, app, system,
DS, DNS) into its own table. Here is the SQL script used to create the
tables. 

        CREATE TABLE [dbo].[Security] (
        [Log] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Date] [datetime] NULL ,
        [Time] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [EventID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [Type] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
        [Category] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [UserID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [Computer] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

I used the database structure and import routine from this document if
anyone is curious. 
http://www.sans.org/rr/whitepapers/logging/902.php 
(Thanks to the author, Gregory Lalla)

I apologize for the initial lack of information. If I missed anything
else that may be of use, please let me know.

Regards,

Joe

-----Original Message-----
From: Harlan Carvey [mailto:keydet89 () yahoo com] 
Sent: Tuesday, June 07, 2005 7:48 AM
To: Joe Quigley
Subject: re: SQL Queries of Windows Event Logs

Joe,

> Since SQL is not my strong point, I'd like to ask 
> the list if anyone has seen (or would be willing to 
> share) a recommended set of reports/queries for 
> tracking potential security issues (failed logins, 
> AD object changes, etc). 

there are a couple of things at work here...

First, are you sure that the logs are configured to
support the queries you've mentioned above?  For
example, are you logging failed login attempts (sorry,
had to ask)?

Second, what are the events you're interested in?  Are
there any others besides the ones you've listed above?

Finally, before anyone can give you the SQL queries
you're asking for, you're going to have to make the
structure of your database public.  Are the fields
dumped into a single table? 

Just stuff to consider, that's all...

Harlan

------------------------------------------
Harlan Carvey, CISSP
"Windows Forensics and Incident Recovery"
http://www.windows-ir.com
http://windowsir.blogspot.com
------------------------------------------