SQL ODBC Logging

KauerKauer
edited August 2007 in Monitoring Software v12 and lower
The Logging in the Table dbo.Serverscheck_Data is correct but was no Data in the other 2 Tables

dbo.Serverscheck

dbo.Serverscheck_Checks

Why?

Comments

  • AdministratorAdministrator
    Try to restart the service. ServersCheck performs only insert and update statements. If the ODBC was activated after a restart, then it will try to UPDATE records that do not exist.



    You can also trace the SQL on your ODBC system
  • KauerKauer
    is this function going in the trial version?
  • AdministratorAdministrator
    Tracing SQL statements is something you do an a database level and is typically done by a DBA.



    When running the Monitoring Service in debug mode as per knowledge base you can also see the SQL statements that it executes
  • KauerKauer
    # S-ODBC Mon Aug 6 09:07:49 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184568528');

    # S-ODBC Mon Aug 6 09:07:49 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184568528', Groups='Swis', Devices='T12RQCH', Label='PING T12RQCH', Type='PING', Status='OK', Upcounter=33190, Downcounter=41, Errors='Connection to host timed out', ServiceLevel='99.87', CheckVal='0' WHERE UID='1184568528';

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('38','1184251196');

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184251196', Groups='Swis', Devices='Swis Abfragen', Label='Swis HTTP Abfrage', Type='URL', Status='OK', Upcounter=14697, Downcounter=0, Errors='Warning level: 1032 (returned value) greater than 1000', ServiceLevel='100', CheckVal='38' WHERE UID='1184251196';

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('269','1184251205');

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184251205', Groups='Swis', Devices='Swis Abfragen', Label='Swis HOST Abfrage', Type='URL', Status='OK', Upcounter=14557, Downcounter=140, Errors='Warning level: 3867 (returned value) greater than 3000', ServiceLevel='99.04', CheckVal='269' WHERE UID='1184251205';

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('27','1184259905');

    # S-ODBC Mon Aug 6 09:07:50 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184259905', Groups='Swis', Devices='Swis Abfragen', Label='Swis WAS Abfrage', Type='URL', Status='OK', Upcounter=14700, Downcounter=0, Errors='Warning level: 1074 (returned value) greater than 1000', ServiceLevel='100', CheckVal='27' WHERE UID='1184259905';

    # S-ODBC Mon Aug 6 09:07:51 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('2','1184569928');

    # S-ODBC Mon Aug 6 09:07:51 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569928', Groups='Swis', Devices='T12RQCJ', Label='PING T12RQCJ', Type='PING', Status='OK', Upcounter=9897, Downcounter=1, Errors='Error level: 144 (returned value) greater than 50', ServiceLevel='99.98', CheckVal='2' WHERE UID='1184569928';

    # S-ODBC Mon Aug 6 09:07:51 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184569998');

    # S-ODBC Mon Aug 6 09:07:51 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1186129156');

    # S-ODBC Mon Aug 6 09:07:52 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569998', Groups='Swis', Devices='T12RQCK', Label='PING T12RQCK', Type='PING', Status='OK', Upcounter=9896, Downcounter=1, Errors='Error level: 176 (returned value) greater than 50', ServiceLevel='99.98', CheckVal='0' WHERE UID='1184569998';

    # S-ODBC Mon Aug 6 09:07:52 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1186129156', Groups='TEST', Devices='Local System', Label='PING', Type='PING', Status='OK', Upcounter=1414, Downcounter=0, Errors='', ServiceLevel='100', CheckVal='0' WHERE UID='1186129156';

    # S-ODBC Mon Aug 6 09:07:53 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('1|61|37|C.38|H.73','1171011122');

    # S-ODBC Mon Aug 6 09:07:54 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1171011122', Groups='Systemüberwachung', Devices='Local System', Label='WINDOWSHEALTH', Type='WINDOWSHEALTH', Status='OK', Upcounter=2730, Downcounter=0, Errors='CPU Error level: 100 (returned value) greater than 95', ServiceLevel='100', CheckVal='1|61|37|C.38|H.73' WHERE UID='1171011122';

    # S-ODBC Mon Aug 6 09:07:55 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184569828');

    # S-ODBC Mon Aug 6 09:07:55 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569828', Groups='Swis', Devices='T12RQCI', Label='PING T12RQCI', Type='PING', Status='OK', Upcounter=9891, Downcounter=1, Errors='Connection to host timed out', ServiceLevel='99.98', CheckVal='0' WHERE UID='1184569828';

    # S-ODBC Mon Aug 6 09:09:51 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('53','1184251196');





    But it was no data in Serverscheck_Checks



    we have SQL Server2005
  • AdministratorAdministrator
    A you can see the SQL statements are executed.



    The INSERT STATEMENTS are performed at startup by the monitoring_manager.exe



    Stop the service and launch this from command prompt. You should see SQL statements like;

    INSERT INTO SERVERSCHECK_CHECKS (UID) VALUES ('1171011122');
  • KauerKauer
    montest:# M Mon Aug 6 09:18:03 2007 Monitoring_manager instances allowed: 1

    # M Mon Aug 6 09:18:03 2007 Monitoring_manager instances counted: 1

    #

    # M Mon Aug 6 09:18:03 2007 Loading language file: DE.lang

    # M Mon Aug 6 09:18:09 2007 ServersCheck Monitoring Manager

    # M Mon Aug 6 09:18:09 2007 15 Day Evaluation version 7.5.1 - for evaluation purposes use only

    # M Mon Aug 6 09:18:09 2007 Started OK

    # M Mon Aug 6 09:18:09 2007 Old thread started

    # O1 Mon Aug 6 09:18:10 2007 Starting Monitoring Rule Thread O1

    # O1 Mon Aug 6 09:18:10 2007 ServersCheck Monitoring Component

    # O1 Mon Aug 6 09:18:10 2007 TRIAL 7.5.0 - for eval only

    # O1 Mon Aug 6 09:18:10 2007 monitoring_rule instances max allowed: 1

    # O1 Mon Aug 6 09:18:10 2007 monitoring_rule instances counted: 1

    # O1 Mon Aug 6 09:18:10 2007 database logging active - dsn: Driver={SQL Server}; Server=T12RQDC; Database=master; UID=checks; PWD=*******;

    # M Mon Aug 6 09:18:11 2007 Superthread started

    # M Mon Aug 6 09:18:11 2007 Security thread started

    # Mon Aug 6 09:18:11 2007 Starting Monitoring Security Sensors Thread 7.5.0

    # M Mon Aug 6 09:18:13 2007 import rules

    # M Mon Aug 6 09:18:13 2007 - 1171011122WINDOWSHEALTH: 1171011122 (WINDOWSHEALTH check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184251196URL: 1184251196 (URL check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184259905URL: 1184259905 (URL check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184251205URL: 1184251205 (URL check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184568528PING: 1184568528 (PING check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184569828PING: 1184569828 (PING check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184569928PING: 1184569928 (PING check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1184569998PING: 1184569998 (PING check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 - 1186129156PING: 1186129156 (PING check) databases required for graphing already exist?

    # M Mon Aug 6 09:18:13 2007 Creating network map networkmap

    # S Mon Aug 6 09:18:14 2007 Starting Monitoring SThread

    # S Mon Aug 6 09:18:14 2007 TRIAL 7.5.1 - for eval only (5)

    # S Mon Aug 6 09:18:14 2007 database logging active - dsn: Driver={SQL Server}; Server=T12RQDC; Database=master; UID=checks; PWD=*******;

    # M Mon Aug 6 09:18:30 2007 import rules

    # M Mon Aug 6 09:18:47 2007 import rules

    # M Mon Aug 6 09:18:47 2007 Creating network map networkmap

    # M Mon Aug 6 09:19:06 2007 import rules

    # M Mon Aug 6 09:19:14 2007 Monitoring Rule Process Watcher: 1 found

    # M Mon Aug 6 09:19:23 2007 import rules

    # M Mon Aug 6 09:19:23 2007 Creating network map networkmap

    # M Mon Aug 6 09:19:40 2007 import rules

    # M Mon Aug 6 09:19:57 2007 import rules

    # M Mon Aug 6 09:19:57 2007 Creating network map networkmap

    # M Mon Aug 6 09:20:14 2007 import rules

    # M Mon Aug 6 09:20:16 2007 Monitoring Rule Process Watcher: 1 found

    # S-ODBC Mon Aug 6 09:18:19 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('919','1184251196');

    # S-ODBC Mon Aug 6 09:18:19 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184251196', Groups='Swis', Devices='Swis Abfragen', Label='Swis HTTP Abfrage', Type='URL', Status='OK', Upcounter=14702, Downcounter=0, Errors='Warning level: 1032 (returned value) greater than 1000', ServiceLevel='100', CheckVal='919' WHERE UID='1184251196';

    # S-ODBC Mon Aug 6 09:18:19 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('250','1184259905');

    # S-ODBC Mon Aug 6 09:18:19 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184259905', Groups='Swis', Devices='Swis Abfragen', Label='Swis WAS Abfrage', Type='URL', Status='OK', Upcounter=14705, Downcounter=0, Errors='Warning level: 1074 (returned value) greater than 1000', ServiceLevel='100', CheckVal='250' WHERE UID='1184259905';

    # S-ODBC Mon Aug 6 09:18:20 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('336','1184251205');

    # S-ODBC Mon Aug 6 09:18:20 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184251205', Groups='Swis', Devices='Swis Abfragen', Label='Swis HOST Abfrage', Type='URL', Status='OK', Upcounter=14562, Downcounter=140, Errors='Warning level: 3867 (returned value) greater than 3000', ServiceLevel='99.04', CheckVal='336' WHERE UID='1184251205';

    # S-ODBC Mon Aug 6 09:18:22 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('1|43|35|C.38|H.73','1171011122');

    # S-ODBC Mon Aug 6 09:18:22 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1171011122', Groups='Systemüberwachung', Devices='Local System', Label='WINDOWSHEALTH', Type='WINDOWSHEALTH', Status='OK', Upcounter=2735, Downcounter=0, Errors='CPU Error level: 100 (returned value) greater than 95', ServiceLevel='100', CheckVal='1|43|35|C.38|H.73' WHERE UID='1171011122';

    # S-ODBC Mon Aug 6 09:19:49 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184568528');

    # S-ODBC Mon Aug 6 09:19:49 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184568528', Groups='Swis', Devices='T12RQCH', Label='PING T12RQCH', Type='PING', Status='OK', Upcounter=33194, Downcounter=41, Errors='Connection to host timed out', ServiceLevel='99.87', CheckVal='0' WHERE UID='1184568528';

    # S-ODBC Mon Aug 6 09:19:52 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1186129156');

    # S-ODBC Mon Aug 6 09:19:54 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184569928');

    # S-ODBC Mon Aug 6 09:19:54 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184569998');

    # S-ODBC Mon Aug 6 09:19:55 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569928', Groups='Swis', Devices='T12RQCJ', Label='PING T12RQCJ', Type='PING', Status='OK', Upcounter=9901, Downcounter=1, Errors='Error level: 144 (returned value) greater than 50', ServiceLevel='99.98', CheckVal='0' WHERE UID='1184569928';

    # S-ODBC Mon Aug 6 09:19:56 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('0','1184569828');

    # S-ODBC Mon Aug 6 09:19:56 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569828', Groups='Swis', Devices='T12RQCI', Label='PING T12RQCI', Type='PING', Status='OK', Upcounter=9895, Downcounter=1, Errors='Connection to host timed out', ServiceLevel='99.98', CheckVal='0' WHERE UID='1184569828';

    # S-ODBC Mon Aug 6 09:19:56 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1184569998', Groups='Swis', Devices='T12RQCK', Label='PING T12RQCK', Type='PING', Status='OK', Upcounter=9900, Downcounter=1, Errors='Error level: 176 (returned value) greater than 50', ServiceLevel='99.98', CheckVal='0' WHERE UID='1184569998';

    # S-ODBC Mon Aug 6 09:19:56 2007 ODBC: UPDATE SERVERSCHECK_CHECKS SET UID='1186129156', Groups='TEST', Devices='Local System', Label='PING', Type='PING', Status='OK', Upcounter=1418, Downcounter=0, Errors='', ServiceLevel='100', CheckVal='0' WHERE UID='1186129156';

    # S-ODBC Mon Aug 6 09:20:20 2007 ODBC: INSERT INTO SERVERSCHECK_DATA (CheckVal, UID) VALUES ('20','1184251196');

    # S-ODBC Mon



    i found no Insert Into Serverscheck_Checks
  • AdministratorAdministrator
    OK. I will check with our development team. It might be an old restriction in the TRIAL.
  • AdministratorAdministrator
    Download the fixed version of the monitoring_manager.exe from following url and replace the original with the downloaded one:

    http://files.serverscheck.net/fixes/monitoring_manager.zip
  • KauerKauer
    The DateLastCheck is any Time the same DateTime

    your query is:

    UPDATE SERVERSCHECK_CHECKS SET UID='1184251205', Groups='Swis', Devices='Swis Abfragen', Label='Swis HOST Abfrage', Type='URL', Status='OK', Upcounter=14599, Downcounter=140, Errors='Warning level: 3867 (returned value) greater than 3000', ServiceLevel='99.05', CheckVal='308' WHERE UID='1184251205';



    --------------------------------------------------------------------



    UPDATE Serverscheck.dbo.SERVERSCHECK_CHECKS SET UID='1184568528', Groups='Swis', Devices='T12RQCH', Label='PING T12RQCH', Type='PING', Status='OK', Upcounter='31785', Downcounter='41', Errors='Connection to host timed out', ServiceLevel='99.87', CheckVal='0'?, DateLastCheck=getdate() WHERE UID='1184568528';



    with this query is that going but where can i change the querys, or can you change that
  • AdministratorAdministrator
    After checking the time issue a bit further it seems indeed that with the redesign of odbc logging the LastDateCheck is an issue that is being worked at.
  • AdministratorAdministrator
    Download the updated http://files.serverscheck.net/fixes/monitoring_thread2.zip



    This should fix the issue.
  • KauerKauer
    THX

    i wrote yesterday a trigger for that but in the primary query is this better for SQL Server
  • KauerKauer
    What Data fill the dbo.Servercheck Table?

    Downs?
  • KauerKauer
    Here is the Trigger Code for any people



    --------------------------------------------------------------------



    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: Michael Kauer

    -- Create date: 06082007

    -- Description: Update the DateLastCheck Field in dbo.Serverscheck_data

    -- =============================================

    ALTER TRIGGER [dbo].[DateTime]

    ON [dbo].[SERVERSCHECK_CHECKS]

    AFTER UPDATE

    AS

    Begin

    declare DateLastCheck VARCHAR(50)

    SET DateLastCheck=(select UID from serverscheck_data where TimeDate=(select Max(TimeDate) from Serverscheck_Data))

    Update Serverscheck_checks set DateLastCheck=(select Max(TimeDate) from Serverscheck_Data) where UID=DateLastCheck

    End



    --------------------------------------------------------------------
  • KauerKauer
    Infos for your SQL Table Script



    1. In the IDX1_SERVERSCHECK, IDX2_SERVERSCHECK and IDX1_SERVERSCHECK_DATA must don't activatet 'USE INDEX' because Serverchecks can writhe only once in the Table.



    2. In the dbo.SERVERSCHECK must the Column TimeStamps DataType change to text, because your Serverscheck SQL Statemant send the following String:

    --------------------------------------------------------------------

    INSERT INTO SERVERSCHECK (TimeStamps, Uni

    xTimeStamps, Label, Status, Errors, UID) VALUES ('8/7/107 13:34:56',1186486496,'

    PING','DOWN','Error: Error level: 0 (returned value) less than 5','1186129156');

    --------------------------------------------------------------------

    The TimeStamps was 8/7/107 13:34:56 but there was no correct datetime



    3. In the dbo.SERVERSCHECK must the columns Groups and Type, because your Serverscheck SQL Statement send the following String:

    --------------------------------------------------------------------

    INSERT INTO SERVERSCHECK (TimeStamps, UnixTimeStamps, Type, Label, Groups, Status, Errors, UID) VALUES ('8/7/107 13:45:19',1186487119,'WINDOWSHEALTH','WINDOWSHEALTH','System³berwachung','OK','Recovered from last error: CPU Warning level: 86 (returned value) greater than 80','1171011122');

    --------------------------------------------------------------------



    I found this Information in the Debug Mode from ServersCheck here was the Infos:

    --------------------------------------------------------------------

    DBD::ADO::db do failed: Can't Execute 'INSERT INTO SERVERSCHECK (TimeStamps, Uni

    xTimeStamps, Label, Status, Errors, UID) VALUES ('8/7/107 13:20:24',1186485624,'

    PING','OK','Recovered from last error: Error level: 0 (returned value) less than

    5','1186129156');'



    Package : DBD::ADO::db

    Filename : /<C:Program FilesServersCheck_Monitoringmonitoring_thread2.exe

    >DBD/ADO.pm

    Line : 980

    Last error : -2147217913



    OLE exception from "Microsoft OLE DB Provider for ODBC Drivers":



    [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data

    type to a datetime data type resulted in an out-of-range datetime value.



    Win32::OLE(0.1707) error 0x80040e07

    in METHOD/PROPERTYGET "Execute"



    Description : [Microsoft][ODBC SQL Server Driver][SQL Server]The convers

    ion of a char data type to a datetime data type resulted in an out-of-range date

    time value.

    HelpContext :

    HelpFile :

    NativeError : 242

    Number : -2147217913

    Source : Microsoft OLE DB Provider for ODBC Drivers

    SQLState : 22008





    Description : [Microsoft][ODBC SQL Server Driver][SQL Server]The stateme

    nt has been terminated.

    HelpContext :

    HelpFile :

    NativeError : 3621

    Number : -2147217913

    Source : Microsoft OLE DB Provider for ODBC Drivers

    SQLState : 01000

    at monitoring_thread2.pl line 9622.

    --------------------------------------------------------------------



    and



    --------------------------------------------------------------------

    DBD::ADO::db do failed: Can't Execute 'INSERT INTO SERVERSCHECK (TimeStamps, Uni

    xTimeStamps, Type, Label, Groups, Status, Errors, UID) VALUES ('8/7/107 13:45:19

    ',1186487119,'WINDOWSHEALTH','WINDOWSHEALTH','System³berwachung','OK','Recovered

    from last error: CPU Warning level: 86 (returned value) greater than 80','11710

    11122');'



    Package : DBD::ADO::db

    Filename : /<C:Program FilesServersCheck_Monitoringmonitoring_thread2.exe

    >DBD/ADO.pm

    Line : 980

    Last error : -2147217900



    OLE exception from "Microsoft OLE DB Provider for ODBC Drivers":



    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Type'.



    Win32::OLE(0.1707) error 0x80040e14

    in METHOD/PROPERTYGET "Execute"



    Description : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid col

    umn name 'Type'.

    HelpContext :

    HelpFile :

    NativeError : 207

    Number : -2147217900

    Source : Microsoft OLE DB Provider for ODBC Drivers

    SQLState : S0022





    Description : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid col

    umn name 'Groups'.

    HelpContext :

    HelpFile :

    NativeError : 207

    Number : -2147217900

    Source : Microsoft OLE DB Provider for ODBC Drivers

    SQLState : S0022

    at monitoring_thread2.pl line 10001.

    --------------------------------------------------------------------



    Now all is running!!! THX for any help!!!
This discussion has been closed.