檢查備份是否完成/自動發信機制是否完成

資訊部每天都需要做人工檢核的作業, 有時真的相當的花費時間, 趁著有個一天的空檔, 開始著手來改善這個現象, 讓每日的檢核作業能盡量的減少人員話費的時間.

目標: (1) 每日檢查備份是否完成 (2) 公司內部的 Auto Mail 是否寄送成功.

以上兩者有部分相通, 都可以由檢核 Log 檔案內的關鍵字來判斷作業是否成功, 或是檢查備份出來的檔案是否存在, 檔案最少需要多大才算成功 等條件, 由以上的假設, 開始在網路上東拼西湊加入一點自己的邏輯判斷, 把以上的工作彙整成一個 VBScript.

建立資料庫 Table

首先在資料庫內建立兩個 Table 分別存放 Backup 記錄及 Auto Mail 的紀錄,

建立 Backup 記錄
drop table “OAUSER”.”BAK_DAILY_RECORD”;
CREATE TABLE “OAUSER”.”BAK_DAILY_RECORD” (“BAK_DATE” DATE NOT NULL,
“BAK_SYSTEM” VARCHAR2(100) NOT NULL,
“BAK_STATUS” VARCHAR2(20) NOT NULL, “BAK_DESCRIPTION”
VARCHAR2(200), “BAK_SERVER” VARCHAR2(25) NOT NULL,
“BAK_CONTACT” VARCHAR2(25), “BAK_FEQUENCY” VARCHAR2(20),
“BAK_SIZE” NUMBER(15) default 0
) TABLESPACE “OAUSER_TBS”;

建立 Auto Mail 記錄
drop table “OAUSER”.”AML_DAILY_RECORD”;
CREATE TABLE “OAUSER”.”AML_DAILY_RECORD” (“AML_DATE” DATE NOT NULL,
“AML_SYSTEM” VARCHAR2(100) NOT NULL,
“AML_STATUS” VARCHAR2(20) NOT NULL, “AML_DESCRIPTION”
VARCHAR2(200), “AML_SERVER” VARCHAR2(25) NOT NULL,
“AML_CONTACT” VARCHAR2(25), “AML_FEQUENCY”
VARCHAR2(20)) TABLESPACE “OAUSER_TBS”;

以上兩個表格建立好之後, 記得針對查詢的去建立 Index 喔… 因剛剛開始使用, 所以查詢條件還需要等資料量多一點之後再來判斷補上 Index.

建立 VBScript

‘ 定義 Oracle 連線變數
Dim connection, connectionString, theCommand, commandString

‘ 定義抓取電腦名稱變數
Dim wshShell, cmpname

‘ 定義傳入 Vbscript 參數
Dim arg1, arg2, arg3, arg4, arg5, arg6, arg7, i, bRun

‘Command type – 1 is for standard query
const cnstCommand = 1

‘ 假設參數判斷錯誤, 程式不執行
bRun = false

‘ 設定為 STRING
If Wscript.Arguments.Count = 7 then

if wscript.arguments(0) = Ucase(“STRING”) Then
arg1 = UCASE(wscript.arguments(0)) ‘ STRING
arg2 = wscript.arguments(1) ‘ Compare Log File Name
arg3 = wscript.arguments(2) ‘ Description
arg4 = wscript.arguments(3) ‘ Contact
arg5 = wscript.arguments(4) ‘ Fequency
arg6 = wscript.arguments(5) ‘ Application Name
arg7 = wscript.arguments(6) ‘ Compare String / File Size
bRun = true
end if

if wscript.arguments(0) = Ucase(“FILE”) Then
arg1 = UCASE(wscript.arguments(0)) ‘ FILE
arg2 = wscript.arguments(1) ‘ FileName
arg3 = wscript.arguments(2) ‘ Description
arg4 = wscript.arguments(3) ‘ Contact
arg5 = wscript.arguments(4) ‘ Fequency
arg6 = wscript.arguments(5) ‘ Application Name
arg7 = wscript.arguments(6) ‘ Compare String
bRun = true
end if

end if

‘ 參數有問題, 稍微說明一下參數的用法
if bRun = false then
wscript.echo “7 arguments are needed or bad arguments”
wscript.echo ” Method 1 – Find the string in side of the file. ”
wscript.echo “arg1 = STRING, Compare String in assigned file”
wscript.echo “arg2 = File Name, File Name and its path”
wscript.echo “arg3 = Description, Descript this operation”
wscript.echo “arg4 = Contact, Whom is called when failed”
wscript.echo “arg5 = Fequency, How Often this operation was executed”
wscript.echo “arg6 = Application Name, What is the name of operation”
wscript.echo “arg7 = Compare String, The string this script compare from”
wscript.echo “”
wscript.echo ” Method 2 – Find the existing of file and compare its size. Filesize only bigger is considered sucessful. ”
wscript.echo “arg1 = FILE, Compare String in assigned file”
wscript.echo “arg2 = File Name, File Name and its path”
wscript.echo “arg3 = Description, Descript this operation”
wscript.echo “arg4 = Contact, Whom is called when failed”
wscript.echo “arg5 = Fequency, How Often this operation was executed”
wscript.echo “arg6 = Application Name, What is the name of operation”
wscript.echo “arg7 = sizefile, The size of file, no smaller than”
‘ wscript.quit
End If

‘ 抓取本機電腦名稱用
Set wshShell = WScript.CreateObject(“WScript.Shell” )
cmpname = wshShell.ExpandEnvironmentStrings(“%COMPUTERNAME%”)
‘msgbox cmpname

‘ 設定連線字串
connectionString = “DRIVER={Microsoft ODBC for Oracle};SERVER=DBTEST-TP;User Id=oauser;Password=useroa;”
Set connection = CreateObject(“ADODB.Connection”)
Set theCommand = CreateObject(“ADODB.Command”)
connection.Open connectionString

‘ 參數檢查 OK, 同時要比較的檔案存在
if bRun = true and FileExist(arg2) then

if arg1 = “STRING” then
‘ 呼叫檔案比對 Function, 檔案內有找尋的字串就傳回 TRUE
if comparetext (arg2, arg7) then
commandString = “INSERT INTO AML_DAILY_RECORD (AML_DATE, AML_SYSTEM, AML_STATUS, AML_SERVER, AML_DESCRIPTION, AML_CONTACT, AML_FEQUENCY) VALUES (sysdate, ‘” + arg6 + “‘, ‘OK’, ‘” + cmpname + “‘, ‘” + arg3 + “‘, ‘” + arg4 + “‘, ‘” + arg5 + “‘)”
else
commandString = “INSERT INTO AML_DAILY_RECORD (AML_DATE, AML_SYSTEM, AML_STATUS, AML_SERVER, AML_DESCRIPTION, AML_CONTACT, AML_FEQUENCY) VALUES (sysdate, ‘” + arg6 + “‘, ‘CompareFail’, ‘” + cmpname + “‘, ‘” + arg3 + “‘, ‘” + arg4 + “‘, ‘” + arg5 + “‘)”
end if
end if

‘ 檢查目的檔案大小
if arg1 = “FILE” then
‘ 如果目的檔案存在且大於參數 arg7 的值
if FileExistSize(arg2) then
commandString = “INSERT INTO AML_DAILY_RECORD (AML_DATE, AML_SYSTEM, AML_STATUS, AML_SERVER, AML_DESCRIPTION, AML_CONTACT, AML_FEQUENCY) VALUES (sysdate, ‘” + arg6 + “‘, ‘OK’, ‘” + cmpname + “‘, ‘” + arg3 + “‘, ‘” + arg4 + “‘, ‘” + arg5 + “‘)”
else
commandString = “INSERT INTO AML_DAILY_RECORD (AML_DATE, AML_SYSTEM, AML_STATUS, AML_SERVER, AML_DESCRIPTION, AML_CONTACT, AML_FEQUENCY) VALUES (sysdate, ‘” + arg6 + “‘, ‘SizeSmaller’, ‘” + cmpname + “‘, ‘” + arg3 + “‘, ‘” + arg4 + “‘, ‘” + arg5 + “‘)”
end if
end if
else
‘ 所指定的檔案不存在, 在 Database 內記錄錯誤訊息
commandString = “INSERT INTO AML_DAILY_RECORD (AML_DATE, AML_SYSTEM, AML_STATUS, AML_SERVER, AML_DESCRIPTION, AML_CONTACT, AML_FEQUENCY) VALUES (sysdate, ‘” + arg6 + “‘, ‘FileNotExist’, ‘” + cmpname + “‘, ‘” + arg3 + “‘, ‘” + arg4 + “‘, ‘” + arg5 + “‘)”
end if

‘ 執行 SQL 指令
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

‘ function 傳入檔案名稱以及找尋的 KEY Word, 如果檔案內找到該字串就傳回 TRUE, 找不到則回傳 FALSE
Function Comparetext(filename, keyword)

Const ForReading = 1, ForWriting = 2
Dim fso, f, readalltextfile, pos

Set fso = CreateObject(“Scripting.FileSystemObject”)
Set f = fso.OpenTextFile(filename,ForReading)
readalltextfile = f.readall
pos = InStr(readalltextfile, keyword)
‘msgbox pos
if pos > 0 then comparetext = true else comparetext = false

End function

‘ 檢查檔案是否存在且檔案大小超過 Arg7 的數字
Function FileExistSize(filename)

DIM objFSO
dim objFile

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

If objFSO.FileExists(filename) Then
set objFile = objFSO.GetFile (filename)
if int(arg7) > objFile.Size then FileExist = true else FileExist = False
Else
fileExist = false
End If

End Function

‘ 檢查檔案是否存在
Function FileExist(filename)
DIM objFSO

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

If objFSO.FileExists(filename) Then
FileExist = true
Else
fileExist = false
End If
End Function

有需要的人可以把以上的程式碼擷取出來使用.

在用法上需配合 OS 的 cscript 使用, LogCheck.vbs 是我自已取的名稱, 後面接的都是參數
cscript LogCheck.vbs “STRING” “D:REPORTSLOGERROR.LOG” “Description” “Contact Name” “EveryDay” “ReportName” “Success String”

排程到系統內, 緊接著備份或 Auto Mail 完成之後, 該 Script 就會依照參數來做必要的檢核, 並把結果寫回到資料庫內.

發表迴響