'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/
