I was wondering if you had any plans of making it possible to have 24x7 log entries be sent directly to a database instead of writing to a text file. The reason I am asking is I would like to automate a report of all failed jobs (regular and dynamic) and currently I would have to parse the text file to get this kind of information. Any comment would be helpful. Thank you

You can do it now. You can either use "Database" notification action type or just periodically load the job log file into a database. Because the log is a tab separated file you can easily load it using DatabaseImport command in 24x7 or any other external tool (for example in MS Access use File/Import External Data menu)

1. For the database notification action do the following:
For example, create a table in your database like
CREATE TABLE job_log
( event_time DATETIME DEFAULT GetDate(),
job_id INTEGER,
job_name VARCHAR(255),
message_text VARCHAR(255)
)

In every job check "on error" notification event and select "execute database commands" action type. For the SQL query type
INSERT INTO job_log (job_id, job_name, message_text)
VALUES (@V"job_id", '@V"job_name"', 'Error')

2. For automated log file import do the following:

In the database
CREATE TABLE job_log
( event_time DATETIME,
status INTEGER,
job_id INTEGER,
run_id INTEGER,
job_name VARCHAR(255),
message_text VARCHAR(255),
PRIMARY KEY (event_time, job_id)
)

CREATE TABLE job_log_staging
( event_time DATETIME,
status INTEGER,
job_id INTEGER,
run_id INTEGER,
job_name VARCHAR(255),
message_text VARCHAR(255)
)

In 24x7, create a new JAL job. In the job script code

Dim rows_affected, number
DatabaseConnect "profile name"
DatabaseExecute "DELETE FROM job_log_staging", rows_affected
DatabaseImport "job_log_staging", "@V"24x7_home"\\schedule.log", rows_affected
DatabaseExecute "INSERT INTO job_log SELECT * FROM job_log_staging t2 WHERE NOT EXISTS (SELECT 1 FROM job_log t2 WHERE t.2event_time = t.event_time AND t2.job_id = t.job_id)", rows_affected
DatabaseDisconnect

Schedule this job to run as often as wanted, let's say once a day or every hour.

: I was wondering if you had any plans of making it possible to have 24x7 log
: entries be sent directly to a database instead of writing to a text file.
: The reason I am asking is I would like to automate a report of all failed
: jobs (regular and dynamic) and currently I would have to parse the text
: file to get this kind of information. Any comment would be helpful. Thank
: you

Thank you for your response. I have tried to use your solutions,
but have a few problems.

Is this possible with dynamically created jobs?
: In every job check "on error" notification event and select

The second thing is I tried the second solution, and when
it tries to import the schedule.log file it gives an error saying
that the file is not tab-seperated file

Thank you
Kelly Grandstaff

There is really no difference between manually and programmatically created jobs. You can always change any job property dynamically.

Regarding the schedule.log import. It expects to find a file with standard .TXT extension that's why it does not like it.
Use something like the following

FileCopy "@V"24x7_home"\\schedule.log", "@V"24x7_home"\schedule_log.txt"

before the import then import the copied file.

: Thank you for your response. I have tried to use your solutions,
: but have a few problems.

: Is this possible with dynamically created jobs?

: The second thing is I tried the second solution, and when
: it tries to import the schedule.log file it gives an error saying
: that the file is not tab-seperated file

: Thank you
: Kelly Grandstaff

When we implemented this code on our production server, the 24x7 Scheduler
software dies periodically, when attempting to do the DatabaseImport command.

Here's our JAL code:

dim rows_affected, number
dim sql_command, string

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Connecting to DB...."
DatabaseConnect "PCCDB"

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Clearing staging table...."
DatabaseExecute "DELETE FROM HISTORY.PCC_24X7JOBLOG_STAG", rows_affected

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Duplicating the log file...."
FileCopy "@V"24x7_home"\\schedule.log", "@V"24x7_home"\schedule_log.txt"

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Executing DB Import...."
DatabaseImport "HISTORY.PCC_24X7JOBLOG_STAG", "@V"24x7_home"\schedule_log.txt", rows_affected

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Building INSERT statement...."
concatex "INSERT INTO HISTORY.PCC_24X7JOBLOG (event_time, status, job_id, run_id, job_name, message_text) ", sql_command
concatex sql_command, "SELECT DISTINCT event_time, status, job_id, run_id, job_name, message_text ", sql_command
concatex sql_command, "FROM HISTORY.PCC_24X7JOBLOG_STAG t1 WHERE NOT EXISTS ", sql_command
concatex sql_command, "(SELECT event_time, status, job_id, run_id, job_name, message_text ", sql_command
concatex sql_command, "FROM HISTORY.PCC_24X7JOBLOG t2 WHERE t2.event_time = t1.event_time AND ", sql_command
concatex sql_command, "t2.job_id = t1.job_id AND t2.message_text = t1.message_text)", sql_command

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Executing...."
DatabaseExecute sql_command, rows_affected

logaddmessageex "INFO", @V"job_id", "@V"job_name"", "Disconnecting from DB...."
DatabaseDisconnect

Our 24x7 log entries indicate that 24x7 died right after the "Executing DB Import...." line shows up in the log:

2/10/2006 04:20:02 0 45227 0 24x7_Logfile_Import Job started.
2/10/2006 04:20:02 0 45227 0 24x7_Logfile_Import Connecting to DB....
2/10/2006 04:20:02 0 45227 0 24x7_Logfile_Import Clearing staging table....
2/10/2006 04:20:03 0 45227 0 24x7_Logfile_Import Duplicating the log file....
2/10/2006 04:20:03 0 45227 0 24x7_Logfile_Import Executing DB Import....
2/10/2006 04:20:09 0 45227 0 24x7_Logfile_Import Building INSERT statement....
2/10/2006 04:20:09 0 45227 0 24x7_Logfile_Import Executing....
2/10/2006 04:20:10 0 45227 0 24x7_Logfile_Import Disconnecting from DB....
2/10/2006 04:20:10 0 45227 0 24x7_Logfile_Import Job finished.
2/10/2006 04:30:02 0 11699 0 Delete Dynamic Jobs Job started.
2/10/2006 04:30:03 0 11699 0 Delete Dynamic Jobs Backing up the job database to C:\Program Files\24x7 Automation 3\LogFile_History\20060210043002_scheduler.dat
2/10/2006 04:30:03 0 11699 0 Delete Dynamic Jobs Deleting dynamic jobs from database
2/10/2006 04:35:51 0 45227 0 24x7_Logfile_Import Job started.
2/10/2006 04:35:55 0 45227 0 24x7_Logfile_Import Connecting to DB....
2/10/2006 04:35:55 0 45227 0 24x7_Logfile_Import Clearing staging table....
2/10/2006 04:35:56 0 45227 0 24x7_Logfile_Import Duplicating the log file....
2/10/2006 04:35:57 0 45227 0 24x7_Logfile_Import Executing DB Import....
2/10/2006 04:36:04 0 45227 0 24x7_Logfile_Import Building INSERT statement....
2/10/2006 04:36:05 0 45227 0 24x7_Logfile_Import Executing....
2/10/2006 04:36:08 0 45227 0 24x7_Logfile_Import Disconnecting from DB....
2/10/2006 04:36:09 0 45227 0 24x7_Logfile_Import Job finished.
2/10/2006 04:36:38 0 11699 0 Delete Dynamic Jobs Finished deleting dynamic jobs from database
2/10/2006 04:36:38 0 11699 0 Delete Dynamic Jobs Job finished.
2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Job started.
2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Connecting to DB....
2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Clearing staging table....
2/10/2006 04:50:02 0 45227 0 24x7_Logfile_Import Duplicating the log file....
2/10/2006 04:50:02 0 45227 0 24x7_Logfile_Import Executing DB Import....
2/10/2006 08:04:23 0 0 0 24x7 Scheduler 24x7 Scheduler starting...
2/10/2006 08:04:27 0 1159 0 24x7 startup Job started.

Notice that the last entry at 4:50 this morning was the import line, and then the next entry was
the 24x7 Scheduler being restarted when we realized the problem this morning.... Also, the import right
before it worked fine, too.

The 24x7 Scheduler software didn't give any indication of why it died....

Ideas?

: There is really no difference between manually and programmatically created
: jobs. You can always change any job property dynamically.

: Regarding the schedule.log import. It expects to find a file with standard
: .TXT extension that's why it does not like it.
: Use something like the following

: FileCopy "@V"24x7_home"\\schedule.log",
: "@V"24x7_home"\schedule_log.txt"

: before the import then import the copied file.

1. First thing to do: ensure that the jb is set to run detached.
2. Second thing: Compare versions of database client software that you got in development and production and if you find that production version is different resolve the differences.
3. Third: ensure you use proper account for database connection and that account got the required permissions
4. Forth: If the job keeps failing in production, temporary enable database tracing option in the Tools/Options/Log; run the job manually and then turn off the database tracing. Analyze the resulting database trace file and check it for unusual errors and messages. If you find an error caused by some db objects, fix the problem in db.

: When we implemented this code on our production server, the 24x7 Scheduler
: software dies periodically, when attempting to do the DatabaseImport command.

: Here's our JAL code: dim rows_affected, number
: dim sql_command, string

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Connecting to DB...."
: DatabaseConnect "PCCDB"

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Clearing staging table...."
: DatabaseExecute "DELETE FROM HISTORY.PCC_24X7JOBLOG_STAG",
: rows_affected

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Duplicating the log
: file...."
: FileCopy "@V"24x7_home"\\schedule.log",
: "@V"24x7_home"\schedule_log.txt"

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Executing DB Import...."
: DatabaseImport "HISTORY.PCC_24X7JOBLOG_STAG",
: "@V"24x7_home"\schedule_log.txt", rows_affected

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Building INSERT
: statement...."
: concatex "INSERT INTO HISTORY.PCC_24X7JOBLOG (event_time, status,
: job_id, run_id, job_name, message_text) ", sql_command
: concatex sql_command, "SELECT DISTINCT event_time, status, job_id,
: run_id, job_name, message_text ", sql_command
: concatex sql_command, "FROM HISTORY.PCC_24X7JOBLOG_STAG t1 WHERE NOT
: EXISTS ", sql_command
: concatex sql_command, "(SELECT event_time, status, job_id, run_id,
: job_name, message_text ", sql_command
: concatex sql_command, "FROM HISTORY.PCC_24X7JOBLOG t2 WHERE
: t2.event_time = t1.event_time AND ", sql_command
: concatex sql_command, "t2.job_id = t1.job_id AND t2.message_text =
: t1.message_text)", sql_command

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Executing...."
: DatabaseExecute sql_command, rows_affected

: logaddmessageex "INFO", @V"job_id",
: "@V"job_name"", "Disconnecting from DB...."
: DatabaseDisconnect

: Our 24x7 log entries indicate that 24x7 died right after the "Executing
: DB Import...." line shows up in the log: 2/10/2006 04:20:02 0 45227 0
: 24x7_Logfile_Import Job started.
: 2/10/2006 04:20:02 0 45227 0 24x7_Logfile_Import Connecting to DB....
: 2/10/2006 04:20:02 0 45227 0 24x7_Logfile_Import Clearing staging table....
: 2/10/2006 04:20:03 0 45227 0 24x7_Logfile_Import Duplicating the log file....
: 2/10/2006 04:20:03 0 45227 0 24x7_Logfile_Import Executing DB Import....
: 2/10/2006 04:20:09 0 45227 0 24x7_Logfile_Import Building INSERT
: statement....
: 2/10/2006 04:20:09 0 45227 0 24x7_Logfile_Import Executing....
: 2/10/2006 04:20:10 0 45227 0 24x7_Logfile_Import Disconnecting from DB....
: 2/10/2006 04:20:10 0 45227 0 24x7_Logfile_Import Job finished.
: 2/10/2006 04:30:02 0 11699 0 Delete Dynamic Jobs Job started.
: 2/10/2006 04:30:03 0 11699 0 Delete Dynamic Jobs Backing up the job database
: to C:\Program Files\24x7 Automation
: 3\LogFile_History\20060210043002_scheduler.dat
: 2/10/2006 04:30:03 0 11699 0 Delete Dynamic Jobs Deleting dynamic jobs from
: database
: 2/10/2006 04:35:51 0 45227 0 24x7_Logfile_Import Job started.
: 2/10/2006 04:35:55 0 45227 0 24x7_Logfile_Import Connecting to DB....
: 2/10/2006 04:35:55 0 45227 0 24x7_Logfile_Import Clearing staging table....
: 2/10/2006 04:35:56 0 45227 0 24x7_Logfile_Import Duplicating the log file....
: 2/10/2006 04:35:57 0 45227 0 24x7_Logfile_Import Executing DB Import....
: 2/10/2006 04:36:04 0 45227 0 24x7_Logfile_Import Building INSERT
: statement....
: 2/10/2006 04:36:05 0 45227 0 24x7_Logfile_Import Executing....
: 2/10/2006 04:36:08 0 45227 0 24x7_Logfile_Import Disconnecting from DB....
: 2/10/2006 04:36:09 0 45227 0 24x7_Logfile_Import Job finished.
: 2/10/2006 04:36:38 0 11699 0 Delete Dynamic Jobs Finished deleting dynamic
: jobs from database
: 2/10/2006 04:36:38 0 11699 0 Delete Dynamic Jobs Job finished.
: 2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Job started.
: 2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Connecting to DB....
: 2/10/2006 04:50:01 0 45227 0 24x7_Logfile_Import Clearing staging table....
: 2/10/2006 04:50:02 0 45227 0 24x7_Logfile_Import Duplicating the log file....
: 2/10/2006 04:50:02 0 45227 0 24x7_Logfile_Import Executing DB Import....
: 2/10/2006 08:04:23 0 0 0 24x7 Scheduler 24x7 Scheduler starting...
: 2/10/2006 08:04:27 0 1159 0 24x7 startup Job started.

: Notice that the last entry at 4:50 this morning was the import line, and then
: the next entry was
: the 24x7 Scheduler being restarted when we realized the problem this
: morning.... Also, the import right
: before it worked fine, too.

: The 24x7 Scheduler software didn't give any indication of why it died....

: Ideas?