Who, Where, When? Simple Tools for Auditing in the Enterprise

Security Tip of the Month – October 2008
See other Security Tips of the Month

by Sanjay Pandit, Senior Consultant, Microsoft Services, Southern California and Alex Pavlovsky, Senior Consultant, Microsoft Services, Southern California

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

How do you know who is accessing what in your IT environment? This vital question is faced by many security administrators, and many IT organizations have challenges identifying and understanding patterns of client access to enterprise resources. Understanding which user or system account accesses which resources when can be a necessary prerequisite to identity and access management, application or Web services security, data security, Active Directory® domain migration/consolidation, client-side security, and/or governance efforts.

One option is to use the Audit Collection service in Microsoft® System Center Operations Manager. With the help of Audit Collection, organizations can consolidate individual security logs into a centrally managed database, and then analyze events using the data analysis and reporting tools provided by Microsoft SQL Server®. The Audit Collection feature provides many additional capabilities and takes advantage of an existing System Center Operations Manager infrastructure; however, for those environments that do not have System Center Operations Manager in place, there is a simple alternative approach. A combination of scripting and utility applications can be effective in achieving the objective of centralized auditing and reporting.

In order to achieve the goal of audit reporting for resources within the environment, we need to:

Identify the resources in the environment.
Access the logs from each of those resources.
Extract the desired information from the logs.
Present the information in a usable format.

Let’s look at a sample logon event from the Windows® Security Log, which depicts a service account logon.

Several pieces of information can be extracted from the Description portion of the event, such as User Name, Logon Type, and the Workstation Name. For full details of the various logon event types and meanings, please see http://support.microsoft.com/kb/274176 and http://technet.microsoft.com/en-us/library/cc776964.aspx.

The goal then, is to capture this information programmatically and consolidate it to a centralized location. We will use two tools for this.

Windows PowerShell™ is the new command-line shell and scripting language designed for system administration and automation ().

Log Parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory ().

The PowerShell portion of our solution will:

Connect to Active Directory.
Retrieve a list of all computers, including domain controllers, workstations, and servers.
Verify that the target computer is online.
Use Log Parser to retrieve the desired information.

The Log Parser portion of the solution will:

Connect to each computer.
Parse the logs for logon events.
Capture the account and location of each successful authentication.
Output a consolidated report.

Here is the PowerShell script:

PowerShell

##get_logs_computers.ps1
# set the log file date format
$LogFileDate = [string](Get-Date -Format "MM-dd-yyyy_hh-mm-ss")
# create the string representing the output file path
$outFilePath = (get-childitem env:TMP).Value+"\LogonEvents_" + $LogFileDate
# create a new directory entry object
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
# create a new directory searcher
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
# set the search root to the current domain
$objSearcher.SearchRoot = $objDomain
# limit the page size to retrieve sets of data from AD instead of everything at once
$objSearcher.PageSize = 1000
# limit search to computer objects only
$objSearcher.Filter = "(objectclass=computer)"
# set search scope to the entire subtree
$objSearcher.SearchScope = "Subtree"
# run the search query
$colResults = $objSearcher.FindAll()
$colresults | %
{
     # add the name of the machine account to the PCNameCollection object
     $PCNameCollection = $PCNameCOllection + $_.Properties.Item("Name")
}
foreach ($PC in $PCNameCollection)
{
      # ping test - no need to review events if the system is offline
      if ((ping -n 1 -l 1 -w 100 $PC) -like "*Reply from*")
      {
            Write-Host "Processing $PC"
            # the next line runs the log parser to search for logon events on the PC.
#This runs for every pingable computer account name retrieved from AD
            .\logparser.exe "`"SELECT EXTRACT_TOKEN(Strings, 0, `'`|`') AS Account, EXTRACT_TOKEN(Strings,1,`'`|`') as UserDomain, TimeGenerated AS LogonDate, EXTRACT_TOKEN(Strings,6,`'`|`') as Computername INTO $outFilePath.tsv FROM \\$PC\Security WHERE TimeWritten > TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP(`'0000-01-02`', `'yyyy-MM-dd`') ) ) AND eventID in (528;540;541;682;673) AND EventType = 8 AND EventCategory = 2 AND userdomain not like `'%nt authority%`'`" -filemode:0`'"
      }
}
#format / sort output and display the result file
gc ($outFilePath + ".tsv") | sort {$_.Split("`t")[0]} | foreach {$_.Replace("\\","")} | foreach {$_.Replace("`t",";")} | add-content ($outFilePath + ".txt")
write-host "Processing completed - review $outfilePath .txt for results"
notepad ($outfilepath + ".txt")
#end script

Upon execution, you will see output similar to the one shown below.

This script provides a simple solution for reporting these accesses, as seen in the sample output below, which is output to a comma-delimited file (.CSV). This provides flexibility in performing additional analysis of the data using Microsoft Office Excel® or other applications or tools.

The simple script shown here can also provide a framework for more detailed reporting and analysis. For example, Log Parser has multiple output options including those to a CSV file, XML, or SQL Server database. This opens up a myriad of options, particularly when using SQL Server Analysis Services or SQL Server and Reporting Services capabilities to provide advanced reporting. This script is simply executing the Log Parser and passing the list of ping-able computers. Log Parser also provides a Component Object Model (COM) application programming interface (API) that can be used to provide more flexible control of the query syntax.

This lightweight solution provides a starting point for an organization to gather a point-in-time view of its IT environment. Although this option lacks some of the inherent security, reporting, and ongoing consolidation capabilities of System Center Operations Manager Audit Collection, the information provided by this solution can help an organization gain a better understanding of how and when accounts are used.

For more complex examples including reporting and data analysis, see our blog at http://www.infradevs.com.