Tracking User Logons in a Domain

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Tracking User Logons in a Domain

Logged in as: Guest

arrSession:exec spGetSession 2,16,32133

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> Post a VBScript >> Tracking User Logons in a Domain
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 next > >>

Message

<< Older Topic Newer Topic >>

Tracking User Logons in a Domain - 3/9/2006 10:54:15 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Krillian wrote and posted a great script in his thread http://www.visualbasicscript.com/m_30261/tm.htm. But at my Evil Headquarters (tm) we do it a bit different.

This code logs user sessions to a SQL Database and has an ASP based web page to display that data. Each post of mine in this thread will contain code for a different portion of the system. I have two ugly red-headed stepchildren in my scripting family. The first is a robocopy wrapper (perhaps I'll post it later) and the second is this web page. :) Be kind.

Logon_Activity_V2_Readme.txt

Tracking a user sessions relies upon several components: 
 * A script that runs from a Group Policy Object
 * A SQL database that accepts information from the script
 * An ASP web page that displays the information from the database

The purpose of each file:
 * logonactivityV4.wsf -> script file that runs for each user
                         at logon and in the background
 * Logon_Activity_V2_Table.sql -> Contains SQL commands to generate
                         a table for you in a database of your choosing
 * Logon_Activity_V2_Stored_Procedures.sql -> Stored Procedures used
                         by loginactivityV4.wsf and by the SQL Server
                         Agent Job service
* Logon_Activity_v2_SQL_Server_Agent_Job.sql -> Contains a cleanup
                         job that runs periodically to keep the database tidy
 * LoginSessions2005 Folder -> The web site that reads the database
   + adovbs.inc ->       A file containing a bunch of constants used
                         by the web site
   + config.txt ->       The configuration file for the web site
   + default.asp ->      The only page in the web site
   + global.asa ->       Reads the config file, builds connections to
                         the SQL Server
 * IIS Install Folder ->  Contains gif files that displays IIS's 
                         configuration

To get started, I suggest you work in this order: 
* Find a Microsoft SQL Server to use
  + Using SQL Enterprise Manager create a database 
    called 'Visual_Basic_Scripting'
  + Open SQL Query Analyzer 
  + Once you are inside SQL Query Analyzer, attach
    to the database 'Visual_Basic_Scripting'
  + Run these scripts inside the Query Analyzer
    in this order
    - Logon_Activity_V2_Table.sql
    - Logon_Activity_V2_Stored_Procedures.sql
    - Logon_Activity_V2_SQL_Server_Agent_Job.sql
 * Open loginactivityV4.wsf
  + Go to line 59 -> strDB_Server = "<Server Name Here>"
  + Insert the name of your database server between
    the double quotes
 * Open LogonSessions2005\config.txt
  + Goto line 6 -> server: <Database Server Name Here>
  + Replace <Database Server Name Here> with the name 
    of your database server.  Don't use double quotes.
 * Install the web site on your web server
  + Place the folder LogonSessions2005 into your Inetpub folder (IIS)
    - If you want to keep people out of the web site, lock down this
      folder using NTFS Security Permissions
  + Look at the .gif files in the IIs Install directory and mimic
    those settings shown into your own IIS server
 * Create a DNS entry for "ls2005" and "logonsessions2005" pointing
  back to your IIS web server
 * Run loginactivityV4.wsf from your desktop with the command line 
  argument of "LOGON".  
  + Open a command prompt window
  + Execute this statement:
    - c:\winnt\system32\wscript.exe "<path>\loginactivityV4.wsf" LOGON
  + Execute this statement:
    - c:\winnt\system32\wscript.exe "<path>\loginactivityV4.wsf" LOGOFF
  + Open Task Manager and kill the wscript process that is still running
    - An explanation why you need to do this only during testing:  
      When a user logs on, their Domain tells them to run the script with
      the command line argument "LOGON".  This process tags the database
      then goes to sleep for 5 minutes.  After the 5 minutes, it tags the
      database again and goes to sleep once more.  This process repeats
      forever.  When a log off event happens, I couldn't find a way to 
      have the process started at logon also signal the database to log 
      off. Therefore, I decided to run a second process that would tag the DB
      at logoff.  If you're curious how I keep all this straight, read the
      stored procedure 'UserLogonV3'.  The LOGOFF process stops itself.
      The LOGON process is killed by Windows during the closing of the
      session.  It may sound sloppy, but it works.
 * Visit the web site [link=http://ls2005]http://ls2005[/link]
  + If your session shows up in the web site, great!  If not, try the
    following steps:
    - Inspect the Database 'Visual_Basic_Scripting' on your database server
      for entries in the table 'LogonActivityV2'.  If you see entries, the
      problem is probably in the web site (or the installation thereof)
    - If you don't see any entries in the database, then enable debugging
      in loginactivityV4.wsf by setting boolDebug = True.  Then rerun
      loginactivitV4.wsf via the command line as described earlier.  You 
      might want to attach a debugger if you're still having problems.  An 
      OK (read: free) debugger ships with Microsoft Office.  It's called
      MSE7.EXE.  Search C:\Program Files for it.  With it you can attach
      to the wscript.exe process and "break" the execution to debug the
      code.  Keep in mind that if you attach to the process and nothing 
      happens, the code might be asleep for 5 minutes.  Just wait for more
      than 5 minutes for the code to come out of sleep.
 * Assuming everything has gone well, it is time to install the script
  in a Group Policy Object.  Start with an Active Directory GPO that 
  only executes for you.  If database entries are succesfully made, you 
  can remove it from that GPO and install it on another that will run for
  more members of your domain.

NOTE:  During the development of this code, I found I would sometimes type
     "login".  Other times I would type "logon".  While the code does work,
     be aware of this grammatical glitch when you're debugging the code
     and moving around files.  :)

Some of you might have wondered what the SQL Agent Job is for.  When
     a user's session is terminated without being able to run the logoff
     script, an incomplete entry will hang around in the database.  The job
     runs every so often and looks for entries whose heartbeat timestamps
     are older than an hour and a half (i think).  It then populates
     those entries with a logoff time of five minutes past the last
     heartbeat.

The log file can at times grow large, you might want to develop
     another SQL Server Agent Job that trims old log entries.  That is 
     entierly up to you.

< Message edited by turranx -- 3/9/2006 11:02:44 PM >

Revisions: 1 | Post #: 1

RE: Tracking User Logons in a Domain - 3/9/2006 10:55:41 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Logon_Activity_V2_Table.sql

_____________________________

Microsoft Windows 2000 Scripting Guide - The best book for newbie scripters
http://www.myspace.com/Evil__Overlord

(in reply to turranx)

Post #: 2

RE: Tracking User Logons in a Domain - 3/9/2006 10:59:18 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Logon_Activity_V2_Stored_Procedures.sql

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.LogOff_Cleanup    Script Date: 3/9/2006 12:40:55 PM ******/
CREATE PROCEDURE [dbo].[LogOff_Cleanup] AS
Begin    /* Find records where the heartbeat hasn't been updated in 90 min. Close that record */
       UPDATE    LogonActivityV2
       SET        dtmlogoff = dateadd(mi,5,dtmheartbeat)
       WHERE    (dtmheartbeat < dateadd(mi,-90,getdate())) and
               ((dtmlogoff is NULL) or (dtmlogoff = CONVERT(DATETIME, '1800-01-01 00:00:00', 102))) and
               (dtmheartbeat is not NULL)
End
Begin    /* Find and close records where no activity has been reported in 90 min */
       UPDATE    LogonActivityV2
       SET        dtmlogoff = dtmlogon, 
               dtmheartbeat = dtmlogon
       WHERE    (dtmlogoff is NULL) and
               (dtmheartbeat is NULL) and
               (dtmlogon < dateadd(mi,-90,getdate()))
End
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.UserLogonV3    Script Date: 3/9/2006 12:40:55 PM ******/
CREATE PROCEDURE [dbo].[UserLogonV3]
   @timestamp datetime,
   @computer char(30),
   @userid char(30),
   @userdomain char(30),
   @activity char(30),
   @sessionID int

AS
   /* Possible States we have to deal with */
/*
   @Activity        @Record_Count    State OK?    Course of Action
   ----------------------------------------------------------------------------------------------------------------------------------------------
   LOGON            0                Good        Create new record
   LOGON            >0             BAD        Close old record(s) by updating logoff time with heartbeat time
                                            Create new record

LOGOFF            0            BAD        Create new record
                                            Set logon, logoff, and heartbeat times equal to logoff time
   LOGOFF            1            Good        Update logoff time
   LOGOFF            >1            BAD        Close all old records by filling in dtmlogoff with @timestamp

HEARTBEAT        0            BAD        Create new record using @timestamp as logon time
                                             Update heartbeat time
   HEARTBEAT        1            Good        Update record with new heartbeat
   HEARTBEAT        >1            BAD        Update all records with latest heartbeat via @timestamp
                                              Close all records by populating dtmlogoff via @timestamp
                                              Open one new record using @timestamp as dtmlogon
*/

Declare        @Record_Count    INT
   /*Look for records that might indicate an open session*/
   SET        @Record_Count =    (SELECT    COUNT(id)
                       FROM    dbo.LogonActivityV2
                       WHERE    (userid = @userid) and
                               (computer = @computer) and
                               (SessionID = @sessionID) and 
                               (dtmlogoff is NULL))
   
/*------------------------------------------------------------------*/

/*Create a record in the table LogonActivityV2 if one doesn't exist*/
   If (@activity = 'LOGON') and (@Record_Count = 0)
   BEGIN
       INSERT INTO LogonActivityV2
           (dtmlogon,computer,userid,userdomain,sessionID)
       VALUES
           (@timestamp,@computer,@userid,@userdomain,@sessionID)
   END

If (@activity = 'LOGON') and (@Record_Count > 0)
   BEGIN
       BEGIN
           UPDATE    LogonActivityV2
           SET        dtmlogoff = dtmheartbeat
           WHERE    (userid = @userid) and
                   (computer = @computer) and
                   (SessionID = @sessionID) and 
                   (dtmlogoff is NULL)
       END
       BEGIN
           INSERT INTO LogonActivityV2
               (dtmlogon,computer,userid,userdomain,sessionID)
           VALUES
               (@timestamp,@computer,@userid,@userdomain,@sessionID)
       END
   END

/*-----------------------------------------------------------------------------*/

If (@activity = 'HEARTBEAT') and (@Record_Count = 0)
   BEGIN
       BEGIN    /* Insert a new record with a heartbeat included */
           INSERT INTO LogonActivityV2
               (dtmlogon,dtmheartbeat,computer,userid,userdomain,sessionID)
           VALUES
               (@timestamp,@timestamp,@computer,@userid,@userdomain,@sessionID)
       END
   END

/*Update a record in the table LogonActivityV2 only if it exists already*/
   If (@activity = 'HEARTBEAT') and (@Record_Count = 1)
   BEGIN
       UPDATE    LogonActivityV2
       SET        dtmheartbeat = @timestamp
       WHERE    (userid = @userid) and
               (computer = @computer) and
               (SessionID = @sessionID) and 
               (dtmlogoff is NULL)
   END

If (@activity = 'HEARTBEAT') and (@Record_Count > 1)
   BEGIN
       BEGIN    /* Update the heartbeat of all records and close them simultaneously*/
           UPDATE    LogonActivityV2
           SET        dtmheartbeat = @timestamp,
                   dtmlogoff = @timestamp
           WHERE    (userid = @userid) and
                   (computer = @computer) and
                   (SessionID = @sessionID) and 
                   (dtmlogoff is NULL)
       END
       BEGIN    /* Create a single new record with heartbeat already embedded */
           INSERT INTO LogonActivityV2
               (dtmlogon,dtmheartbeat,computer,userid,userdomain,sessionID)
           VALUES
               (@timestamp,@timestamp,@computer,@userid,@userdomain,@sessionID)
       END
   END

/*----------------------------------------------------------------------------------*/

If (@activity = 'LOGOFF') and (@Record_Count = 0)
   BEGIN
       BEGIN    /* Insert a new record where logon, heartbeat, and logoff are identical */
           INSERT INTO LogonActivityV2
               (dtmlogon,dtmheartbeat,dtmlogoff,computer,userid,userdomain,sessionID)
           VALUES
               (@timestamp,@timestamp,@timestamp,@computer,@userid,@userdomain,@sessionID)
       END
   END

/*If a single record exists, close it*/
   If (@activity = 'LOGOFF') and (@Record_Count = 1)
   BEGIN
       UPDATE    LogonActivityV2
       SET        dtmlogoff = @timestamp
       WHERE    (userid = @userid) and
               (computer = @computer) and
               (SessionID = @sessionID) and 
               (dtmlogoff is NULL)
   END

If (@activity = 'LOGOFF') and (@Record_Count > 1)
   BEGIN
       UPDATE    LogonActivityV2
       SET        dtmlogoff = @timestamp
       WHERE    (userid = @userid) and
               (computer = @computer) and
               (SessionID = @sessionID) and 
               (dtmlogoff is NULL)
   END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[UserLogonV3]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[UserLogonV3]  TO [Domain Users]
GO

(in reply to turranx)

Post #: 3

RE: Tracking User Logons in a Domain - 3/9/2006 11:01:01 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Logon_Activity_V2_SQL_Server_Agent_Job.sql

-- Script generated on 3/9/2006 12:43 PM
-- By: [email=G.Forums@oraucoc.org]G.Forums@oraucoc.org[/email]
-- Server: DB01

BEGIN TRANSACTION            
 DECLARE @JobID BINARY(16)  
 DECLARE @ReturnCode INT    
 SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1 
 EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Logon Activity V2 Cleanup') > 0 
 PRINT N'The job "Logon Activity V2 Cleanup" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job
 EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Logon Activity V2 Cleanup', @owner_login_name = N'sa', @description = N'Locates all sessions that haven''t received a heartbeat in the past half hour and sets the log off time to the last heartbeat + 30 minutes.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Run Cleanup', @command = N'LogOff_Cleanup', @database_name = N'Visual_Basic_Scripting', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Logon Activity V2 Cleanup Job', @enabled = 1, @freq_type = 4, @active_start_date = 20050512, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:

< Message edited by turranx -- 3/9/2006 11:33:22 PM >

(in reply to turranx)

Revisions: 1 | Post #: 4

RE: Tracking User Logons in a Domain - 3/9/2006 11:06:11 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

LogonActivityV4.wsf

<?xml version="1.0" ?>
<package>
   <job id="LogonActivityV4" args="logoff" prompt="no">
       <?job error="false" debug="false" ?>
       <script id="Primary Job" language="VBScript">
<![CDATA[
Option Explicit
'*********************************************************************************
'Description:   This is a logon/logoff script that runs for every user in our
'               domain.  It writes their session information to a SQL Database.
'*********************************************************************************
'Revisions:
'(Mar 09th 2006) - v1.1.3 - Added code to populate strActivity with "LOGON" in case
'                       a command line argument wasn't provided. Also added an
'                       updated version of GetSessionID called 'GetUserSessionIDEx'
'                       Also documented the TagDBServer function.
'(Jan 31st 2006) - v1.1.2 - Added code to explicitly clean up instantiated objects
'                       when function GetSessionID is used
'(Mar 30th 2005) - v1.1.1 - It has come to my attention that when a user
'                       disconnects from TS, their session ID remains the same, but
'                       the Env Var 'SESSIONNAME' changes.  Therefore, I am
'                       implementing a change to pull a number for appending to
'                       SessionGUID_#### not from 'SESSIONNAME' Env Var, but from
'                       the session id which is retreived by parsing the output
'                       from qwinsta.
'(Mar 30th 2005) - v1.1 - Exploring better methods of determining if it is a logon
'                       or logoff.  The Domain's group policy editor allows me to
'                       specify command line arguments.  So I have redone GetGUID
'                       to use this as an indicator.
'(Mar 29th 2005) - v1.0 - Put into extended testing -> Logging to DB03
'(Mar 27th 2005) - v0.1 - Writing Script
'*********************************************************************************
'Written For: All versions of x86 Windows 2000 and up.  Server & Desktop.
'*********************************************************************************
'NOTE: * This code should be installed in a Group Policy Object.  Preferably at
'        the root of your Active Directory tree. The GPO might be titled 'Default
'        Domain Policy'.  Once you've found a good place to install it, navigate
'        to 'User Configuration' -> 'Windows Settings' -> 'Scripts (Logon/Logoff)'.
'        Now insert the script into the Logon and Logoff sections with the
'        parameters "logon" or "logoff".  Don't use double quotes.
'*********************************************************************************
'Create Variants
Dim strDB_Server                        'Name of the Database Server
Dim strDB_Name                          'Name of the Database on the DB Server
Dim strDB_Query                         'Query String
Dim boolDebug                           'Is it in Debug Mode
Dim dtmTimeStamp                        'Timestamp of this activity
Dim strComputerName                     'This computer's name
Dim strUserDomain                       'The Domain of this user logging on
Dim strUserID                           'The user ID of this user logging on
Dim intTSSessionID                       'A GUID to represent this session
Dim strActivity                         'May be either "Log on" Or "Log off"
'*********************************************************************************
'Populate Variants
strDB_Server = "<Server Name Here>"
strDB_Name = "Visual_Basic_Scripting"
boolDebug = False
'*********************************************************************************
'On Error Resume Next

'*********************************************************************************
'Begin Script
GatherInformation
If boolDebug = True Then
   WScript.Echo    "Time: " & dtmTimeStamp & vbCRLF & _
               "Computer: " & strComputerName & vbcrlf & _
                     "UserID: " & strUserID & vbcrlf & _
                     "Domain: " & strUserDomain & vbcrlf & _
                     "Activity: " & strActivity & VbCrLf & _
                     "SessionID: " & intTSSessionID 
End If
TagDBServer
'If the command line argument isn't "LOGON" then its
'probably "LOGOFF".  When Logging off, don't go into a loop
If UCase(strActivity) = UCase("LOGON") Then
       strActivity = UCase("heartbeat")
       Do While 1 = 1
               dtmTimeStamp = Now
               TagDBServer
               WScript.Sleep 300000    'Sleep for 5 minutes
                   'Then retag the database
       Loop
End If

WScript.Quit
'Fin

'*********************************************************************************
'Subroutines

'============================
'GatherInformation
'============================
Sub GatherInformation
       Dim objShell
       Dim colSystemEnvVars    'System Environment Variables Collection
       Dim objNetwork          'Network Information
   Dim arrSessions()    'A dynamic array
       Set objShell = WScript.CreateObject("WScript.shell")
       Set colSystemEnvVars = objShell.Environment("System")
       Set objNetwork = Wscript.CreateObject("WScript.Network")
       
       dtmTimeStamp      = Now
       strComputerName   = objNetwork.ComputerName
       strUserID         = objNetwork.UserName
       strUserDomain     = objNetwork.UserDomain

'         If strUserID = "<userID>" Then
'           boolDebug=True
'           WScript.Echo "Debugging..."
'         End If

intTSSessionID    = GetUserSessionIDEx(strUserID,arrSessions)
   If Wscript.Arguments.Count > 0 Then
           strActivity = UCase(WScript.Arguments.Item(0))
   Else
       strActivity = "LOGON"
   End If

Set objShell = Nothing
       Set colSystemEnvVars = Nothing
       Set objNetwork = Nothing
End Sub

'============================
'GetUserSessionIDEx
'============================
Function GetUserSessionIDEx(ByVal strUserID, ByRef arrOutputSessionList)
       'Created: February 20th, 2006
       'Revisions:
       '(Mar 9th 2006) - Return Value wasn't actually returning the proper
       '                 SessionID if strUserID was who was running the script
       'NOTE: This Function grew out of GetUserSessionID which came from
       '      GetSessionID.
       '      This code only gathers sessions on the local computer.
       '      If multiple sessions for one userID are found, 
       '      the last session ID will be returned.
       'INPUT: strUserID -> A String "jdoe" or "flastname"
       '       arrOutputSessionList -> A variable or 'NOTHING' if you
       '                               don't plan on checking the array 
       '                               for multiple sessions
       'Output: Return Value -> -1 If user not logged In
       '                         0 If user is logged In local
       '                        >0 If user is logged in though a Remote Desktop Session
       '        arrOutputSessionList -> An array containing every session ID
       '                                For the userID In strUserID
       Dim boolSessionIDFound          'A flag indicating If a user session was found
       Dim boolNoSessionFound          'A flag indicating no user session was found
       Dim strReadline                 'Used to hold strings from Environment Variables
                                       ' And occasionally qwinsta.exe output
       Dim strSessionID                'A temporary placeholder for the Return Value
       Dim objShell                    'Shell object.  Used for running programs and
                                       ' extracting system information
       Dim colVolatileEnvVars          'A collection of Volatile Environment Variables
       Dim objExec                     'Used To run qwinsta.exe
       Dim strTemp                     'Used To hold and process output from qwinsta.exe
       Dim strRunMe                    'Used To hold the path and command line arguments
                                       ' run qwinsta.exe
       dim i                           '
       Dim intLoopCount                'A counter used to keep track of how many times
                                       ' qwinsta.exe was run.  Sometimes people close
                                       ' the qwinsta.exe command window which keeps the
                                       ' script from obtaining a value for the session
                                       ' information.  This value counts up to 10 before
                                       ' giving up.
       Dim objNetwork                  'Used to obtain information about who is logged 
                                       ' into the local console
       Dim boolDebug                   'Set this To true to see wscript.echo output from
                                       ' the subroutine as it runs
       ReDim arrOutputSessionList(0)   '
   Dim strMySession
       
       Set objNetwork = Wscript.CreateObject("WScript.Network")
       boolDebug = False
       intLoopCount = 0
       strReadline = ""
       Set objShell = WScript.CreateObject("WScript.shell")
       Set colVolatileEnvVars = objShell.Environment("Volatile")        
       If boolDebug = True then Wscript.Echo "Getting Session Information"
       'Is this a Console session or a RDP session?
       'The environment variable SESSIONNAME exists only on Terminal Servers
       strReadline=colVolatileEnvVars("SESSIONNAME")
       If strReadline <> "" Then
               boolSessionIDFound = False
               Do Until boolSessionIDFound Or (intLoopCount > 10) Or (boolNoSessionFound = True)
                       If boolDebug = True Then WScript.Echo "RDP Session - Executing qwinsta"
                       strRunMe = "qwinsta """ & strUserID & """"
                       Set objExec = objShell.Exec(strRunMe)
                       strReadline = objExec.StdOut.readline()
                       'The first line is either "No session exists for <username>" or
                       ' " SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE"
                       ' as long as no additional command line parameters are used.
                       If objExec.StdOut.AtEndOfStream = True Then
                               boolNoSessionFound = True
                               Set objExec = Nothing
                       Else
                               Do Until objExec.StdOut.AtEndOfStream
                                       strTemp = objExec.StdOut.ReadLine()
                                       If boolDebug = True Then wscript.Echo "Qwinsta: " & strTemp
                                       If ( instr(strTemp,strUserID) > 0) Then
                                           If boolSessionIDFound = True Then
                                               'This ID was found once already.
                                               'Increase the array size to handle another entry
                                                   ReDim Preserve arrOutputSessionList(UBound(arrOutputSessionList)+1)
                                           End If
                                           'Identify my session
                                           If ( instr(strTemp,">") > 0) Then strMySession =

Split(LTrim(Mid(strTemp,instr(strTemp,strUserID)+Len(strUserID)))," ")(0)
                                               'Add this session ID to the array of sessions for my UserID
                                               arrOutputSessionList(UBound(arrOutputSessionList)) =

Split(LTrim(Mid(strTemp,instr(strTemp,strUserID)+Len(strUserID)))," ")(0)
                                               boolSessionIDFound = True
                                               If boolDebug = True Then WScript.Echo "Qwinsta Found the correct line: " &

arrOutputSessionList(UBound(arrOutputSessionList))
                                       End If
                               Loop
                       End If
                       intLoopCount = intLoopCount + 1
               Loop
               If (boolSessionIDFound = False) Or (boolNoSessionFound = True) Then
                       'We have a problem...  The session ID was not found for the user in strUserID
                       'How to handle?
                       'Use a completely fake session ID of -1
                       If boolDebug = True Then WScript.Echo "Qwinsta has not found the correct line.  User not logged in."
                       strSessionID = -1
               Else
                       'We have isolated the proper Line(s).  
                       'Now Extract the Session ID                        
                       If strUserID = objNetwork.UserName Then
                               'strUserID is the user who is running this script
                               strSessionID = strMySession
                       Else
                               'Someone else other than strUserID is running the script
                               'So just return the last session ID for that UserID found
                               strSessionID = arrOutputSessionList(UBound(arrOutputSessionList))
                       End If
                       If boolDebug = True Then WScript.Echo "RDP SessionID: " & strSessionID
               End If
               If (err <> 0) AND (boolDebug = True) Then Wscript.Echo err.description
          Else
               'This code is being run on a workstation.
               'Only console sessions exist on workstations.
               If boolDebug = True Then WScript.Echo "Console Logon"
               If strUserID = objNetwork.UserName Then
                'The local colsole session is the userID specified in strUserID
                strSessionID = 0
               Else
                'The local console session is not the userID specifed in strUserID
                strSessionID = -1
               End If
       End If 
       GetUserSessionIDEx = strSessionID
       Set objExec = Nothing
       Set objShell = Nothing
       Set objNetwork = Nothing
       Set colVolatileEnvVars = Nothing
End Function

'============================
'TagDBServer
'============================
Sub TagDBServer
       'Created: March 25th, 2005
       'Revisions:
       '(Mar 9th 2006) - Documented Function.  Also converted some
   '                 Variants to Constants. 
       'Description: The sole purpose of this function is to upload
   '             information collected in global variables to
   '             a Stored Procedure located on a Database Server.
   '          The Stored Procedure is named 'UserLogonV3'.
   'NOTE: The user who runs this code needs execute permissions
   '      to the Stored Procedure 'UserLogonV3' and WRITE 
   '      permissions to the database/table where 'UserLogonV3'
   '      specifies
       'INPUT: Global Variables -> dtmTimeStamp 
   '                           strComputerName 
   '                           strUserID 
   '                           strUserDomain 
   '                           strActivity 
   '                           intTSSessionID 
       'Output: Return Value -> None

On Error Resume Next        'Set Resume Next in case there is 
                   ' a problem talking to the DB server
       Dim strConnection        'Connection String
       Dim objConnection        'Connection Object
       Dim objRecordSet        'Recordset Object
       Dim strQuery            'SQL String to Execute

Const adUseClient = 3        
       Const adOpenStatic  = 3
       Const adLockOptimistic = 3
       
   'Populate objects
       Set objConnection = CreateObject("ADODB.Connection")
       Set objRecordSet = CreateObject("ADODB.Recordset")
       
   'Build Connection & Query String
       strConnection = "Provider=SQLOLEDB;Data Source=" & strDB_Server & ";Initial Catalog=" & strDB_Name & ";Trusted_Connection=yes;Network

Library=dbmssocn"
       strQuery = "EXEC UserLogonV3 '" & dtmTimeStamp & "','" & strComputerName & "','" & strUserID & "','" & strUserDomain & _
                  "','" & strActivity & "','" & intTSSessionID & "'"
       If boolDebug = True Then wscript.Echo "TagDBServer - Query: " & strQuery

'Connect to DB Server
       objConnection.Open strConnection
       objRecordSet.CursorLocation = adUseClient
   
   'Execute Query
       objRecordSet.Open strQuery, objConnection, adOpenStatic, adLockOptimistic
       If boolDebug = True Then WScript.Echo "TagDBServer - Error: " & err.Number & "  -  " & err.Description

'Tear down objects
       objConnection.Close
       Set objConnection = Nothing
       Set objRecordSet = Nothing
On Error Goto 0            'Go back to standard Error handling
End Sub
]]>
       </script>
   </job>
</package>

(in reply to turranx)

Post #: 5

RE: Tracking User Logons in a Domain - 3/9/2006 11:09:19 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Web Page - LogonSessions2005
You will also need "adovbs.inc". Search your hard drive for it.

config.txt

title:        Logon Sessions 2005
description:    Answering the tough questions of Who, Where, and For How Long...

table: LogonActivityV2
database: Visual_Basic_Scripting
server: <Database Server Name Here>

'array to populate: <HTML friendly>| <SQL friendly>

andor content: And| and
andor content: Or| or

AscDesc content: Descending| DESC
AscDesc content: Ascending| ASC

'custom query:   Select a Query|
'custom query:   Open Workstation Sessions| SELECT Top 1000 * FROM LoginActivityV2 WHERE (sessionID = 0) and (dtmlogoff is NULL)
'custom query:   Open Terminal Server Sessions| SELECT Top 1000 * From LoginActivityV2 WHERE (dtmlogoff <> 0) and (dtmlogoff is NULL) and (computer like <terminal server name>%)
'custom query:   Today's Activity| SELECT Top 1000 * FROM LoginActivityV2 WHERE (dtmlogon > CONVERT(character,getdate(),101)) 
'custom query:   Yesterday's Activity| SELECT Top 1000 * FROM LoginActivityV2 WHERE (dtmlogon < CONVERT(character,getdate(),101)) and (dtmlogon > CONVERT(character,getdate()-1,101))

'NOTE: For these custom queries to work, you should replace "<terminal server name>" with the name of your terminal server
'NOTE: You should also replace "<server name prefix>" with the prefix your servers use for a naming convention

custom query:   Select a Query|
custom query:   Open Workstation Sessions|Top?1000/FILTERME?dtmlogoff/Relation?is NULL/AndOr?and/FILTERME?sessionID/Relation?=/Exactly?0/AndOr?and/FilterMe?computer/Relation?not like/Text?<server name prefix>
custom query:   Open Terminal Server Sessions|Top?1000/FILTERME?SessionID/Relation?<>/Text?0/AndOr?and/FILTERME?dtmlogoff/Relation?is NULL/AndOr?and/FilterMe?computer/Relation?like/Text?<terminal server name>
custom query:   Today's Activity|Top?1000/FILTERME?dtmheartbeat/Relation?>/Text?CONVERT(character,getdate(),101)
custom query:   Yesterday's Activity|Top?1000/FILTERME?dtmlogon/Relation?</Text?CONVERT(character,getdate(),101)/AndOr?and/FilterMe?dtmlogon/Relation?>/Text?CONVERT(character,getdate()-1,101)
custom query:   Today's Improperly Named Workstation Sessions|Top?1000/FilterMe?computer/Relation?not like/Text?<server name prefix>/AndOr?and/FilterMe?computer/Relation?not like/Text?w/AndOr?and/FILTERME?dtmlogon/Relation?>/Text?CONVERT(character,getdate(),101)

< Message edited by turranx -- 3/9/2006 11:39:05 PM >

(in reply to turranx)

Revisions: 2 | Post #: 6

RE: Tracking User Logons in a Domain - 3/9/2006 11:10:23 PM

turranx

Posts: 38
Score: 0
Joined: 2/7/2006
Status: offline

Web Page - LogonSessions2005

global.asp

'For more help With <object>, see http://www.w3schools.com/asp/asp_globalasa.asp
<object runat="Server" language="vbscript" scope="Application" id="objConnection" progid="ADODB.Connection"></object>
'<object runat="Server" language="vbscript" scope="Application" id="objRecordSet"  progid="ADODB.RecordSet"></object>
#include file="adovbs.inc"
<script language="vbscript" runat="server">

'============================
'Application_OnStart         
'============================
'This subroutine will create a link to the database
'it will stay open Until the last session is closed
'See Page 310 of Beginning Active Server Pages 3.0
'by WROX -> ISBN Number: 0-7645-4363-6
Sub Application_OnStart
       Const ForReading = 1
       Const ForWriting = 2
       Const ForAppending = 8
       Dim strConnection
       Dim objConfigFile
       Dim strReadLine
       Dim i
       
       Dim objFSO
   Set objFSO = createObject("Scripting.FileSystemObject")
   Set objConfigFile = objFSO.OpenTextFile(Server.MapPath(".") & "\config.txt", ForReading)
   Application("Filter_Count") = -1
   Application("AndOr_Count") = -1
   Application("PreBuilt_Query_Count") = -1
   Application("Relation_Count") = -1
   Application("AscDesc_Count") = -1
   Do Until objConfigFile.AtEndOfStream
           strReadLine = objConfigFile.ReadLine
           If Len(Trim(strReadLine)) > 0 Then
                   temp = Split(strReadLine,":")
                   Select Case Trim(temp(0))
                           Case "title"
                                   Application("Title") = Trim(Split(strReadLine,":")(1))
                           Case "description"
                                   Application("Description") = Trim(Split(strReadLine,":")(1))
                           Case "filter content"
                                   Application("Filter_Count") = Application("Filter_Count") + 1
                                   Application("Filter_SQL_" & CStr(Application("Filter_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(1))
                                   Application("Filter_HTML_" & CStr(Application("Filter_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(0))
                           Case "AscDesc content"
                                   Application("AscDesc_Count") = Application("AscDesc_Count") + 1
                                   Application("AscDesc_SQL_" & CStr(Application("AscDesc_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(1))
                                   Application("AscDesc_HTML_" & CStr(Application("AscDesc_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(0))
                           Case "andor content"
                                   Application("AndOr_Count") = Application("AndOr_Count") + 1
                                   Application("AndOr_SQL_" & CStr(Application("AndOr_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(1))
                                   Application("AndOr_HTML_" & CStr(Application("AndOr_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(0))
                           Case "relation content"
                                   Application("Relation_Count") = Application("Relation_Count") + 1
                                   Application("Relation_SQL_" & CStr(Application("Relation_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(1))
                                   Application("Relation_HTML_" & CStr(Application("Relation_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(0))
                           Case "custom query"
                                   Application("PreBuilt_Query_Count") = Application("PreBuilt_Query_Count") + 1
                                   Application("PreBuilt_Query_SQL_" & CStr(Application("PreBuilt_Query_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(1))
                                   Application("PreBuilt_Query_HTML_" & CStr(Application("PreBuilt_Query_Count"))) = Trim(Split(Trim(Split(strReadLine,":")(1)),"|")(0))
                           Case "table"
                               Application("Table") = Trim(temp(1))
                           Case "server"
                                   Application("Server") = Trim(temp(1))
                           Case "database"
                                   Application("Database") = Trim(temp(1))
                           Case Else
                   End Select
           End If
   Loop
   objConfigFile.close
   set objConfigFile = Nothing
   set objFSO = Nothing
   
   strConnection = "Provider=SQLOLEDB;Data Source=" & Application("Server") & _
               ";Initial Catalog=" & Application("database") & ";Trusted_Connection=yes;Integrated Security=SSPI;Network Library=dbmssocn"
       On Error Resume Next
       objConnection.Close
       Set objConnection = Nothing
       On Error Goto 0
       objConnection.Open strConnection
End Sub

'============================
'Application_OnEnd           
'============================
Sub Application_OnEnd
       On Error Resume Next
       objConnection.Close
       Set objConnection = Nothing
End Sub

'============================
'Session_OnStart             
'============================
Sub Session_OnStart
End Sub

'============================
'Session_OnEnd               
'============================
Sub Session_OnEnd
End Sub
</script>

(in reply to turranx)

Post #: 7