Interpreting SSH Server Log Files using Microsoft Log Parser

Bitvise SSH Server's textual log files are recorded in a machine processable XML format. It is straightforward to process XML files using any .NET language, but another way to extract information from Bitvise SSH Server log files is using Microsoft Log Parser.

When using Microsoft Log Parser, it is important to use the following parameters:

-fNames:XPath -fMode:Tree

With the -fNames:XPath parameter, field names will appear unambiguously, using the full XPath of the attribute to which they refer. The default setting, "compact", can assign unpredictable field names to attributes used in different types of log entries.

Example 1

A basic command to find out who and when logged onto the server:

LogParser -i:XML -fNames:XPath -fMode:Tree "SELECT /log/event/@time, /log/event/session/@windowsAccount, /log/event/session/@virtualAccount FROM *.log WHERE /log/event/@name = 'I_LOGON_AUTH_SUCCEEDED'"

A optimization of the above command - we can shorten the paths using the -rootXPath parameter:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time, /event/session/@windowsAccount, /event/session/@virtualAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED'"

We can use aliases to clarify output headers:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@windowsAccount AS WinAccount, /event/session/@virtualAccount AS VirtAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED'"

Show only logons to Windows accounts:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@windowsAccount AS WinAccount, FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND /event/session/@virtualAccount IS NULL"

Show only logons to virtual accounts - using the VirtAccount alias in WHERE clause to shorten the command:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@virtualAccount AS VirtAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND VirtAccount IS NOT NULL"

Show all Windows and virtual logons on August 7, 2014 (local time):

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@windowsAccount AS WinAccount, /event/session/@virtualAccount AS VirtAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND Time BETWEEN '2014-08-07' AND '2014-08-08'"

Example 2

Enumerate the times and remote addresses of logins for a virtual user named "Michele". This is not yet ideal - the matching in this example is case-sensitive:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@remoteAddress AS RemoteAddress FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND /event/session/@virtualAccount = 'Michele'"

An improvement on the above matches the username in case-insensitive fashion:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@remoteAddress AS RemoteAddress FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND /event/session/@virtualAccount LIKE 'Michele'"

To enumerate the times and remote addresses of all virtual users whose name starts with "M", case insensitive:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@remoteAddress AS RemoteAddress, /event/session/@virtualAccount AS VirtAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_SUCCEEDED' AND VirtAccount LIKE 'M%'"

Example 3

To find out who and when transferred which files:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@windowsAccount AS WinAccount, /event/session/@virtualAccount AS VirtAccount, /event/sfs/parameters/@path AS Path, /event/sfs/parameters/@bytesWritten AS BytesWritten, /event/sfs/parameters/@bytesRead AS BytesRead FROM *.log WHERE /event/@name = 'I_SFS_TRANSFER_FILE'"

Limit the above query to files that have only been written to:

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/session/@windowsAccount AS WinAccount, /event/session/@virtualAccount AS VirtAccount, /event/sfs/parameters/@path AS Path, /event/sfs/parameters/@bytesWritten AS BytesWritten, /event/sfs/parameters/@bytesRead AS BytesRead FROM *.log WHERE /event/@name = 'I_SFS_TRANSFER_FILE' AND BytesWritten <> 0"

Find out who removed which '*.docx' or '*.doc' files (case-insensitive) on August 7, 2014, between 2 pm and 7:30 pm (local time):

LogParser -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/session/@windowsAccount AS WinAccount, /event/session/@virtualAccount AS VirtAccount, /event/sfs/parameters/@path AS Path FROM *.log WHERE /event/@name = 'I_SFS_REMOVE_FILE' AND /event/@time BETWEEN '2014-08-07 14:00' AND '2014-08-07 19:30' AND (Path LIKE '%.docx' OR Path LIKE '%.doc')"

More Information

For more information on the Log Parser's SQL SELECT statement, execute:

LogParser -h GRAMMAR

We also suggest checking out the -o:DATAGRID output format.