Extracting user login events from Security.evtx with Log Parser [Updated!]

UPDATE – At the bottom of the page, I have included an Excel macro to help cleanup the CSV output from Log Parser.


Investigations usually center around what was happening, and when. In a corporate environment, things can sometimes get turned on their heads.

Instead of concentrating on the WHAT, the primary focus could turn out be the WHEN. If there is an allegation that a user is ducking out early or working overtime without tracking it, it can be very important to know when they were logged into their computer. Comparing this information to time entry systems can show discrepancies that need to be addressed.

Fortunately, Windows logs much of this information for us by default. In Vista and above, the log file we need to look at is C:\Windows\System32\winevt\Logs\Security.evtx. We could go through the event log, line by line, but why when there is a better way?! (Windows XP also had a security event log, but because it did not log an event when a user locked a workstation, it was much less useful for building a usage timeline.)

There are many event IDs in the security log, but only a few that we need to look at for our usage timeline. They are:

4800 – Workstation was LOCKED
4801 – Workstation was UNLOCKED
4802 – Screensaver INVOKED
4803 – Screensaver DISMISSED
4647 – User Initiated LOGOFF
4648 – User LOGON

There are other events created by various user actions, but these six will give us an accurate picture of when a workstation was in use. (Random factoid: Most event IDs in Vista and above correlate to the same event ID in XP + 4096. Why? Good question.)

Using LogParser from Microsoft, we can pull out just these six events for a particular user. If you have not used LogParser before, I highly recommend giving it a look. With it, you can search quite a few different log formats, using a SQL like query. There’s even a few GUI options out there for those that are command-line averse.

I setup a batch file that I can just drop a Security.evtx file onto. It will prompt for the userID I am interested in, and spit out a CSV with the six events listed above. Here is my batch file:

@SET /p USERNAME=”Enter UserID:”
“c:\program files (x86)\log parser 2.2\logparser.exe” -i:EVT -o:CSV -stats:OFF “SELECT TimeGenerated,SYSTEM_UTCOFFSET() as UTC_Offset,EventID,ComputerName, CASE EventID  WHEN 4800 THEN SUBSTR(Message,0,26) WHEN 4801 THEN SUBSTR(Message,0,28) WHEN 4802 THEN SUBSTR(Message,0,28) WHEN 4803 THEN SUBSTR(Message,0,30) WHEN 4647 THEN SUBSTR(Message,0,21) WHEN 4648 THEN SUBSTR(Message,0,48) END as EventDesc,Message FROM %1 WHERE (EventID=4800 or EventID=4801 or EventID=4802 or EventID=4803 or EventID=4648 or EventID=4647) AND STRCNT(Message,’%USERNAME%’)=1”> %1.csv

Event times are stored in UTC, but when they are displayed, or extracted with LogParser, they get converted into the local timezone of the machine you are examining them on. For this reason, the script above exports the event time into one column, and the current system UTC offset in another. This makes it easier to adjust events for different timezones.

The event ID number and ComputerName are the next two columns, allowing logs to be combined and filtered between different computers.

Next is a short version of the event description, just telling us what event type it is. The final column is the complete dump of the event description, which will include SIDs, session IDs, and other info that may or may not be relevant.

So we’re done now, right? We can just take the event times at face value and move on?? We don’t need to worry about the screensaver, LOCKED/UNLOCKED tells us all we need, right???

Wrong, of course. There is another factor that needs to be taken into account. Some organizations mandate that the screensaver kick in after a certain period of inactivity (15 minutes, one hour, etc). After that time, the user needs to re-enter their login credentials to unlock the computer.

The trick here is that the workstation does not actually LOCK until the screensaver is dismissed! In other words, if I walk away from my computer at 11 am without locking it, the screensaver kicks in 15 minutes later, and I come back at 1 pm (a nice, long lunch), wiggle the mouse to wake up the computer and then login, the log events will look like this:

11:15 am – Screensaver INVOKED
11:59 am – Screensaver DISMISSED
1:00 pm – Workstation LOCKED
1:01 pm – Workstation UNLOCKED

If we ignored the screensaver events, you would never know I took a two hour lunch. Instead, it would look like I was only gone for a minute!

In order to deal with these properly, we need to check each Screensaver invoked message to see if the user manually locked the workstation first. If not, we need to determine the screensaver interval and subtract that interval from the INVOKED time to determine when the workstation was first idle.

If you are looking to paint a more complete picture of user activity throughout the day, consider integrating other logged events, such as email sent time, instant messaging, phone logs, and web history. Combining these data points can offer a fairly complete picture of an employee’s activity throughout the day.

Update!

I have put together an Excel macro that will cleanup the CSV file generated by Log Parser above. You can download it here. Load the macro into your Personal.xlsb file and run it after opening the CSV. Depending on what your screensaver timeout is, you might need to adjust the variable in the macro (It is set to 15 minutes).

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s