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 MS 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.

If the SSH Server is running...

If the SSH Server is currently running, then it keeps the most recent log file open for writing. The Log Parser will then refuse to process that log file.

To work around this, you can either stop the SSH Server, or copy the log files to another directory before processing. You can copy the most recent log file as well, even while it is open for writing.

Example 1

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

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

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

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

We can use aliases to clarify output headers:

Show only logons to Windows accounts:

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

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

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

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

LogParser -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/conn/@windowsAccount AS WinAccount, /event/conn/@virtualAccount AS VirtAccount FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_COMPLETED' AND Time BETWEEN '2022-08-07' AND '2022-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 -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/conn/@remoteAddress AS RemoteAddress FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_COMPLETED' AND /event/conn/@virtualAccount = 'Michele'"

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

Enumerate distinct IP addresses from which the virtual user "Michele" has connected:

LogParser -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT DISTINCT EXTRACT_PREFIX(/event/conn/@remoteAddress, 0, ':') AS RemoteIP FROM *.log WHERE /event/@name = 'I_LOGON_AUTH_COMPLETED' AND /event/conn/@virtualAccount LIKE 'Michele'"

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

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

Example 3

To find out who and when transferred which files:

LogParser -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/conn/@windowsAccount AS WinAccount, /event/conn/@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 -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time AS Time, /event/conn/@windowsAccount AS WinAccount, /event/conn/@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, 2022, between 2 pm and 7:30 pm (local time):

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

Extracting negotiated algorithms

If your settings still enable SSH algorithms which are no longer recommended for use, you can check if any clients are using those algorithms before you disable them. The following example extracts information about SSH algorithms negotiated by any client in available log files:

LogParser -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time as Time, /event/conn/@id as Id, /event/conn/@remoteAddress as RemoteAddr, /event/parameters/@kexAlg as KexAlg, /event/parameters/@hostKeyAlg as HostKeyAlg, /event/parameters/@cipherAlgIn as CipherAlgIn, /event/parameters/@cipherAlgOut as CipherAlgOut, /event/parameters/@macAlgIn as MacAlgIn, /event/parameters/@macAlgOut as MacAlgOut FROM *.log WHERE /event/@name='I_SSH_KEY_EXCHANGE_ALGORITHMS'"

For several algorithms, Bitvise software logs friendly names because some of the technical names are unintuitive or verbose. The algorithm "ssh-rsa" is recorded as either "RSA" or "RSA/sha1", depending on whether it's used as a public key format or a signature algorithm. The algorithms "rsa-sha2-256" and "rsa-sha2-512" are recorded as "RSA/sha2-256" and "RSA/sha2-512".

If you enabled support for FTPS, the following example extracts information about the TLS protocol and cipher suite negotiated by FTPS clients:

LogParser -q -i:XML -fNames:XPath -fMode:Tree -rootXPath:/log/event "SELECT /event/@time as Time, /event/conn/@id as Id, /event/conn/@remoteAddress as RemoteAddr, /event/parameters/@protocol as Protocol, /event/parameters/@cipherSuite as CipherSuite FROM *.log WHERE /event/@name='I_FTP_CONTROL_TLS_NEGOTIATED'"

Processing statistics files

In addition to log files, the SSH Server maintains usage statistics in XML format. These are stored in the Stats subdirectory of the SSH Server installation directory. Statistics files can also be processed using MS Log parser. For example:

LogParser -q -i:XML -fNames:XPath -q -fMode:Tree "SELECT DISTINCT /stats/@type, /stats/@account, /stats/info/@lastLogin FROM *.xml"

This will display the last login time for each virtual and Windows account recorded in SSH Server statistics.

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.