Query Job Database

Is there a way to create a custom report against the 24x7 job database. We are getting a lot of jobs, with many interdependancies. I was hoping to create a grid showing each job from 24x7 and include the following information:

Job No:
Job Name:
Description:
Schedule Type:
Start Time:
Semaphore File / Process:
Created Semaphore Files:

I was hoping to sent to delimited text file or relational db.

Thanks,
Jeff

This task is very easy if you got 24x7 Windows version. I suggest first exporting jobs and their properties to a relational database, for example, to MS Access, which also has built-in reporting tools. Then create a report displaying what you need. To get the stuff exported, use Tools/Import-Export Job Database menu.

If you got the 24x7 Multi-platform version you would need to write a little JavaScript or Java program to export the jobs to a file first then manually load the exported file to a database.

If you need further assistance, please let us know which version and the build number you got there.

Thanks for the information. I have the windows version and was able to successfully export to MS Access.
Jeff

I am trying to export the database on the Windows scheduler and getting an error.

Row #134 Bind Parameter value for 42 is too big (6)

Which database are you exporting to? Using which database connectivity driver? During which export step this error occurs? while exporting job definitions, logs, etc...)? For the last question check what it says on the wizard screen and in the status bar.

	SysOp wrote:
	If you got the 24x7 Multi-platform version you would need to write a little JavaScript or Java program to export the jobs to a file first then manually load the exported file to a database. If you need further assistance, please let us know which version and the build number you got there.

I'd like to do this with Multi-platform 4.1 427 under Windows using JavaScript but I've only tinkered with JS--do you have some sample code for exporting to a text file?

There is a command in 24x7 JavaScript that can be used to programmatically retrieve job property values, for example,

var command = Scheduler.getJobProperty( 125, "COMMAND" );

Here the first parameter is the job id the second parameter is a property name. Properties names are documented in the help. For example, if you want to create a tab-separated file containing 3 columns: job id, name and command you would need to write a loop like the following

	Code:
	var i=0; for (i=1; i < 1000; i++) { try { var text_line = Scheduler.getJobProperty( i, "ID" ) + '\t' + Scheduler.getJobProperty( i, "NAME" ) + '\t' + Scheduler.getJobProperty( i, "COMMAND" ) + '\n'; File.write(f, text_line); } catch (error) { } }

Now to make it work, you need to open a target file before the loop and close it after the loop

	Code:
	var f = File.open("test.txt", "ReadWrite", true); ...loop goes here... File.close(f);

Practically you will need to decide which job properties you want to export and add them to the loop. The resulting file can be then loaded into some database, for example,

after file close

	Code:
	Process.run("bcp pubs..my_table in test.txt -c -t -E");

In this case you will need to prepare the target table before loading and make a column for each job property that you want to export.

Thanks SysOp!

I needed to add a step to create the file before opening it. I ended up generating an XML copy of the job database, exporting every field. Here's the code in case anybody else can use it.

Code:

// 2008-01-15 Tom Robinson based on sample from SoftTree Technologies

// Note we don't try to catch '<', CDATA termination, etc. within text fields

var TagFields=new Array("ID","ACCOUNT","ALL_DAY_TYPE","AGENT","ASYNC","BACKUP_AGENT","BACKUP_HOST","CALENDAR","COMMAND",
"DAY_END_TIME","DAY_LIST","DAY_NAME","DAY_NUMBER","DAY_START_TIME","DELAY","DELETE_RULE","DISABLE_ON_ERROR","DISABLED",
"DETACHED","END_DATE","END_TIME","EXIT_CODE","FILE","FOLDER","FOLDER_NAME","FRIDAY","HOST","IGNORE_ERRORS","INIT_TIMEOUT",
"INTERVAL","JOB_TYPE","KEYSTROKE","LOG","MESSAGE","MODIFY_TERMINAL","MODIFY_TIME","MODIFY_USER","MONDAY",
"MONTHLY_TYPE","MOVE_DIR","MSG_ACCOUNT","MSG_ACTIONS","MSG_DATABASE","MSG_E-MAIL","MSG_ERROR","MSG_FILE",
"MSG_FILE_NAME","MSG_FINISH","MSG_JOB","MSG_JOB_ID","MSG_LATE","MSG_NET","MSG_NET_RECIPIENT","MSG_NOTFOUND",
"MSG_PAGE","MSG_PAGER","MSG_PASSWORD","MSG_PROFILE","MSG_PROGRAM","MSG_PROGRAM_NAME","MSG_PROGRAM_TIMEOUT",
"MSG_RECIPIENT","MSG_SCRIPT","MSG_SCRIPT_TYPE","MSG_START","MSG_TRAP","NAME","NUMBER_OF_RETRIES","PASSWORD",
"POLLING_INTERVAL","PRIORITY","PROFILE","PROTECTION","QUEUE","REBOOT","RETRY_INTERVAL","RENAME_SUFFIX","RETRY_ON_ERROR",
"RUNAS_DOMAIN","RUNAS_PASSWORD","RUNAS_USER","SATURDAY","SAVE_ATTACHMENT","SCHEDULE_TYPE","SCRIPT_TYPE",
"SEND_KEYSTROKE","SIZE_CHECK_INTERVAL","SKIP","SKIP_HOLIDAY","SLIDE_HOLIDAY","SQL","START_DATE","START_IN",
"START_TIME","SUBJECT","SUNDAY","TIME_LIST","THURSDAY","TIMEOUT","TUESDAY","WEDNESDAY","WINDOW"); // 24x7 fields to write to straight tags

var CdataFields=new Array("DESCRIPTION","SCRIPT"); // 24x7 fields to wrap in CDATA

File.save("Database Export.xml","<jobdatabase>\n");

var f=File.open("Database Export.xml","Write",true); // true=append

var i=0;
for (i=1; i < 1000; i++) { // Assume won't be more than 1,000 jobs defined
try {

   if (Scheduler.getJobProperty(i,"ID") > "")
   {
      textline="";
      for (j in TagFields)
      {
         ThisField=TagFields[j];
         ThisData=Scheduler.getJobProperty(i,ThisField);
         // if (ThisField=="E-MAIL") {var ThisField="E_MAIL"};
         if (ThisData > "") {var textline=textline+"\t\t<"+ThisField+">"+ThisData+"</"+ThisField+">\n"};
      }

      for (j in CdataFields)
      {
         ThisField=CdataFields[j];
         ThisData=Scheduler.getJobProperty(i,ThisField);
         if (ThisData > "") {var textline=textline+"\t\t<"+ThisField+"><![CDATA[\n"+ThisData+"\n]"+"]></"+ThisField+">\n"}; // Note splitting up the CDATA termination so we don't trigger it when exporting ourself
      }

      File.write(f,"\t<job>\n"+textline+"\t</job>\n");
   }
}
catch (error) {
}
}

File.write(f,"</jobdatabase>\n");

File.close(f);

(2008-01-16 Update to use arrays)

Last edited by barefootguru on Tue Jan 15, 2008 6:08 pm; edited 2 times in total

Beautiful. Thanks for sharing!