| Downloads |
|---|
| 37725.zip |
Logon and logoff reports are one of the most common management-requested types of auditing reports. Management can use these reports for many types of tasks—from verifying time sheet entries to detecting unusual network activity. The generation of logon and logoff reports for a given user seems fairly straightforward: Collect your security audit logs for the time interval for which you want a report, then filter the logs for the entries that correspond to the user of interest. However, creating a robust script to perform this operation isn't such a simple task, as you'll discover.
In the first article of this two-part series, "Event-Log Auditing, Part 1," February 2003, http://winscriptingsolutions.com, InstantDoc ID 27574, I showed you a script, LogDump.cmd, that dumps the Security event logs from Windows 2000 or Windows NT servers into comma-separated value (.csv) text files that the script then combines into .zip archives for more efficient storage. The script I create in this article, Logonlogoff.cmd, which Web Listing 1 (http://www.winscriptingsolutions.com, InstantDoc ID 37725) shows, uses the log dumps stored in these .zip archives to generate logon and logoff reports for a specified user over a specified time period.
LogDump.cmd
In "Event-Log Auditing, Part 1," I showed you the LogDump.cmd script, which uses PsLogList.exe to create the log dumps and Info-ZIP's Zip to compress the archives. The servers from which you dump the logs store these archives under C:\logs\seclog\servername_SECLOG_%zipdate%.zip, where %zipdate% is the month and year (mm_yyyy) that the .zip file was created. I use Info-ZIP's UnZip in Logonlogoff.cmd to decompress the .zip archives so that I can process the dumped log files. The latest version, as of this writing, is UnZip 5.5, which you can obtain at http://www.info-zip.org/pub/infozip/unzip.html. For more information about UnZip, go to http://www.info-zip.org/unzip.html#win32.
Requirements
Before you write any code, you need to consider the processing requirements and restrictions. First, you want to process only .zip archives that LogDump.cmd created. You also need to make sure that LogDump.cmd is running against the domain controllers (DCs) on a daily basis to ensure that the naming convention used in the .zip archive and the log files it contains is correct. The script must be able to handle log dumps from both Win2K and NT servers because the event descriptions for the two OSs differ significantly and will affect how the script parses the files for output. The script must let you specify a start date and end date for the time interval for which you want to generate the report and must be smart enough to handle dates from different months and even years. Finally, the script must generate a report for only one username at a time.
Detecting Leap Years
Because you want to be able to process date ranges that span multiple months (and possibly years), the script needs to know the calendar year (or years) of the date range for which you're generating the logon and logoff reports. Because certain months have 30 days and other months have 31 days, you can easily insert code that determines how many days are in the specified starting and ending months. But if the date range includes February, the script needs to know whether the February date or dates are in a leap year before it can determine whether February has 28 or 29 days. Leap years occur every 4 years, except for years that are divisible by 100, unless the year is also divisible by 400. To test for divisibility, you need to obtain the remainder from a division operation—known as performing the modulus operation—then determine whether the remainder is equal to 0. Unfortunately, neither Win2K nor NT shell scripting has a built-in modulus operator. To work around this shortcoming, you can use the fact that performing a division operation results in a whole number with the remainder lost, such that 5 / 2 = 2, not 2.5. With this in mind, you can use the following pseudocode to determine whether a number (i.e., the year) is divisible by 4.
1. temp = number / 4
2. result = temp ¥ 4
3. if result = number, then the number is exactly divisible by 4; otherwise the number isn't divisible by 4
For example, in Step 1, if number is 17, then temp is 4. In Step 2, result is 16. In Step 3, 16 doesn't equal 17, so 17 isn't divisible by 4. In a shell script, you can use the Set command with the /a switch to evaluate an expression as a numerical expression. The code at callout E in Web Listing 1 checks for divisibility by 4, 400, and 100 to test for the leap year condition.
Logonlogoff.cmd
Logonlogoff.cmd begins by using the command
Set serverlist=<server1> <server2> <server3>
to specify the names of the servers that store the logs. The script then creates a folder called logonlogoff in the %temp% directory in which the log processing will take place. The script defines the path to this directory in the logonlogofftemp variable so that it can reference the directory later.
Next, the code at callout A sets some variables equal to the values the user supplied on the call to the script, including the start date and end date (in mm/dd/yyyy format) for the time interval for which you want to generate the report; the userid, which is the username for the computer for which you want the report and which will also be the name of Logonlogoff.cmd's output file; and the logtype, which can be either Win2K (default) or NT. Depending on the logtype designation, the script runs slightly different scripting logic.
To determine the current year, the script uses the output of the date /t command; for Win2K servers, you simply replace date /t with echo %date%. If you use the forward slash (/) character as the date delimiter, the current year is the third token of the output. The script stores this value in the curr_year variable using the following code:
For /f "tokens=3 delims=/" %%i
in ('date /t') do set
curr_year=%%i
An important step is to validate the start and end dates to protect against invalid dates and date ranges (e.g., an end date predating the start date). To perform a validation check, the script extracts the day, month, and year from the user-entered date and stores this data in variables that you can use later for comparisons. The simplest way to extract and store this data is to use a For loop with the forward slash character and the backslash (\) character as delimiters to tokenize the date, as the code at callout B shows.
Separating the data lets you easily determine whether the user input the date in the format mm/dd/yyyy. For example, if the user entered the date as mm-dd-yyyy, the script treats the string as one token rather than three, so the other two tokens (including start_year) will be undefined. If start_year doesn't have an assigned value, the script displays the proper syntax for the script and ends. The advantage of splitting the date into parts (i.e., day, month, and year) is that you can perform simple comparisons against portions of the date. For example, if the start year is greater than the current year, the date is obviously invalid. Likewise, if the end year is greater than the current year, processing the request doesn't make sense.
Whenever you ask a user to enter a day or month in numeric format, you run the risk that the user will enter a leading 0 in the month or day (e.g., 01/15/1995). The leading 0 can cause problems in date comparisons because in Win2K and NT shell scripting, 01 doesn't necessarily equal 1 (especially in an NT command prompt). Logonlogoff.cmd uses the following code to check for leading 0s in the start day and removes them.
If %start_day:~0,1% EQU 0 set start_day=%start_day:~1,1%
The construction %start_day:~0,1% returns one character of start_day beginning with offset 0 (the first character). If the character is equal to 0, it's obviously a leading 0 and the code strips out the 0 by setting start_day to itself starting with offset 1 (~1).
Next, logonlogoff.cmd performs a set of validation rules—once for the start date and a second time for the end date. First, the script uses a variable to determine whether the start year is a leap year. The script first clears the variable's value, then calls the code at callout E, which checks to see whether the start year is a leap year. If it is, Logonlogoff.cmd sets the isStartLeapYear variable to TRUE.
To determine the start month, the script first assumes that the month has 31 days, then uses a series of If statements to determine whether the start month is April, June, September, or November. If so, the script sets the no_days variable to 30. If the start month is February, Logonlogoff.cmd assumes 28 days, but if isStartLeapYear has a value of TRUE, the code sets no_days to 29. Finally, the script verifies that the start day value isn't greater than the number of days in the start month. For example, if the user wants to create an audit report beginning on 4/31/2002, Logonlogoff.cmd will determine that this date is invalid because April has only 30 days. The script repeats this process for the end date (with different variable names, of course).
After the script has verified that the start and end dates are properly formatted dates that make sense, it needs to verify their validity relative to each other. If the end year of the specified time interval is later than the start year, the dates are valid. If the end year is earlier than the start year, the script displays the syntax to the user and ends, as the following code shows.
If %end_year% GTR %start_year% goto :proceed If %end_year% LSS %start_year% goto :syntax
If neither of the above cases is true, the start and end years must be the same. The script then performs the same comparison it made with the start and end years for the start and end months. Finally, the script repeats the same logic for the start and end days. This procedure might seem like a great deal of time spent on reality checks, but the extra effort pays off. Generating logon and logoff reports can consume large amounts of time; you don't want to discover after the fact that an invalid date or date range caused the script to run astray or produce output that isn't what you want.
Generating Reports
The next step is to generate the reports. Logonlogoff.cmd first copies and extracts the .zip files that contain the Security logs from the start month and year through the end month and year, as the code at callout C shows. Remember, the Logdump.cmd script I showed you in Part 1 stores the Security event log dumps in .zip archives grouped by month and year with the naming convention %server%_SECLOG_%zipdate%.zip, where %server% is the name of the server that the log is from and %zipdate% is a concatenation of the month and year that the archive was created in mm_yyyy format.
Logonlogoff.cmd stores the month and year to process, starting with the start month and year that the user specified, in the processmonth and processyear variables. For each processmonth and processyear combination and for each server name defined in serverlist, the script performs the following steps:
1. The script determines whether the .zip archive exists and, if not, goes to the next .zip archive.
2. The script copies the .zip archive to %logonlogofftemp%, which is defined as %temp%\logonlogoff.
3. The script uses UnZip to decompress the archive to %logonlogofftemp%.
Logonlogoff.cmd then verifies that the stop variable is set to TRUE. This variable is set to TRUE when the processmonth and processyear variables are equal to the end month and end year, in which case the copy and extraction process has finished; otherwise, the script increments processmonth to the next value and repeats the process. For example, if processmonth is 6 (June), the script increments it to 7 (July). After the script increments the month to greater than 12 (December), it resets processmonth to 1 (January) and increments processyear by 1.
Logonlogoff.cmd also includes some simple code to append a leading 0 to one-digit months because the naming convention of the .zip archives and event-log dumps have leading 0s for one-digit months (e.g., June is 06, not 6). This step ensures that the script can create the proper filenames. The following code adds the leading 0 when necessary.
If %start_month% GEQ 10 goto :checkendfilter Set start_month=0%start_month% :checkendfilter
Because the log files are grouped by month and year, to process only the logs within the specified date range, the script must delete the unneeded log files that have been extracted into %logonlogofftemp%, as the code at callout D shows. For example, if the user wants to generate logon and logoff reports for 5/15/2002 through 6/15/2002, the script extracts log files from 5/1/2002 to 6/30/2002, then deletes the log files from 5/1/2002 through 5/14/2002 and 6/16/2002 through 6/30/2002.
Now, the only log files remaining in %logonlogofftemp% are files that you need to generate the reports. To make your life easier, Logonlogoff.cmd filters the contents of each log file for entries that contain the username that you want to report on. The script stores this value in the userid variable, then outputs the matching entries into a text file called %userid%.log.
You now have a file that contains only entries for the specified user that are within the desired date range. The script then filters %userid%.log for event ID 528 (Logon events) and event ID 538 (Logoff events) and separates the logon and logoff events into two files: %userid%_logon.log and %userid%_logoff.log. This step is important because logon events need additional string manipulation to extract the workstation that the user logged on from; logoff events don't contain workstation information.
The :outputreport section of Logonlogoff.cmd formats the output into .csv format. For logon events, the script passes the date and time, username, and description fields to :outputreport; for logoff events, the script passes the date and time and username fields. The date and time format of the log dump file isn't Microsoft Excelfriendly; if you import this .csv file into Excel, you can't sort the rows by the date and time column. To eliminate this problem, the script needs to reformat the date into a proper Excel date and time format (i.e., month/day/year time). It does so by stripping the day of the week from the date and time string; tokenizing the rest of the string into date, month, year, and time; and converting the months to numerical values. To determine the workstation from which the user logged on, the script needs to know whether the log is a Win2K log or an NT log (remember that the user specified the OS through a command-line parameter). In Win2K, the workstation is the second token in the description field if the description is tokenized and the backslash delimits it. In NT, the workstation is the ninth token in the description field when the colon (:) is the delimiter. You can also check for interactive logons on the server if you tokenize the description field, delimited with the close parenthesis character—)—and you look at the first character of the second token. To generate the report, the script outputs the username, the reformatted date and time, and the workstation (in the case of logon events) to %userid%.csv in the current directory. After generating the report, the script deletes the %logonlogofftemp% directory to free up space.
Script Limitations
Logonlogoff.cmd produces an Excel-compliant .csv file called %userid%.csv that contains information about when the user logged on and logged off the network and from which workstation. Although the script is a handy security tool, it has a few limitations. First, because the script obtains the date ranges from the filename and not the contents of the log file, the output might contain dates that are outside the specified range if you don't dump logs on a daily basis. Also, a user might have multiple logon and logoff entries on the same day at different times in the output even if the user logged on and logged off from the workstation only once during the day. This limitation isn't a defect in the script but rather a feature of Win2K and NT. Mapping drives and other operations that require authentication generate security events, including logon and logoff entries, in the log.
Auditing event logs is a tedious but important task. In this two-part series, I've shown you how to simplify this task by describing a script that automates the process of saving event logs and another script that processes the logs into useful information. You can use both scripts to create useful and inexpensive logon and logoff reports from your Security event logs.
