Windows Server has many events logs, trace logs, and can support logging to performance, IIS, and other data to databases. In some cases, the activity logs for some services are in plain old fashioned – yes, you guessed it – text files. While there are many free and pricey tools out in the wild for managing various aspects of Windows services, many people still ask me how they can work with certain kinds of logs and actually use the data.
This week in a Windows Network Infrastructure class, we were looking at some log files for the Dynamic Host Configuration Protocol (DHCP), which are text-oriented log files. While there are often .NET classes, WMI classes, and other such application programming interfaces (APIs) for managing Windows subsystem, many systems administrators could probably just learn to read the raw data in the files far more easily than learning some of the APIs for managing a service such as DHCP. Instead, let’s look at a third option – an alternative to both learning the API, and learning the details of the file format.
For some log files, we can simply use PowerShell commands such as:
and then quickly manipulate the data to suit our needs.
DHCP logs happen to be a bit more complicated. The files start with a line with a couple of tabs and the title “Microsoft DHCP Service Activity Log.” After a couple of blank lines, there is a table of Event ID values and their meanings. Each line in the body of the table has an numeric event ID followed by a tab character and then a string representing the meaning of the particular event id. Then, after another blank line is a single line which defines some values for the QResult and ProbationTime attributes of the subsequent log entries. Indeed, following yet another blank line is a comma-separated header line which identifies the fields of each event, followed by each of the comma-separated events themselves.
Parsing all of this is relatively easy, and we could take a look at writing a PowerShell script to absorb all of it, however we’ll start simple. Let’s first just target the raw events. While there are probably a bazillion ways to process this data, consider the following technique. How do we find the actual events in the file? Select-String to the rescue! If you heard of the grep command from the UNIX world, you could think of PowerShell’s Select-String cmdlet as a .NET oriented, PowerShell oriented, regular expression matcher that, while not exactly grep, is still pretty powerful. To find the line with the headers for the event table in a DHCP log file, simply use:
Select-String “ID,Date,Time” DhcpSrvLog-Wed.log
Wonderful, so Select-String can show us the line in the file that we already knew the first three words of. How is this useful? Not very. However, knowing that the output of Select-String is an object rather than just text, and extracting the LineNumber property of the search results might help.
$x = (select-string “ID,Date,Time” DhcpSrvLog-Wed.log).LineNumber-1
$log = get-content DhcpSrvLog-Wed.log
$y = $log.count-1
The above PowerShell code snippet extracts the events from a DHCP activity log as follows. First, we find the line number of the line with the “ID,Date,Time” string, subtract one from it so it can be used as an index later (line numbers are one-based as reported by Select-String, yet integer-indexed arrays are zero-based) and save this value in a variable $x. Next, we get the contents of the whole file and keep it in a variable $log. This isn’t necessary, as there are many other techniques to extract the proper lines from the file, however our chosen technique in the next couple lines is simplified by doing this. Thirdly, we take the number of lines in the log, subtract one, and save the difference in the variable $y. Finally, we reference the activity log table directly with $log[$x..$y] which uses the substring operator .. (dot-dot) to extract the valid lines. This technique could be used when the table is in the middle of a file, and not just the remainder of a file after a certain point like with the DHCP activity logs.
Tying this back in with Import-CSV, unfortunately, the Import-CSV cmdlet does not support bringing in input from a pipeline or even a simple variable. Luckily there are two approaches we can use instead. The method which is PowerShell version 1.0 compatible is to redirect the extracted information ($log[$x..$y] in this example) to a file and use Import-CSV against the file. Of course, a unique pseudo-random file name should be used, however here’s the basic idea with a fixed name:
$dhcpLog = Import-CSV temp.csv
Another approach is possible with PowerShell version 2.0, which is to use the “new” version 2.0 ConvertFrom-CSV cmdlet, which accepts an -InputObject parameter or pipeline input.
$dhcpLog = ($log[$x..$y] | ConvertFrom-CSV)
Certainly, this sort of extraction and conversion technique can be used with IIS log files, DHCP activity logs, and other files which may or may not contain pure comma-separated-values (CSV). In the next article, we’ll look at another aspect of working with DHCP activity logs.