How to centralize your SQL Server Event Logs.

  • Geoff A

    SSChampion

    Points: 11417

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

  • peleg

    SSCrazy

    Points: 2611

    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: 1480

    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: 11417

    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: 11417

    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

    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

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

  • rena24

    Default port

    Points: 1480

    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

    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: 11417

    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

    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: 11417

    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: 1480

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

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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

    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.

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

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