Managing files and folders using Java in Oracle PL/SQL

Collection of Java utilities for work with files anf folders in Oracle PL/SQL.
For testing I used Java 6 and Oracle 11.2.0.1.

1. Create a number of important facilities for testing and the work of our utilities.

CREATE TABLE BLOB_TAB
( F_ID    INTEGER,
  F_NAME  VARCHAR2(128 BYTE),
  F_BLOB  BLOB,
  F_LEN   VARCHAR2(20 BYTE) );

CREATE SEQUENCE BLOB_TAB_SEQ
  START WITH 1
  MAXVALUE 9999999
  MINVALUE 1
  NOCYCLE
  CACHE 5
  ORDER;

CREATE GLOBAL TEMPORARY TABLE ATTR_FILES
( DIRNAME   VARCHAR2(255 BYTE),
  FILENAME  VARCHAR2(255 BYTE),
  LENGTH    NUMBER,
  MOD_DATE  DATE,
  TYPE      VARCHAR2(1 BYTE) )
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE TYPE LIST_FILES AS TABLE OF VARCHAR2(255);

2. Create Java source named «Lobster».

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Lobster" as
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.jdbc.OracleTypes;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import oracle.sql.driver.*;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;

// What's: Collection java utilities for work with files, folders, CLOB and BLOB.
// Author: souluran adn Denis Popov (http://www.sql.ru/faq/faq_topic.aspx?fid=469)

public class Lobster {

// Procedure set encoding for Java = cp1251 (for situations where database character set is AL32UTF8), otherwise there not will be read Russian characters
  public static void encod()
  {
    System.setProperty("file.encoding","cp1251");
  }

// Procedure get list of file names in directory
  public static ARRAY list_files(String dirname)
  throws SQLException {
    encod();
    Connection con = null;
    ARRAY listed = null;
    con = getConnection();
    File dir = new File(dirname);
    try{
        if (dir.exists()) {
            ArrayDescriptor arrayDescriptor = new ArrayDescriptor("LIST_FILES",con);
            listed = new ARRAY(arrayDescriptor,con,((Object[])dir.list()));
        }
    } finally {
    if (con != null) {con.close();}
      }
  return listed;
  }

// Procedure get list of attributes files in directory
  public static void list_files_attr(String dirname)
  throws SQLException {
     encod();
     File path = new File(dirname);

     if (path.exists()) {

        String[] list = path.list();
        String element;

        File fileObject;
        long length;
        String dateStr, type;
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");

        for(int i = 0; i < list.length; i++)
        {
            element = list[i];
            fileObject = new File(dirname + File.separatorChar + list[i]);
            length = fileObject.length();
            if (fileObject.isDirectory()) {
              type = "d";
            } else if (fileObject.isFile()) {
              type = "f";
            } else {
              type = "?";
            }

            java.util.Date d = new java.util.Date(fileObject.lastModified());
            dateStr = format.format(d);

            #sql { INSERT INTO ATTR_FILES (dirname, filename, length, mod_date, type)
                   VALUES (:dirname, :element, :length, to_date(:dateStr, 'dd.mm.yyyy hh24:mi:ss'), :type) };
        }
     }
  }

// Function get modified time file
  public static String getfiletime(String dirname, String fileName)
  throws SQLException {
     encod();
     File path = new File(dirname + "\\" + fileName);
     String dateStr = null;
     if (path.exists()) {
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");
        java.util.Date d = new java.util.Date(path.lastModified());
        dateStr = format.format(d);
     }
     return dateStr;
  }

// Function check exixsting file in directory
  public static oracle.sql.NUMBER existsfile(String dirname, String fileName) throws Exception {
    //System.setProperty("file.encoding","cp1251");
    encod();
    int res = 0;
    File theDir = new File(dirname + "\\" +fileName);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure move file
  public static void movefile(String dirname, String fileName, String newdirname, String newfileName)
  throws IOException {
    encod();
    //String myfile = new String(fileName.getBytes("windows-1251"), "windows-1251");
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
        srcFile.delete();
    }
  }

// Procedure copy file
  public static void copyfile(String dirname, String fileName, String newdirname, String newfileName)
  throws Exception, IOException {
    encod();
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
    }

  }

// Procedure delete file
  public static void deletefile(String dirname, String fileName)
  throws IOException {
    System.setProperty("file.encoding","cp1251");
    try {
        File srcFile = new File(dirname + "\\" + fileName);
        srcFile.delete();
    } finally {
    }
  }

// Procedure create new directory in Windows
  public static void newdir(String dirname)
  throws Exception {
    encod();
    File theDir = new File(dirname);
    // if the directory does not exist, create it
    if (!theDir.exists()) {
    //System.out.println("creating directory: " + dirname);
    boolean result = false;
        try{
            theDir.mkdir();
            result = true;
            }
        catch(SecurityException se){
            //handle it
            }
    if(result) {
               //System.out.println(dirname + " created");
               }
                          }
  }

// Function check exixsting directory in Windows
  public static oracle.sql.NUMBER existsdir(String dirname) throws Exception {
    encod();
    int res = 0;
    File theDir = new File(dirname);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure recursive search files in directory
  public static void recursivecopy(File src, File dest)
  throws Exception, IOException  {
    encod();
        if(src.isDirectory()){

            //if directory not exists, create it
            if(!dest.exists()){
               dest.mkdir();
               System.out.println("Directory copied from "
                              + src + "  to " + dest);
            }

            //list all the directory contents
            String files[] = src.list();

            for (String file : files) {
               //construct the src and dest file structure
               File srcFile = new File(src, file);
               File destFile = new File(dest, file);
               //recursive copy
               recursivecopy(srcFile, destFile);
            }

        }else{
            //if file, then copy it
            //Use bytes stream to support all file types
            InputStream in = new FileInputStream(src);
                OutputStream out = new FileOutputStream(dest);

                byte[] buffer = new byte[1024];

                int length;
                //copy the file content in bytes
                while ((length = in.read(buffer)) > 0){
                   out.write(buffer, 0, length);
                }

                in.close();
                out.close();
                System.out.println("File copied from " + src + " to " + dest);
        }
  }

// Procedure copy directory in Windows
  public static void copydir(String srcDir, String destDir) throws Exception, IOException {
        encod();
        File src = new File(srcDir);
        File dest = new File(destDir);
        recursivecopy(src, dest);
  }

// Procedure recursive search in directory
  private static void recursiveDelete(File file) {
      System.setProperty("file.encoding","cp1251");
         if (!file.exists()) return;
         if (file.isDirectory()) {
            for (File f : file.listFiles()) recursiveDelete(f);
            file.delete();
         } else {
            file.delete();
         }
  }

// Procedure delete directory with all contetns in Windows
  public static void deletedir(String dirname) throws IOException  {
         encod();
         File directory = new File(dirname);
         recursiveDelete(directory);
  }

// Function import file into CLOB
  public static CLOB file2clob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    InputStream in = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      byte[] buf = new byte[clob.getChunkSize()];
      int length;
      while ((length = in.read(buf)) != -1) {
        writer.write(new String(buf, 0, length));
      }
    } finally {
      if (in != null) {in.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Function import file with characterSet into CLOB
  public static CLOB file2clob(String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      reader = new InputStreamReader(
        new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      char[] chars = new char[clob.getChunkSize()];
      int iChar;
      while ((iChar = reader.read(chars)) != -1) {
        writer.write(chars, 0, iChar);
      }
    } finally {
      if (reader != null) {reader.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Procedure export CLOB into file
  public static void clob2file(CLOB clob, String dirname, String fileName)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new BufferedWriter(new FileWriter(dirname + "\\" + fileName));
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Procedure export CLOB into file with characterSet
  public static void clob2file(CLOB clob, String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new OutputStreamWriter(
        new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Function import file into BLOB
  public static BLOB file2blob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    BLOB blob = null;
    OutputStream out = null;
    InputStream in = null;
    //Locale.setDefault(new Locale("ru", "RUS", "WIN"));
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      blob = BLOB.createTemporary(con, true, BLOB.DURATION_SESSION);
      // For Oracle 8i.
//      blob = createTemporaryBlob(con);
      out = blob.getBinaryOutputStream();
      String myfile = null;
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      int length;
      int chunkSize = blob.getChunkSize();
      byte[] buf = new byte[chunkSize];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
      if (con != null) {con.close();}
    }
    return blob;
  }

// Procedure export BLOB into file
  public static void blob2file(BLOB blob, String dirname, String fileName)
    throws SQLException, IOException {
    InputStream in = null;
    OutputStream out = null;
    try {
      encod();
      out = new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName)));
      in = blob.getBinaryStream();
      int length;
      byte[] buf = new byte[blob.getChunkSize()];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
    }
  }

// Function connection
  public static Connection getConnection()
    throws SQLException {
    return DriverManager.getConnection("jdbc:default:connection:");
  }

// Function create temporary CLOB
  public static CLOB createTemporaryClob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.CLOB);
      cst.execute();
      return (CLOB)cst.getClob(1);
    } finally {
      if (cst != null) { cst.close(); }
    }
  }

// Function create temporary BLOB
  public static BLOB createTemporaryBlob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.BLOB);
      cst.execute();
      return (BLOB)cst.getBlob(1);
    } finally {
      if (cst != null) { cst.close();
        }
    }
  }

}
/

3. Then create a package of «Lobster» for publication the Java call specification, so we can access it via PL/SQL.

CREATE OR REPLACE PACKAGE Lobster as

/* Package of utilities for work with files, folders, CLOB and BLOB */

/* get list of files from directory */
function list_files(p_dir_name VARCHAR2) RETURN list_files AS
language java name 'Lobster.list_files(java.lang.String) return oracle.sql.ARRAY';

/* get list of attributes files from directory */
procedure list_files_attr(p_dir_name varchar2) as
language java name 'Lobster.list_files_attr(java.lang.String)';

/* file exists or not */
function existsfile(p_dir_name varchar2, p_file_name varchar2) return number as
language java name 'Lobster.existsfile(java.lang.String, java.lang.String) return oracle.sql.NUMBER';

/* move file to another directory */
procedure movefile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.movefile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* copy file to another directory */
procedure copyfile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.copyfile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* delete file from directory */
procedure deletefile (p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.deletefile(java.lang.String, java.lang.String)';

/* create new directory or subdirectory */
procedure newdir(p_dir_name varchar2) as
language java name 'Lobster.newdir(java.lang.String)';

/* directory exists or not */
function existsdir(p_dir_name varchar2) return number as
language java name 'Lobster.existsdir(java.lang.String) return oracle.sql.NUMBER';

/* delete directory or subdirectory with all contents */
procedure deletedir(p_dir_name varchar2) as
language java name 'Lobster.deletedir(java.lang.String)';

/* copy all contents from directory to another directory */
procedure copydir(p_dir_name varchar2, p_newdir_name varchar2) as
language java name 'Lobster.copydir(java.lang.String, java.lang.String)';

/* import file into CLOB */
function file2clob(p_dir_name varchar2, p_file_name varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import file into CLOB with encoding */
function file2clob(p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import CLOB into file */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String)';

/* import CLOB into file with encoding */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String, java.lang.String)';

/* import file into BLOB */
function file2blob(p_dir_name varchar2, p_file_name varchar2) return blob as
language java name 'Lobster.file2blob(java.lang.String, java.lang.String) return oracle.sql.BLOB';

/* import BLOB into file */
procedure blob2file(p_blob blob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.blob2file(oracle.sql.BLOB, java.lang.String, java.lang.String)';

end;
/ 

4. Grant the Oracle JVM the relevant filesystem permissions.

DECLARE
  l_schema VARCHAR2(30) := 'USERNAME';
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.util.PropertyPermission', 'file.encoding', 'write' );
END;
/

5. Examples how use package Lobster:

-- import file into BLOB
DECLARE

l_id INT DEFAULT 1;
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file.xls';

select BLOB_TAB_SEQ.NEXTVAL into l_id from dual;

INSERT INTO blob_tab
VALUES(l_id, null, EMPTY_BLOB(), 0);

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = l_id
FOR UPDATE;

l_blob := lobster.file2blob(l_dir, l_name);

UPDATE blob_tab
SET f_name = l_name, f_blob = l_blob, f_len = dbms_lob.getlength(l_blob)
where f_id = l_id;
COMMIT;

END;
/

-- import BLOB into file
DECLARE
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file2.xls';

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = :id;

lobster.blob2file(l_blob, l_dir, l_name);

END;
/

-- list of file name
SELECT column_value FROM TABLE(lobster.list_files('d:\mydir'));

--list of file attributes
begin
execute immediate 'truncate table ATTR_FILES';
lobster.list_files_attr('d:\mydir');
end;
/
select * from ATTR_FILES;

-- manage file and folders
declare
res number;
begin
-- copy file to another folder
lobster.copyfile('d:\mydir', 'test.txt', 'd:\my', 'test2.txt');
-- move file to another folder
lobster.movefile('d:\mydir', 'test.txt', 'd:\my', 'test.txt');
-- delete file
lobster.deletefile('d:\my', 'test.txt');
-- create new folder
lobster.newdir('d:\my5');
-- delete folder
lobster.deletedir('d:\my5');
-- copy all contetnts from folder to anothe folder
lobster.copydir('d:\my', 'd:\mytest');
-- exists file
res := lobster.existsfile('d:\my', 'test.txt');
dbms_output.put_line('file is ' || res);
res := lobster.existsdir('d:\my');
dbms_output.put_line('folder is ' || res);
end;
/ 
Реклама

What encoding string UTF8 or WIN1251

Пример, как определить кодировку строки UTF8 или WIN1251?

with t as
(select 'Текст в кодировке UTF8' filename from dual union
select 'Текст в кодировке WIN1251' filename from dual union
select 'Text does not contain cyrilic charcters' filename from dual)
select filename,
case
    when not regexp_like(asciistr(convert(filename, 'cl8mswin1251')), '\FFFD') then
        case
            when regexp_like(convert(filename, 'cl8mswin1251', 'utf8'),'[а-яА-Я]') then 'UTF8'
            else 'WIN1251'
        end
        else 'WIN1251'
end ENCODE from t
order by 2;

FILENAME                                  ENCODE
----------------------------------------- -------
Текст РІ РєРѕРґРёСЂРѕРІРєРµ UTF8     UTF8   
Текст в кодировке WIN1251                 WIN1251
Text does not contain cyrilic charcters   WIN1251

Теперь дополним пример конвертацией текста UTF8 в WIN1251:

with t as
(select 'Текст в кодировке UTF8' filename from dual union
select 'Текст в кодировке WIN1251' from dual union
select 'Text does not contain cyrilic charcters' from dual)
select
encode,
case
    when ENCODE = 'WIN1251' then filename
    else convert(filename, 'cl8mswin1251', 'utf8')
end decode_filename
from(
select filename,
case
    when not regexp_like(asciistr(convert(filename, 'cl8mswin1251')), '\FFFD') then
        case
            when regexp_like(convert(filename, 'cl8mswin1251', 'utf8'),'[а-яА-Я]') then 'UTF8'
            else 'WIN1251'
        end
        else 'WIN1251'
end ENCODE from t)
order by 2;
                                      
ENCODE    DECODE_FILENAME
--------  -----------------------------------------
UTF8      Текст в кодировке UTF8
WIN1251   Текст в кодировке WIN1251
WIN1251   Text does not contain cyrilic charcters                                                      

 

 

VBScript collect events print from Windows logs

Скрипт позволяет собирать события печати из журналов Windows с локального или удаленного компьютера(ов) и импортировать их в базу Oracle.

Требования для машины, на которой запускаете скрипт:

  • ОС Windows  (7, XP, Server 2003, Server 2008)
  • Oracle client (or instant client) version 10.2 and above.
  • Запускать скрипт под пользователем, который входит в группу Администрирования на других компьютерах сети.

Требования к базе Oracle:

  • Oracle DB 10.2 and above.
  • Создать в какой-нибудь схеме базы Oracle таблицы:
/* список компьютеров, по которым нужно собирать события печати */
CREATE TABLE PRINTLOG_COMPUTERS
(COMPUTER       VARCHAR2(100));
/* сюда будут писаться данные по печати */
CREATE TABLE PRINTLOG
(SYSDATETIME    DATE,
CREATEDTIME     DATE,
OS              VARCHAR2(50),
EVENTID         NUMBER,
SOURCENAME      VARCHAR2(128),
COMPUTER        VARCHAR2(100),
USERNAME        VARCHAR2(128),
MESSAGE         VARCHAR2(4000));
/* сюда будут писаться ошибки, возникающие при сборе событий */
CREATE TABLE PRINTLOG_ERROR
(SYSDATETIME    DATE,
COMPUTER        VARCHAR2(100),
ERRORMESSAGE    VARCHAR2(512));
  • В таблицу PRINTLOG_COMPUTERS внести имена компьютеров, по которым будет собираться статистика печати.

Требования к компьютерам, с которых будут собираться события печати из журналов Windows:

  • Желательно, чтобы компьютеры были под управлением Windows (7, XP, Server 2003, Server 2008)
  • И чтобы находились в одном домене

Исходник скрипта, с комментариями:

'### [ VBScript ]
'### (c) 02.04.2015
'### What: collects events printing (10, 307) from Windows events logs on local or remote computer and writes them to the Oracle database
'### Author: souluran

Dim objWMIService
Dim colLoggedEvents
Dim colLogFiles
Dim ObjLogFile
Dim objEvent
Dim errCon
Dim errMessage
Dim strSearchString
Dim con
Dim objRecordset
Dim PROC, SQL, ERRINS, INS
Dim objRegistry
Dim strKeyPath
Dim flag
Dim arrSubKeys
Dim subkey
Dim EventCode
Dim LogFile
Dim OverWrite
Const HKEY_LOCAL_MACHINE = &H80000002

On Error Resume Next

'--> connection parameters to Oracle
Driver   = "************" '--> for example "Oracle in instantclient_11_2"
dbName   = "************"
dbUser   = "************" '--> better to have a user with the DBA or have privileges Select/Insert/Delete on tables PRINTLOG_COMPUTERS, PRINTLOG, PRINTLOG_ERROR
Password = "************"
dbSchema = "************" '--> scheme that created the table PRINTLOG_COMPUTERS, PRINTLOG, PRINTLOG_ERROR

'--> open connect to Oracle
Set con = CreateObject("ADODB.Connection")
con.ConnectionTimeOut = 20
con.CommandTimeout = 120
con.Open "Driver={" & Driver & "};DBQ=" & dbName & ";UID=" & dbUser & ";PWD=" & Password

Err.Clear
'--> check connect to Oracle
If Err.Number <> 0 Then
    'error handling:
    errCon = "ORACLE: " & Err.Number & " Srce: " & Err.Source & " Desc: " &  Err.Description
    'WScript.Echo errStr
    Err.Clear
    'WScript.Quit
End If

'--> set the start and the end dates for the collection
begdate = "20150101 00:00:00"
enddate = "20150430 23:59:59"

'--> get list of computers
SQL = "select COMPUTER from " & dbSchema & ".PRINTLOG_COMPUTERS order by 1"
Set objRecordset = con.Execute(SQL)
Do Until objRecordset.EOF
    
    Computer = objRecordset.Fields("COMPUTER").Value
    'WScript.Echo Computer

Err.Clear
    '--> check computer to access
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & Computer & "\root\cimv2")
    If Err.Number <> 0 Then
        errMessage = Err.Description
        'WScript.Echo errMessage
        ERRINS = "INSERT INTO " & dbSchema & ".printlog_error VALUES(sysdate,'" & Computer & "','" & errMessage & "')"
        Set objRecordsetErr = con.Execute(ERRINS)
        Err.Clear
        errMessage = vbNullString
    Else    
        '--> check version OS Windows
        Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
        OS = vbNullString
        For Each objOperatingSystem in colOperatingSystems
            OS = objOperatingSystem.Caption
            Exit For
        Next
        'WScript.Echo OS
        
        '--> set logfile and evencode for Windows XP and 7
        If InStr(OS, "Windows XP") > 0 Or InStr(OS, "Windows(R) XP") > 0 Or
  InStr(OS, "Server 2003") > 0 Then
            LogFile = "System"
            EventCode = "10" 'event print for Windows XP (2003)
        ElseIf InStr(OS, "Windows 7") > 0 Or InStr(OS, "Windows(R) 7") Or InStr(OS, "Server 2008") > 0 Then
            LogFile = "Microsoft-Windows-PrintService/Operational"
            EventCode = "307" 'event print for Windows 7 (2008)
            
            '--> check exists registry key for read printing events on Windows 7
            Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & Computer & "\root\default:StdRegProv")
            strKeyPath = "SYSTEM\CurrentControlSet\services\eventlog"
            objRegistry.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys
            flag = 0
            For Each subkey In arrSubKeys
                If subkey = LogFile Then
                    flag = 1
                    Exit For
                End If
            Next
            
            If flag = 0 Then
                'WScript.Echo "Create new key Microsoft-Windows-PrintService/Operational."
                strKeyPath = "SYSTEM\CurrentControlSet\services\eventlog\Microsoft-Windows-PrintService/Operational"
                objRegistry.CreateKey HKEY_LOCAL_MACHINE, strKeyPath
            End If
        End If
    
        Rec = 0
        '--> check the log for events
        Set colLogFiles = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile = '" & LogFile & "' and EventCode = '" & EventCode & "'")
        For Each ObjLogFile In colLogFiles
            Rec = 1
            Exit For
        Next
    
        If Rec = 0 Then
            errMessage = "The " & LogFile & " are failed or not contained events " & EventCode
            'WScript.Echo errMessage
            ERRINS = "INSERT INTO " & dbSchema & ".PRINTLOG_ERROR VALUES(sysdate,'" & Computer & "','" & errMessage & "')"
            Set objRecordsetErr = con.Execute(ERRINS)
            errMessage = vbNullString
        Else 
        
        '--> option overwrite exists records in table (0 - OFF, 1 - ON)
        OverWrite = 0
        If OverWrite = 1 Then
            DEL = "DELETE FROM " & dbSchema & ".PRINTLOG where computer = '" & computer & "' and trunc(CREATEDTIME) >= to_date(substr('" & begdate & "', 1, 8), 'yyyymmdd') " _
                & " and trunc(CREATEDTIME) <= to_date(substr('" & enddate & "', 1, 8), 'yyyymmdd')"
            Set objRecordsetDel = con.Execute(DEL)
        End If

            '--> search events in a given period
            cnt = 0        
            Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile = '" & LogFile & "' and EventCode = '" & EventCode & "' and TimeGenerated >= '"& begdate &"' and TimeGenerated <= '" & enddate & "'")
            cnt = colLoggedEvents.Count
            If cnt > 0 Then
                'WScript.Echo cnt
                For Each objEvent In colLoggedEvents
                     strSearchString = objEvent.Message
                 
                     EventCode = objEvent.EventCode
                     SourceName = objEvent.SourceName
                     If InStr(objEvent.ComputerName, ".keramin.int") > 0 Then
                        ComputerName = LCase(objEvent.ComputerName)
                     Else
                        ComputerName = LCase(objEvent.ComputerName) & ".keramin.int"
                     End If
                     UserName = LCase(objEvent.User)
                     CreatedTime = objEvent.TimeWritten
                     Message = Replace(objEvent.Message, "'", "")
                         
                     INS = "INSERT INTO " & dbSchema & ".PRINTLOG VALUES(sysdate, to_date(substr('" & CreatedTime & "', 1, 14), 'yyyymmddhh24miss'),'" & OS & "','" & EventCode & "','"    & SourceName & "','" & ComputerName & "','" & UserName & "','" & Message & "')"
                     Set objRecordsetIns = con.Execute(INS)
                             
                Next

            Else
                errMessage = "There was no events print in " & LogFile & " from " & begdate & " to " & enddate
                'WScript.Echo errMessage
                'ERRINS = "INSERT INTO " & dbSchema & ".PRINTLOG_ERROR VALUES(sysdate,to_date(substr('" & begdate & "', 1, 8), 'yyyymmdd'),'" & Computer & "','" & errMessage & "')"
                'Set objRecordsetErr = con.Execute(ERRINS)
                errMessage = vbNullString
            End If
        End if
    End If
    
objRecordset.MoveNext
Loop

'--> close connect to Oracle
con.Close
Set objRecordsetErr = Nothing
Set objRecordsetIns = Nothing
Set objRecordsetDel = Nothing
Set con = Nothing
 >        End if
    End If
    
objRecordset.MoveNext
Loop

'--> close connect to Oracle
con.Close
Set objRecordsetErr = Nothing

А вот маленький запрос =), который возвращает нужные для статистики данные по таблице PRINTLOG:

SELECT SYSDATETIME,
         CREATEDTIME,
         EVENTID,
         SOURCENAME,
         COMPUTER,
         OS,
         USERNAME,
         SUBSTR (
            REGEXP_REPLACE (MESSAGE,
                            'Документ \d+, |Document \d+, ',
                            ''),
            1,
              REGEXP_INSTR (
                 REGEXP_REPLACE (MESSAGE,
                                 'Документ \d+, |Document \d+, ',
                                 ''),
                 'владельца|owned|, которым владеет')
            - 1)
            DOC,
         REGEXP_REPLACE (
            TRIM (
               REGEXP_REPLACE (
                  SUBSTR (
                     MESSAGE,
                     REGEXP_INSTR (
                        MESSAGE,
                        'владельца|owned by|, которым владеет')),
                  'владельца |owned by |, которым владеет')),
            ' (.*)')
            OWNER,
         SUBSTR (
            REGEXP_REPLACE (
               SUBSTR (
                  MESSAGE,
                  REGEXP_INSTR (
                     MESSAGE,
                     'напечатан на |был распечатан на |was printed ')),
               'напечатан на |был распечатан на |was printed on '),
            1,
              REGEXP_INSTR (
                 REGEXP_REPLACE (
                    SUBSTR (
                       MESSAGE,
                       REGEXP_INSTR (
                          MESSAGE,
                          'напечатан на |был распечатан на |was printed on ')),
                    'напечатан на |был распечатан на |was printed on '),
                 'через порт|via port|through port')
            - 2)
            PRINTER,
         SUBSTR (
            REGEXP_REPLACE (
               SUBSTR (
                  MESSAGE,
                  REGEXP_INSTR (MESSAGE,
                                'через порт |via port |through port ')),
               'через порт |via port |through port '),
            1,
              REGEXP_INSTR (
                 REGEXP_REPLACE (
                    SUBSTR (
                       MESSAGE,
                       REGEXP_INSTR (
                          MESSAGE,
                          'через порт |via port |through port ')),
                    'через порт |via port |through port '),
                 ' Размер: | Размер в байтах: | Size in bytes: ')
            - 3)
            PORT,
         SUBSTR (
            REGEXP_REPLACE (
               SUBSTR (
                  MESSAGE,
                  REGEXP_INSTR (
                     MESSAGE,
                     'Размер: |Размер в байтах: |Size in bytes: ')),
               'Размер: |Размер в байтах: |Size in bytes: '),
            1,
              REGEXP_INSTR (
                 REGEXP_REPLACE (
                    SUBSTR (
                       MESSAGE,
                       REGEXP_INSTR (
                          MESSAGE,
                          'Размер: | Размер в байтах: | Size in bytes: ')),
                    'Размер: | Размер в байтах: | Size in bytes: '),
                 ' байт;|число страниц: |(.|;) Pages printed: |(.|;) Страниц напечатано: ')
            - 1)
            BYTES,
         TRIM (
            REGEXP_REPLACE (
               SUBSTR (
                  MESSAGE,
                  REGEXP_INSTR (
                     MESSAGE,
                     ' байт; |число страниц: | Pages printed: | Страниц напечатано: ')),
                  ' байт; |число страниц: |Страниц напечатано: |(p|P)ages printed: |[.] Действий пользователя не требуется[.]|[.] No user action is required[.]|'
               || CHR (13)
               || CHR (10)))
            PAGES,
         MESSAGE
FROM printlog
ORDER BY 1;

Если будут какие-то вопросы обращайтесь, возможно помогу.
Также в скором времени создам пост с подобный скриптом, но написанном на Python.

Convert mail message from utf8 to windows-1251

Если вы с помощью PL\SQL делаете разбор почтовых сообщений (стандарта MIME) и получаете атрибуты (тема, тело сообщения, имена вложенных файлов и т.п.), Вы можете столкнуться с ситуацией, когда значение некоторых атрибутов дает подобный результат:

=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=

В моем случае я получал имя файла вложенного в письмо. Данное письмо я считывал из почтового ящика при помощи пакета MAIL_CLIENT.
Как оказалось когда имя файла во вложении было на латинице, то я его получал без проблем, но когда в имени файла содержались символы кириллицы, он кодировался в формат заголовка MIME с кодировкой UTF8.У Oracle для этих случаев есть пакет UTL_ENCODE, который позволяет кодировать и декодировать данные для почтовых сообщений.
В итоге написав следующую конструкцию, я решил проблему с кириллицей в имени файла:

file_name := case
when filename like '=?utf-8?%' then utl_encode.mimeheader_decode(filename)
else filename
end;

где, filename - это значение атрибута - имя вложенного файла в письме.
А условие case нужно для того, чтобы выполнять декодирование строк формата заголовка MIME в UTF8,
и не обрабатывать их, если имя содержит только латиницу.

В итоге я получил вместо:
=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=
нужное мне значение имени файла:
plam_как-то так_2.jpg

Для проверки:

select utl_encode.mimeheader_decode('=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=')
VAL from dual;

VAL                                                                             
----------------------
plam_как-то так_2.jpg  

Oracle regular expressions examples (REGEXP)

  • Oracle 10 and above

Примеры использования регулярных выражений.

REGEXP_REPLACE
1. Замена всех пробелов в строке на двойной пробел.

with t(fname) as
(select 'Hello, dear   friend! Nice to  see   you!' from dual)
select regexp_replace(fname, '[ ]+', '  ')  from t;</pre>
<pre>fname
--------------------------------------------------
Hello,  dear  friend!  Nice  to  see  you!

2. Замена первых n-символов в строке на другие.

with t(fname) as
(select 'Regular expressions is very easy' from dual)
select regexp_replace(fname, lpad(fname, 7), 'Arithmetic')  from t;

FNAME                              
-----------------------------------
Arithmetic expressions is very easy

3. Замена n-ого символа(ов) в строке, начиная с позиции k

with t(fname) as
(select 1000340082145076 from dual)
select regexp_replace(fname, '..', '21', 5, 1) fname  from t;

FNAME           
----------------
1000210082145076

4. Получить часть строки после первой запятой

with t(fname) as
(select 'gfdgh  ввап1; 567, 23425' from dual)
select regexp_replace(fname,  '^[^,]*,') fname  from t;

FNAME
------
23425

5. Удаляем из строки html tags

with t(fname) as
(select '<span style="color: rgb(255, 255, 153);">Hello World!</span><br style="color: rgb(255, 255, 153);"><span style="color: rgb(255, 255, 153);">Listen to your heart!</span><br>' from dual)
select regexp_replace(fname, '<[^>]*>', ' ') fname  from t;

FNAME
---------------------------------------
Hello World!   Listen to your heart!

6. Вырезать значение из двойных ковычек

with t(fname) as
(select 'LTD  "GROUP  "BIG WORLD" Las Vegas' from dual)
select replace(regexp_replace(fname, '^([^"]*"(.*)"[^"]*|(.*))$', '\2\3'), '"') fname  from t;

FNAME
----------------
GROUP  BIG WORLD

REGEXP_SUBSTR
1. Вырезать часть строки по заданному шаблону

with t(fname) as
(select '(KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'public'||chr(39)||', p_level=>5, p_app_id=>:APP_ID) or KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'ADMINISTRATORS'||chr(39)||', p_level=>10, p_app_id=>:APP_ID) or KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'users'||chr(39)||', p_level=>15, p_app_id=>:APP_ID))' fname from dual)
select regexp_substr(fname,'KERAMIN."user_role_auth"\(p_username=>:USER_NAME, p_groupname=>[^,]+, p_level=>10, p_app_id=>:APP_ID\)', 1, 1) fname  from t;

FNAME
--------------------------------------------------------------------------------
KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'ADMINISTRATORS',
p_level=>10, p_app_id=>:APP_ID)

2. Делим одну строку на несколько строк с использованием разделителя (например, <,>)

with t as
(select 'O,r,a,c,l,e' fname from dual)
select regexp_substr(fname,'[^,]+', 1, level) fname from t
connect by regexp_substr(fname, '[^,]+', 1, level) is not null;

FNAME
-----------
O
r
a
c
l
e

REGEXP_COUNT
1. Подсчет количества цифр в строке

with t(fname) as
(select '12 mgf45, fhfh-565gh hgh56 66' fname from dual UNION ALL
select 'fd8g78df7g8ghorhegy g rgtueriogti' fname from dual UNION ALL
select '5768hf hgh565 g-+g 676 454ggf55fhgfh' fname from dual)
select regexp_count(fname, '\d') fname from t;

FNAME
----------
11
5
15

2. Подсчет количества символов в строке без учета регистра

with t(fname) as
(select 'A fox is not taken twice in the same snare' fname from dual)
select regexp_count(fname, 'a', 1, 'i') fname from t;

FNAME
----------
4

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

Richard Foote's Oracle Blog

I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?
 
By no changes, it means that there have been no alterations to any segments, no new indexes have been added, no changes associated  bind peeking (indeed, there may not even be any bind variables), no parameters changes, no new patches or upgrades, no new outlines or profiles, no new system stats and perhaps most prevalent of all, no changes to any CBO statistics.
 
The DBA hasn’t touched a thing and yet suddenly, for no apparent reason, execution plans suddenly change and (say) an inappropriate index is suddenly used and causes performance degradation.
 
How can this be possible ?
 
There are…

View original post ещё 1 271 слово

Clustering Factor: A Consideration in Concatenated Index Leading Column Decision (Sweet Thing)

Richard Foote's Oracle Blog

Short but sweet today.

I last discussed how high cardinality columns shouldn’t necessarily be in the leading column of a concatenated index as  they don’t provide the performance benefit as sometimes claimed.

If all things are equal and the columns in the concatenated index are all likely to be referenced, a simple consideration that is often forgotten when deciding which column to have as the leading index column is the Clustering Factor of the corresponding columns.

As previously discussed, the Clustering Factor  determines how well aligned or ordered the index entries are in relation to the rows in the parent table. So if the rows are ordered within the table on a particular column or columns (such as a sequential ID column, a monotonically increasing date or time-stamp, etc), then an index on these columns is likely to have a very good Clustering Factor. Consequently less IOs will be required to retrieve all the required rows…

View original post ещё 275 слов

Important !! Clustering Factor Calculation Improvement (Fix You)

Richard Foote's Oracle Blog

Believe me, this article is worth reading :)

I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now be calculated. Many thanks to Martin Decker for pointing this out to me.

As anyone who has attended my Index Seminars will know, the CF of an index is one of the most important statistics used by the Cost Based Optimizer (CBO) in determining the most efficient execution plan. As such, it has always been an issue for me that the manner in which the CF is calculated has been so flawed.

Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the…

View original post ещё 1 108 слов

Reuse Of Empty Index Leaf Blocks (Free Four)

Richard Foote's Oracle Blog

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked:

«Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?»

I’ve previously discussed how Oracle can recycle index blocks that contain nothing but deleted index entries as such blocks are effectively added to the index freelist to be reused by subsequent index block splits. In my «Index Internals — Rebuilding The Truth» presentation, I mention a number of methods of how to see this reuse in operation, such as via block dumps, tree dumps and INDEX_STATS.

However, another simple method which I don’t think I’ve discussed here before is the use of the DBMS_SPACE package. So to help answer Stalin’s question, a simple…

View original post ещё 291 слово

ManageEngine ServieDesk : wrong DueBy Date for SLA

Если в созданной заявке неверно проставляется Срок исполнения (DueBy Date), нужно проверить параметры, от которых данный показатель:
1. Admin -> Operation Hours: правильно ли задано рабочее время.
2. Admin -> Service Catalog -> кнопка Manage: правильно ли задано значение времени в SLA.

Если в предыдущих пунктах все верно, то нужно обратить внимание на версию JRE time zone, под которой работает ServiceDesk.
Может быть она устарела и требует немедленного обновления!

1. В командной строке (CMD) получим версию JRE time zone и текущего файла tzupdater.jar (если файла tzupdater.jar нет, то переходим к п.2.):

c:\ManageEngine\ServiceDesk — это директория, куда установлен ServiceDesk

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -V -v

------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
 java.vendor: Sun Microsystems Inc.
 java.version: 1.6.0_45
 JRE time zone data version: tzdata2014e    //версия загруженная в java
 Embedded time zone data version: tzdata2014e    //версия файла tzupdater.jar
 Validating the time zone data
 Validation complete 

2. Теперь пройдем по этой ссылке http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html к списку всхем имеющихся версий tzupdater.jar.

Например, после нашей имеющейся версии tzdata2014e уже было выпущено несколько новых, где добавлены и изменены часовые зоны
(в одном из которых, Since Belarus is not changing its clocks even though Moscow is, the time zone abbreviation in Europe/Minsk is changing from FET to its more-traditional value MSK on 2014-10-26 at 01:00).

3. Здесь можно скачать последнюю версию tzupdater.jar: http://www.oracle.com/technetwork/java/javase/downloads/index.html

4. Скачанный файл нужно скопировать в директорию c:\ManageEngine\ServiceDesk\jre\bin\. Если там уже имеется файл, то переименуйте его, например, tzupdater.jar.old.

5. В командной строке получим разницу версий JRE time zone и скаченного файла tzupdater.jar:

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

-----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e    //текущая версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar недавно скачанного
Validating the time zone data
/data/tzdata2014i.test:4870: test failed: Asia/Kashgar
/data/tzdata2014i.test:4874: test failed: Asia/Urumqi
/data/tzdata2014i.test:5402: test failed: Pacific/Fiji
/data/tzdata2014i.test:5404: test failed: Pacific/Fiji
/data/tzdata2014i.test:5406: test failed: Pacific/Fiji
/data/tzdata2014i.test:5408: test failed: Pacific/Fiji
/data/tzdata2014i.test:5410: test failed: Pacific/Fiji
/data/tzdata2014i.test:5412: test failed: Pacific/Fiji
/data/tzdata2014i.test:5414: test failed: Pacific/Fiji
/data/tzdata2014i.test:5416: test failed: Pacific/Fiji
/data/tzdata2014i.test:5418: test failed: Pacific/Fiji
/data/tzdata2014i.test:5517: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5518: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5519: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5522: test failed: Asia/Yakutsk
/data/tzdata2014i.test:5525: test failed: Europe/Kaliningrad
/data/tzdata2014i.test:5528: test failed: Europe/Volgograd
/data/tzdata2014i.test:5531: test failed: Asia/Magadan
/data/tzdata2014i.test:5532: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5533: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5534: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5537: test failed: Asia/Khandyga
/data/tzdata2014i.test:5540: test failed: Asia/Yekaterinburg
/data/tzdata2014i.test:5543: test failed: Asia/Vladivostok
/data/tzdata2014i.test:5548: test failed: Europe/Simferopol
/data/tzdata2014i.test:5551: test failed: Asia/Ust-Nera
/data/tzdata2014i.test:5554: test failed: Asia/Omsk
/data/tzdata2014i.test:5557: test failed: Asia/Novosibirsk
/data/tzdata2014i.test:5560: test failed: Asia/Sakhalin
/data/tzdata2014i.test:5563: test failed: Asia/Krasnoyarsk
/data/tzdata2014i.test:5564: time zone not found: Asia/Chita
/data/tzdata2014i.test:5565: time zone not found: Asia/Chita
/data/tzdata2014i.test:5566: time zone not found: Asia/Chita
/data/tzdata2014i.test:5569: test failed: Asia/Irkutsk
/data/tzdata2014i.test:5572: test failed: Europe/Moscow
/data/tzdata2014i.test:5577: test failed: America/Grand_Turk
/data/tzdata2014i.test:5580: test failed: W-SU

Validation tests failed. 

6. Теперь обновим JRE Time zone:
CMD>

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -u -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e
Embedded time zone data version: tzdata2014i
Extracting files... done.
Renaming directories... done.
Validating the new time zone data... done.
Time zone data update is complete. 

7. Проверим версию JRE time zone:
CMD>

</pre>
CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014i        //версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar
Validating the time zone data
Validation complete 

Значения JRE time zone data version и Embedded time zone data version должны быть равны!

8. Перезапустим машину, на которой установлен сервер ManageEngine SevriceDesk, чтобы все измнения вступили в силу.

9. Если после выполнения п.1-п.8 все равно проблема осталась, то откройте файл c:\ManageEngine\Servicedesk\bin\startout.log и найдите в нем значение параметра user.timezone (временная зона). Если значение не равно временной зоне, что установлена в системе (например, если user.timezone = America/Caracas, а в системе установлено Europe/Minsk), значит Java запускается с какой-то своей временной зоной по умолчанию.

10. Чтобы исправить эту ситуацию, нужно остановить службу SD, открыть файл c:\ManageEngine\Servicedesk\server\default\conf\wrapper.conf на редактирование. Найти в нем раздел # Java Additional Parameters, где перечислены параметры запуска Java, и добавить туда новый параметр (-Duser.timezone):
В моем случае в этом разделе:

# Java Additional Parameters (added new parameter #24)
wrapper.java.additional.1=-server
wrapper.java.additional.2=-Dprogram.name=run.sh
wrapper.java.additional.3=-Djboss.server.type=com.adventnet.j2ee.deployment.system.AdventNetServerImpl
wrapper.java.additional.4=-Dorg.jboss.logging.Log4jService.catchSystemOut=false
wrapper.java.additional.5=-Dorg.jboss.logging.Log4jService.catchSystemErr=false
wrapper.java.additional.6=-Djava.util.logging.manager=com.adventnet.logging.LogManager
wrapper.java.additional.7=-Djava.util.logging.config.file=../server/default/conf/logging.xml
wrapper.java.additional.8=-Djava.util.logging.config.class=com.adventnet.logging.LoggingScanner
wrapper.java.additional.9=-Dlog.dir=../server/default
wrapper.java.additional.10=-Dtier-type=BE
wrapper.java.additional.11=-Dtier-id=BE1
wrapper.java.additional.12=-Ddb.home=../pgsql
wrapper.java.additional.13=-Dproduct.home=../
wrapper.java.additional.14=-Dfile.encoding=UTF-8
wrapper.java.additional.15=-Dsdp.java.gc=false
wrapper.java.additional.16=-Dserver.dir=../
wrapper.java.additional.17=-Duser.language=en
wrapper.java.additional.18=-Duser.region=US
wrapper.java.additional.19=-XX:PermSize=64m
wrapper.java.additional.20=-XX:MaxPermSize=256m
wrapper.java.additional.21=-Djboss.shutdown.forceHalt=false
wrapper.java.additional.100=-DlogDir=../server/default/log
wrapper.java.additional.22=-Dproduct.home=../
wrapper.java.additional.23=-Dorg.tanukisoftware.wrapper.WrapperManager.mbean=false
wrapper.java.additional.24=-Duser.timezone=Europe/Minsk # добавил нужную мне timezone

11. Запустить службу SD. Попробовать создать новую заявку, Срок исполнения должен ставиться верный, исходя из выбранного SLA.