Solved

VBS script to write to an Oracle DB

Posted on 2013-01-16
12
1,926 Views
Last Modified: 2013-02-09
Hello experts,

I'm trying to write a logon(off) script that will record some data to an orcale database.

This script is to be run on winXP and Win7.

Herse what i have so far;

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', " & Now()& ")" );"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit

any help would be appreciated
0
Comment
Question by:jackstark
  • 7
  • 2
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38786063
what is the problem/error?
I would replace the "now()" by using the oracle function sysdate, otherwise you get into date issues. ..

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', sysdate)" );"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
set thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit 

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787645
Are you getting any errors?

Are you using tnsnames.ora file?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787696
Another thing is, looks like you have an extra ) in your INSERT statement
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787702
and I am not sure you are handling date correctly. Try

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM-DD-YYY'));"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit 

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787717
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', " & Now()& "-->)<--" );"

extra one here
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 35

Accepted Solution

by:
YZlat earned 250 total points
ID: 38787743
sorry, missed one Y:

commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM-DD-YYYY'));"

Open in new window


or try

commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM/DD/YYYY'));"

Open in new window

0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 38871117
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38871118
I think the issue is pretty clear -  OP had an extra parenthesis
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38871126
and date issues can be corrected with to_date function
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38871143
I second that. Points for yzlat
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now