How to centralize your SQL Server Event Logs.

Question

How to centralize your SQL Server Event Logs.

Geoff A

SSChampion

Points: 11511
More actions
November 6, 2010 at 2:20 pm

#228813

Comments posted to this topic are about the item How to centralize your SQL Server Event Logs.

Viewing 15 posts - 1 through 15 (of 72 total)

You must be logged in to reply to this topic. Login to reply

peleg SSCrazy Points: 2611 More actions · Answer 1

the only thing that i whould change, is running the vbs from windows schedule task and not from the sql server, i think it's more correct way.

This will give you the ability prepare a vbs that you can put on all of you server's, rather only in the db server (although you can query the event viewer of a remote server, which is little more work).

rena24 Default port Points: 1482 More actions · Answer 2

Hello,

your Article is really great. I tried to Run the Script on our Server (Windows Server 2003 R2 SERVICE PACK 1) but I get an error (see attached file). Can anyone Help me?

Thanks a lot

Geoff A SSChampion Points: 11511 More actions · Answer 3

renasoursou (11/8/2010)
Hello,
your Article is really great. I tried to Run the Script on our Server (Windows Server 2003 R2 SERVICE PACK 1) but I get an error (see attached file). Can anyone Help me?
Thanks a lot

Do you have SQL running with a database called DBA on your server?

does the database DBA have the table EventLogStaging?

Geoff A SSChampion Points: 11511 More actions · Answer 4

peleg (11/8/2010)
the only thing that i whould change, is running the vbs from windows schedule task and not from the sql server, i think it's more correct way.
This will give you the ability prepare a vbs that you can put on all of you server's, rather only in the db server (although you can query the event viewer of a remote server, which is little more work).

yes, you could use windows scheduler, however, since the DB is required to make it work, the script would have to include some logic to handle itself if the DB was not avaialble.

it is also designed to be used by a DBA. in some places, DBA's do not have access to the windows scheduler.

also, the script is only required on one server 😉

peleg SSCrazy Points: 2611 More actions · Answer 5

since the DB is required to make it work, the script would have to include some logic to handle itself if the DB was not avaialble.

this logic should run from the vbscript.

peleg SSCrazy Points: 2611 More actions · Answer 6

the problem that newbie got, isn't related to the DB, rather to the query of the wmi object.

rena24 Default port Points: 1482 More actions · Answer 7

Geoff A (11/8/2010)
renasoursou (11/8/2010)
Hello,
your Article is really great. I tried to Run the Script on our Server (Windows Server 2003 R2 SERVICE PACK 1) but I get an error (see attached file). Can anyone Help me?
Thanks a lot
Do you have SQL running with a database called DBA on your server?
does the database DBA have the table EventLogStaging?

Yes I have. On thIs Server there are running an 2000 Sql Server and a 2005 SQL Server Instance. I have changed this Line

--> objConn.Open "Provider=SQLOLEDB.1;Data Source=RLDB2,4949\RLDB2_05;Initial Catalog=DBA;Integrated Security=SSPI"

RLDB2,4949\RLDB2_05 --> This is our SQL Server 2005 Instance on this Server.

I dont understand this error...

peleg SSCrazy Points: 2611 More actions · Answer 8

you have problem with the WMI object, not with the DB.

what did you change with the code?

query = "Select * from __InstanceCreationEvent" _

& " " & "where TargetInstance isa 'Win32_NTLogEvent'" _

& " " & "and TargetInstance.Logfile = 'Application'" _

& " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _

& " " & "and (TargetInstance.SourceName like 'MSSQL%')"

' get ready to insert into our DBA table

the problem is when you try to run the query :

Set colMonitoredEvents = objWMIService.ExecNotificationQuery(query)

Geoff A SSChampion Points: 11511 More actions · Answer 9

renasoursou, try this;

create an alias for RLDB2,4949\RLDB2_05 and call it something like "monitor"

then change Data Source=RLDB2,4949\RLDB2_05 to Data Source=monitor

peleg SSCrazy Points: 2611 More actions · Answer 10

Geoff A (11/8/2010)
renasoursou, try this;
create an alias for RLDB2,4949\RLDB2_05 and call it something like "monitor"
then change Data Source=RLDB2,4949\RLDB2_05 to Data Source=monitor

how/where do you define this alias?

Geoff A SSChampion Points: 11511 More actions · Answer 11

peleg (11/8/2010)
Geoff A (11/8/2010)
renasoursou, try this;
create an alias for RLDB2,4949\RLDB2_05 and call it something like "monitor"
then change Data Source=RLDB2,4949\RLDB2_05 to Data Source=monitor
how/where do you define this alias?

http://msdn.microsoft.com/en-us/library/ms190445.aspx

rena24 Default port Points: 1482 More actions · Answer 12

Its running with the allias name. Thanks a lot and its really a great and usefull Article!!! :-):-):-):-)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 13

nice article

i've been doing something similar for 2 years and might check this out to see if it's faster than mine. in my case i run the VB from each server and dump to a central SQL server. i have a staging table for each server and there is a job that runs every hour to delete old data from the main tables and copy data from the staging tables and then truncate them. and i wrote a bunch of reports over the last year to track locked out accounts, AD Group changes, etc. each report has a SSRS subscription to email it to the right people

i've wanted to change over to powershell, but haven't found anything where it says how to dump the data directly to SQL

to make sure data is being imported i have a report that i check once a week that shows me the server name and the latest app/security log dates in the database for each server

GNUZEN SSC Veteran Points: 202 More actions · Answer 14

Great post!

I usually did the same way before SQL Server 2008.

Now I've implemented central monitor solution using PowerShell and SQL Server central management feature.

For security reason I would try not using VBS. Using native SQL Server central management also enables you to manage and configure in one place.

my two cents.