I ran into an error when I modified my script:
[Microsoft][ODBC SQL Server Driver][SQL Server][The name '#3' is not permitted
in this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
Code: 80040e14
my script follows:
***********************************
Set WSHNetwork = WScript.CreateObject("WScript.Network")
' Get Current Users Information
set shell = WScript.CreateObject( "WScript.Shell" )
username = shell.ExpandEnvironmentStrings("%USERNAME%")
'msgbox username
clientmachine = shell.ExpandEnvironmentStrings("%CLIENTNAME%")
'msgbox clientmachine
sessionname = shell.ExpandEnvironmentStrings("%SESSIONNAME%")
'msgbox sessionname
server = shell.ExpandEnvironmentStrings("%COMPUTERNAME%")
'msgbox server
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=dbsrv;" & _
"Database=log;"
Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
SQL = "insert into Transactions (DateStamp, UserName,
WorkStation,TransactionType)" & " values (#" & NOW() & "#,'"& username & "','"
& server & "','LogOn')"
objRS.Open SQL, objConn
Set objRS = Nothing
Set objConn = Nothing
********************************
The WSH error pointed to line 23 (objRS.Open SQL, objConn), could this refer to
a sysntax error in the objCon declaration? I may have fudged the syntax
although I ran the script with and without the Uid and Pwd variables with the
same results.
Any help would be greatly appreciated.
Thx,
Gideon Ashcraft
-----Original Message-----
From: Paul Wilkinson <[EMAIL PROTECTED]>
Sent: Mar 11, 2005 8:00 AM
To: [email protected]
Subject: Re: [ActiveDir] (l)user login auditing
'Code snip of doing a MS SQL query in vbscript
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=xxxxxx;" & _
"Database=xxxxx;" & _
"Uid=xxxxx;" & _
"Pwd=xxxxxxx"
Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
SQL = "your sql statement here"
objRS.Open SQL, objConn
Set objRS = Nothing
Set objConn = Nothing
Paul Wilkinson
865-974-0649
2422 Dunford Hall
OIT Lab Services
University of TN, Knoxville
Gideon Ashcraft wrote:
>This did the trick, I already ran it in my test group with the script piping
>into a backend database with a frontend setup for HR to run queries (I was
>getting errors on the logins if I had the database open). I would prefer to
>have it pipe into SQL though, but I'm new to vbs (I survived on batch
>scripting until now), what would be the calls to pass the data into a sql
>database as opposed to access? I could merge the data from the access
>database, but it would be much easier to pipe it straight into sql, and give
>hr a web interface.
>
>
>
>Many thx,
>
>
>
>Gideon Ashcraft
>
>
>
> _____
>
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Stelley, Douglas
>Sent: Wednesday, March 09, 2005 8:26 AM
>To: [email protected]
>Subject: RE: [ActiveDir] (l)user login auditing
>
>
>
>I had to do a similar thing (in a 1000 user environment) so I created an MDB
>file (userlog.mdb) with a table (Transactions) that has fields(DateStamp,
>UserName, WorkStation,TransactionType)
>
>Then I wrote the following 2 scripts: which are run at logon and logoff. the
>resulting mdb will give a nice history going forward of logon/off times and
>workstation access. I could give them more, but they didn't ask...
>
>Doug
>
>
>
>'{userlogon.vbs}
>
>
>
>Set WSHNetwork = WScript.CreateObject("WScript.Network")
>
>
>
>' Get Current Users Information
>set shell = WScript.CreateObject( "WScript.Shell" )
>username = shell.ExpandEnvironmentStrings("%USERNAME%")
>'msgbox username
>clientmachine = shell.ExpandEnvironmentStrings("%CLIENTNAME%")
>'msgbox clientmachine
>sessionname = shell.ExpandEnvironmentStrings("%SESSIONNAME%")
>'msgbox sessionname
>server = shell.ExpandEnvironmentStrings("%COMPUTERNAME%")
>'msgbox server
>
>
>
>dim SQL, rs
>set conn=CreateObject("ADODB.Connection")
> conn.Provider="Microsoft.Jet.OLEDB.4.0"
> conn.Open "\\ntapps\pccommon\userlog\userLog.mdb
><file:///\\ntapps\pccommon\userlog\userLog.mdb> "
> set rs = createobject("ADODB.Recordset")
> SQL = "insert into Transactions (DateStamp, UserName,
>WorkStation,TransactionType)" & " values (#" & NOW() & "#,'"& username &
>"','" & server & "','LogOn')"
> rs.Open SQL, conn
> 'rs.Close
> conn.Close
>
>
>
>'{userlogoff.vbs}
>
>Set WSHNetwork = WScript.CreateObject("WScript.Network")
>
>
>
>' Get Current Users Information
>set shell = WScript.CreateObject( "WScript.Shell" )
>username = shell.ExpandEnvironmentStrings("%USERNAME%")
>'msgbox username
>clientmachine = shell.ExpandEnvironmentStrings("%CLIENTNAME%")
>'msgbox clientmachine
>sessionname = shell.ExpandEnvironmentStrings("%SESSIONNAME%")
>'msgbox sessionname
>server = shell.ExpandEnvironmentStrings("%COMPUTERNAME%")
>'msgbox server
>
>
>
>dim SQL, rs
>set conn=CreateObject("ADODB.Connection")
> conn.Provider="Microsoft.Jet.OLEDB.4.0"
> conn.Open "\\ntapps\pccommon\userlog\userLog.mdb
><file:///\\ntapps\pccommon\userlog\userLog.mdb> "
> set rs = createobject("ADODB.Recordset")
> SQL = "insert into Transactions (DateStamp, UserName,
>WorkStation,TransactionType)" & " values (#" & NOW() & "#,'"& username &
>"','" & server & "','LogOff')"
> rs.Open SQL, conn
> 'rs.Close
> conn.Close
>
>
>
>
>
>
>
> _____
>
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Jason B
>Sent: Wednesday, March 09, 2005 11:08 AM
>To: [email protected]
>Subject: Re: [ActiveDir] (l)user login auditing
>
>This is where Ed should chime in with his famous line - something to the
>extent of it being a bad idea to try to address/fix behavioral problems
>using technology.
>
>
>
>I wish I had an answer for you, but would be curious to hear what others say
>about the situation.
>
>----- Original Message -----
>
>From: Gideon <mailto:[EMAIL PROTECTED]> Ashcraft
>
>To: [email protected]
>
>Sent: Wednesday, March 09, 2005 8:28 AM
>
>Subject: [ActiveDir] (l)user login auditing
>
>
>
>Some fool mentioned to our HR department that we can track our employee's
>work routines by auditing the login events to our DC's instead of their
>supervisors actually doing work and tracking the work habits of their
>charges. So now I need to present reports to our illustrious HR department
>in terms they can understand (pretty pictures and colors with all the
>details washed out so they can grasp the picture). I started by enabling
>login successes in the default DC policy and was overwhelmed by a flood of
>events from login attempts and the constant flood of logins (20,000 security
>events/day) from our LANutil inventory (don't ever use PC-Duo) software
>(originally setup wrong by helpdesk staff and currently locking the accounts
>of anyone associated with that deployment (I'm letting them suffer for the
>moment because they did it without asking for Domain Admin support).
>
>
>
>Currently I am using a 60 day trial of GFI's SELM log monitor to archive
>events (until my UNIX admin has the time to learn enough PROLOG to get
>Tivoli to mine our logs, or I learn how to use the free MS Log Parser to
>mine our DC's) and I did a test login and logout on a test user account (all
>events associated with that user were cleaned prior to testing) and I found
>that logging in created 28 mixed login and logout events (including 538,
>540, 673 events) on login but only 1 540 logON event during logOFF and 2 538
>logoff events 12 and 41 minutes after logging out!!!
>
>
>
>What I would really like to do is tell HR to &[EMAIL PROTECTED] Themselves and
>tell the
>supervisors to do a better job tracking their employees and spend my
>valuable time tracking events for critical System and application events
>instead of babysitting the incompetents. But unfortunately the powers that
>be wish to appease the HR beast rather than put it in its place, so I have
>to clean up the flood of login events into a form that they can understand.
>
>
>
>Does anyone recommend any software suited to this purpose or can does anyone
>know of a simple query of events to pinpoint domain activity?
>
>
>
>Gideon Ashcraft
>
>Network Administrator
>
>Screen Actors Guild
>
>
>
>Confidentiality Notice: The information contained in this message may be
>legally privileged and confidential information intended only for the use of
>the individual or entity named above. If the reader of this message is not
>the intended recipient, or the employee or agent responsible to deliver it
>to the intended recipient, you are hereby notified that any release,
>dissemination, distribution, or copying of this communication is strictly
>prohibited. If you have received this communication in error please notify
>the author immediately by replying to this message and deleting the original
>message. Thank you.
>
>
>
>
List info : http://www.activedir.org/List.aspx
List FAQ : http://www.activedir.org/ListFAQ.aspx
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
List info : http://www.activedir.org/List.aspx
List FAQ : http://www.activedir.org/ListFAQ.aspx
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
