Dates are one of the few things that don't move nicely from Access to
real SQL.
I always format dates as (eg) '15 Mar 2005' - this copes whether your
software is set for proper dates(!), American dates or any other
format.
I have a simple function to cope with this:
Function showdate(d)
d=day(d) & " " & monthname(month(d),true) & " " & year(d)
End function
And then your SQL becomes:
SQL = "insert into Transactions (DateStamp, UserName,
WorkStation,TransactionType)" & " values ('" & showdate(NOW()) & "','" &
username & "','" & server & "','LogOn')"
For things like this when what you want to insert is "now" then SQL
makes life much easier:
SQL = "insert into Transactions (DateStamp, UserName,
WorkStation,TransactionType)" & " values (getdate(),'" &
username & "','" & server & "','LogOn')"
- the getdate function just returns the current date/time and
automatically puts it in a SQL friendly form.
Steve
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Gideon Ashcraft
> Sent: 11 March 2005 20:13
> To: [email protected]
> Subject: Re: [ActiveDir] (l)user login auditing
>
> 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/
>
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/
