Document 204399
Orsenna – Oracle - How to execute SQL file
with Oracle Monitor, save the result in a file
and analyze it with WUG.
Date
Version
Reference
Author
25/10/2011
1.0
001
Antoine CRUE
YOUR T ECHNICAL CONT ACT S
J EAN-PHILIPPE SENCKEISEN
ANT OINE CRUE
PHONE : +33 1 34 93 35 33
PHONE : +33 1 34 93 35 33
EMAIL : JPSENCKEISEN@ORSENNA. FR
EMAIL
: ACRUE@ORSENNA. FR
This document contains confidential informations which are property of ORSENNA. It cannot be diffused or transferred outside of
your organization without written authorization from ORSENNA. It cannot be copied or reproduced in any way. ORSENNA can
modify, without previous notice, any condition included in the present offer, depending of the evolution of ORSENNA services
(services,devices, programs, documents,prices).The information included in this document can therefore be modified. ORSENNA is
a trademark. This proposition is only available with its technical Visa.
FAQ – Oracle / SQL File
Orsenna
Summary
2 |
1
OBJECTIVE..........................................................................................................................................................3
2
HOW TO US E.......................................................................................................................................................4
3
SCRIPT ..................................................................................................................................................................9
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
1
Objective
This FAQ show you the process to check values in your environment with Oracle Monitor. The goal
of this tutorial is to implement an Oracle monitor which is able to execute SQL file with Oracle
Monitor, save the result in a file and analyze it with WUG.
For this tutorial, we will analyze and compare the number of rows.
3 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
2
How to use
Now you could execute SQL file when you configure custom SQL Query :
4 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
Click on « Use advanced options » :
SQL file : select SQL file which will be execute by WhatsUp Gold.
Result : select txt file (this file will store the content of the SQL query result).
Script file : select script file which will analyze your result file and send the return to WhatsUp Gold.
For our example :
Figure 1- SQL Query file (.SQL)
Figure 2 - Result file (before process)
5 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
Figure 3 - Script file
Now I will configure my Active Monitor, if my result file contains more than 10 rows, Active Monitor
will be “Down”:
6 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
I test my new Active Monitor:
7 |
Réf. 001
29/10/2011
Orsenna
FAQ – Oracle / SQL File
When I click on test, WhatsUp Gold execute SQL file query, store the result in the result file :
Script analyze it and send the result to WhatsUp Gold. WhatsUp Gold compare it with the threshold
defined by the user :
8 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
3
Script
Dim sSQLResultFile, sReturnFile, strData, arrLines, LineCount
' Initialize the program
Set args = WScript.Arguments
' The first argument is path to the SQL result file.
sSQLResultFile = args (0)
' The second argument is path to a file that the script must write result to.
sReturnFile
= args (1)
' Read content of the SQL result
sInput = ReadAllFile (sSQLResultFile)
' An output string. This string will be written to the "sReturnFile" file.
' This can be a returned result or error details.
strTextFile = sSQLResultFile
sOutput = AnalyzeFileAll(strTextFile)
bSuccess = true
' Add your processing here.
' This sample simply return a simple text
' Write the result to output file. First line must be "ERROR" or "OK"
If bSuccess = false Then
' In case of error during the processing, we notify the active monitor by writting ERROR
string at the first line
' The details can come after
WriteFileAll sReturnFile, "ERROR" & vbNewline & sOutput
Else
' If no error occurred, we write "OK" at the first line.
' The returned result can come after the first line
WriteFileAll sReturnFile, "OK" & vbNewline & sOutput
End If
' Helper function for reading all content of file
' path: path to the file that will be read
Function ReadAllFile (path)
9 |
Réf. 001
29/10/2011
FAQ – Oracle / SQL File
Orsenna
Const ForReading = 1, ForWriting = 2, ForAppending = 8
' The following line contains constants for the OpenTextFile
' format argument, which is not used in the code below.
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(path, ForReading)
ReadAllFile = f.ReadAll ()
f.Close ()
End Function
' Helper function for writting a string to file.
' str: a string that will be written to the file.
' path: a file that will be written to.
Function WriteFileAll(path, str)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(path, ForWriting, false)
f.Write (str)
f.Close ()
End Function
Function AnalyzeFileAll(strTextFile)
CONST ForReading = 1
Dim objFSO, strData, arrLines, LineCount
Set objFSO = CreateObject("Scripting.FileSystemObject")
strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll
arrLines = Split(strData,vbCrLf)
LineCount = UBound(arrLines) + 1
AnalyzeFileAll = LineCount
' Context.LogMessage "Valeur=" & LineCount
End Function
10 |
Réf. 001
29/10/2011
© Copyright 2024