## Function listing

The following functions are available within Log Parser.

ADD( addend1 <any type>, addend2 <any type> ) Calculates the sum of two values. Returns a value of the same type as its arguments. Type: arithmetical

### AVG

AVG( [ DISTINCT | ALL ] <field_expr> ) Returns the average among all the values, or only the DISTINCT values, of the specified field-expression. Type: aggregate

### BIT_AND

BIT_AND( arg1 <INTEGER>, arg2 <INTEGER> ) Calculates the bitwise AND of two values. Type: arithmetical

### BIT_NOT

BIT_NOT( arg <INTEGER> ) Calculates the bitwise NOT of a value. Type: arithmetical

### BIT_OR

BIT_OR( arg1 <INTEGER>, arg2 <INTEGER> ) Calculates the bitwise OR of two values. Type: arithmetical

### BIT_SHL

BIT_SHL( arg1 <INTEGER>, arg2 <INTEGER> ) Shifts a value left by a specified number of bits. Type: arithmetical

### BIT_SHR

BIT_SHR( arg1 <INTEGER>, arg2 <INTEGER> ) Shifts a value right by a specified number of bits. Type: arithmetical

### BIT_XOR

BIT_XOR( arg1 <INTEGER>, arg2 <INTEGER> ) Calculates the bitwise XOR of two values. Type: arithmetical

### CASE

CASE <field_expression> WHEN <field_expression> THEN <field_expression> [ ... ] [ ELSE <field_expression> ] END Compares the argument of the CASE statement with the arguments of the WHEN statements, returning the argument of a THEN statement when a match is found. If no match is found, returns the argument of the ELSE statement, or NULL if no ELSE statement is provided. Type: miscellaneous

### COALESCE

COALESCE( arg1 <any type>, arg2 <any type> [, ....] ) Returns the first non-NULL value among its arguments. Type: miscellaneous

### COMPUTER_NAME

COMPUTER_NAME() Returns the NetBIOS name of the local computer. Type: system information

### COUNT

COUNT( [ DISTINCT | ALL ] * ) | ( [ DISTINCT | ALL ] <field_expr_list> ) Returns the number of items in a group. Type: aggregate

#### Examples

• `COUNT(DISTINCT cs-uri-stem) AS [Distinct Requests]`
Returns a count of how many distinct files were requested.

### DIV

DIV( dividend <INTEGER | REAL>, divisor <INTEGER | REAL> ) Calculates the quotient of two values. Type: arithmetical

#### Examples

• `DIV(sc-bytes, 1024) AS [KB Sent]`
Converts bytes sent from the server to the client to Kilobytes (KB).
• `DIV(DIV(sc-bytes, 1024), 1024) AS [MB Sent]`
Converts bytes sent from the server to the client to Megabytes (MB).

### EXP

EXP( argument <INTEGER | REAL> ) Calculates e (the Natural logarithm base) raised to the power of the specified argument. Type: arithmetical

### EXP10

EXP10( argument <INTEGER | REAL> ) Calculates 10 raised to the power of the specified argument. Type: arithmetical

### EXTRACT_EXTENSION

EXTRACT_EXTENSION( filepath <STRING> ) Returns the file extension portion of a file path. Type: string manipulation

#### Examples

• `EXTRACT_EXTENSION(cs-uri-stem) AS [File Extension]`
Extracts the file extension for requests. Returns nothing for those without an extension.
• `EXTRACT_EXTENSION(cs-uri-stem) NOT IN ('jpg';'png';'gif';'ico')`
When used in a WHERE clause, excludes requests for common image formats.

### EXTRACT_FILENAME

EXTRACT_FILENAME( filepath <STRING> ) Returns the filename portion of a file path. Type: string manipulation

#### Examples

• `EXTRACT_FILENAME(cs-uri-stem) AS [File Requested]`
Extracts the file name for requests.

### EXTRACT_PATH

EXTRACT_PATH( filepath <STRING> ) Returns the directory path portion of a file path. Type: string manipulation

#### Examples

• `EXTRACT_PATH(cs-uri-stem) AS [Path Requested]`
Extracts the path of a request. The trailing slash (/) is removed.

### EXTRACT_PREFIX

EXTRACT_PREFIX( argument <STRING>, index <INTEGER>, separator <STRING> ) Returns a substring beginning at the first character and up to, but not including, the specified instance of the separator. Type: string manipulation

### EXTRACT_SUFFIX

EXTRACT_SUFFIX( argument <STRING>, index <INTEGER>, separator <STRING> ) Returns a substring beginning after the specified instance of the separator and up to the end of the string. Type: string manipulation

### EXTRACT_TOKEN

EXTRACT_TOKEN( argument <STRING>, index <INTEGER> [ , separator <STRING> ] ) Splits the string into substrings at each point where the separator occurs, and returns the substring with the specified index. Type: string manipulation

#### Examples

• `EXTRACT_TOKEN(cs(Referer), 2, '/') AS [Referring Domain]`
Returns full domain of the site referring traffic to this resource.

### EXTRACT_VALUE

EXTRACT_VALUE( argument <STRING>, key <STRING> [ , separator <STRING> ] ) Parses 'key=value' pairs in the string, returning the value corresponding to the specified key. Type: string manipulation

### FLOOR

FLOOR( argument <REAL> ) Returns the integral part of the specified argument. Type: arithmetical

### GROUPING

GROUPING( <field_expr> ) Returns a value of 1 when the row is added by the ROLLUP operator of the GROUP BY clause, or 0 when the row is not the result of ROLLUP. GROUPING is used to distinguish the NULL values returned by ROLLUP from standard NULL values. The NULL returned as the result of a ROLLUP operation is a special use of NULL. It acts as a value placeholder in the result set and means 'all'. Type: aggregate

### HASHMD5_FILE

HASHMD5_FILE( filePath <STRING> ) Calculates the MD5 hash of the content of a file and returns its hexadecimal representation. Type: miscellaneous

### HASHSEQ

HASHSEQ( value <STRING> ) Returns a unique, sequential integer for each distinct value of the argument. Type: miscellaneous

### HEX_TO_ASC

HEX_TO_ASC( hexString <STRING> ) Converts the hexadecimal representation of a series of bytes into a string where the bytes belonging to the 0x20-0x7F range have been converted to ASCII characters. Type: string manipulation

### HEX_TO_HEX16

HEX_TO_HEX16( hexString <STRING> [ , bigEndian <INTEGER> ] ) Converts the hexadecimal representation of a series of bytes into another hexadecimal representation in which bytes are grouped into 16-bit WORDs. Type: string manipulation

### HEX_TO_HEX32

HEX_TO_HEX32( hexString <STRING> [ , bigEndian <INTEGER> ] ) Converts the hexadecimal representation of a series of bytes into another hexadecimal representation in which bytes are grouped into 32-bit DWORDs. Type: string manipulation

### HEX_TO_HEX8

HEX_TO_HEX8( hexString <STRING> ) Converts the hexadecimal representation of a series of bytes into another hexadecimal representation in which bytes are grouped into 8-bit octets. Type: string manipulation

### HEX_TO_INT

HEX_TO_INT( hexString <STRING> ) Converts the hexadecimal representation of an integer into the integer itself. Type: conversion

### HEX_TO_PRINT

HEX_TO_PRINT( hexString <STRING> ) Converts the hexadecimal representation of a series of bytes into a string where the bytes corresponding to printable ASCII characters have been converted to the characters themselves. Type: string manipulation

### IN_ROW_NUMBER

IN_ROW_NUMBER() Returns the current input record number. Type: miscellaneous

### INDEX_OF

INDEX_OF( string <STRING>, searchStr <STRING> ) Returns the character position where the first occurrence of a search substring occurs in a string. Type: string manipulation

### INT_TO_IPV4

INT_TO_IPV4( ipV4Address <INTEGER> ) Converts a 32-bit integer into the string representation of an IPV4 address. Type: conversion

### IPV4_TO_INT

IPV4_TO_INT( ipV4Address <STRING> ) Converts the string representation of an IPV4 address into a 32-bit integer. Type: conversion

### LAST_INDEX_OF

LAST_INDEX_OF( string <STRING>, searchStr <STRING> ) Returns the character position where the last occurrence of a search substring occurs in a string. Type: string manipulation

### LOG

LOG( argument <INTEGER | REAL> ) Calculates the Natural logarithm of the specified argument. Type: arithmetical

### LOG10

LOG10( argument <INTEGER | REAL> ) Calculates the base-10 logarithm of the specified argument. Type: arithmetical

### LTRIM

LTRIM( string <STRING> ) Removes whitespace characters from the beginning of a string. Type: string manipulation

### MAX

MAX( [ DISTINCT | ALL ] <field_expr> ) Returns the maximum value among all the values of the specified field-expression. Type: aggregate

#### Examples

• `MAX(sc-bytes) AS [Maximum Bytes Sent]`
Returns the maximum number of bytes sent from the server to the client, across all requests.

### MIN

MIN( [ DISTINCT | ALL ] <field_expr> ) Returns the minimum value among all the values of the specified field-expression. Type: aggregate

#### Examples

• `MIN(sc-bytes) AS [Minimum Bytes Sent]`
Returns the minimum number of bytes sent from the server to the client, across all requests.

### MOD

MOD( dividend <INTEGER | REAL>, divisor <INTEGER | REAL> ) Calculates the remainder of the division of two numbers. Type: arithmetical

### MUL

MUL( multiplicand <INTEGER | REAL>, multiplier <INTEGER | REAL> ) Calculates the product of two values. Type: arithmetical

#### Examples

• `MUL(PROPCOUNT(*), 100)`
When used with another field and a simple GROUP BY, returns the percentage of requests by that field. See example query HTTP status codes and percentage of total.

### OUT_ROW_NUMBER

OUT_ROW_NUMBER() Returns the current output record number. Type: miscellaneous

### PROPCOUNT

PROPCOUNT( * ) [ ON ( <on_field_expr_list> ) ] | ( <field_expr_list> ) [ ON ( <on_field_expr_list> ) ] Returns the ratio of the COUNT aggregate function calculated on a group to the COUNT aggregate function calculated on a hierarchically higher group. Type: aggregate

### PROPSUM

PROPSUM( <field_expr> ) [ ON ( <on_field_expr_list> ) ] Returns the ratio of the SUM aggregate function calculated on a group to the SUM aggregate function calculated on a hierarchically higher group. Type: aggregate

### QNTFLOOR_TO_DIGIT

QNTFLOOR_TO_DIGIT( value <INTEGER>, digits <INTEGER> ) Truncates a number to a specified number of significant digits, masking the remaining digits to zero. Type: arithmetical

### QNTROUND_TO_DIGIT

QNTROUND_TO_DIGIT( value <INTEGER>, digits <INTEGER> ) Rounds a number to a specified number of significant digits, masking the remaining digits to zero. Type: arithmetical

### QUANTIZE

QUANTIZE( argument <INTEGER | REAL | TIMESTAMP>, quantization <INTEGER | REAL> ) Truncates a value to the nearest multiple of another value. Type: arithmetical

#### Examples

• `QUANTIZE(TO_TIMESTAMP(date, time), 3600)`
Returns date/times to the hour. (In other words, drops the minutes.)

### REPLACE_CHR

REPLACE_CHR( string <STRING>, searchCharacters <STRING>, replaceString <STRING> ) Replaces each occurrence of a character in a given set of characters with a string. Type: string manipulation

### REPLACE_IF_NOT_NULL

REPLACE_IF_NOT_NULL( argument <any type>, replaceValue <any type> ) Returns the second argument when the first argument is not NULL, and NULL otherwise. Type: miscellaneous

### REPLACE_STR

REPLACE_STR( string <STRING>, searchString <STRING>, replaceString <STRING> ) Replaces each occurrence of a substring with a string. Type: string manipulation

### RESOLVE_SID

RESOLVE_SID( sid <STRING> [ , computerName <STRING> ] ) Resolves a SID and returns its full account name. Type: system information

### REVERSEDNS

REVERSEDNS( ipAddress <STRING> ) Resolves an IP address and returns the corresponding host name. Type: system information

#### Examples

• `REVERSEDNS(c-ip)`
Returns the host name for the client’s IP address.

### ROT13

ROT13( string <STRING> ) Encodes or decodes a string using the ROT13 algorithm. Type: string manipulation

### ROUND

ROUND( argument <REAL> ) Returns the integer closest to the specified argument. Type: arithmetical

### RTRIM

RTRIM( string <STRING> ) Removes whitespace characters from the end of a string. Type: string manipulation

### SEQUENCE

SEQUENCE( [ startValue <INTEGER> ] ) Returns a unique sequential integer associated with the current input record number. Type: miscellaneous

### SQR

SQR( argument <INTEGER | REAL> ) Calculates the square of the specified argument. Type: arithmetical

### SQRROOT

SQRROOT( argument <INTEGER | REAL> ) Calculates the square root of the specified argument. Type: arithmetical

### STRCAT

STRCAT( string1 <STRING>, string2 <STRING> ) Appends one string to another. Type: string manipulation

### STRCNT

STRCNT( string <STRING>, token <STRING> ) Returns the number of occurrences of a substring in a string. Type: string manipulation

#### Examples

• `STRCNT(cs-uri-stem, '/') AS [Request Depth]`
Returns the depth of a requested file.

### STRLEN

STRLEN( string <STRING> ) Returns the length of a string. Type: string manipulation

### STRREPEAT

STRREPEAT( string <STRING>, count <INTEGER> ) Creates a string by repeating a substring a given number of times. Type: string manipulation

### STRREV

STRREV( string <STRING> ) Reverses the characters in a string. Type: string manipulation

### SUB

SUB( minuend <any type>, subtrahend <any type> ) Calculates the difference of two values. Type: arithmetical

#### Examples

• `TO_DATE(SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-08', 'yyyy-MM-dd')))`
Gets the date seven days ago, based on the current local date/time.

### SUBSTR

SUBSTR( string <STRING>, start <INTEGER> [ , length <INTEGER> ]) Returns a substring beginning at a specified location and having a specified length. The <start> of <string> begins at 0. Type: string manipulation

### SUM

SUM( [ DISTINCT | ALL ] <field_expr> ) Returns the sum of all the values, or only the DISTINCT values, of the specified field-expression. Type: aggregate

#### Examples

• `SUM(sc-bytes) AS [Total Bytes Sent]`
Returns the total number of bytes sent from the server to the client.

### SYSTEM_DATE

SYSTEM_DATE() Returns the current system date in Universal Time Coordinates (UTC) time. Type: system information

### SYSTEM_TIME

SYSTEM_TIME() Returns the current system time of the day in Universal Time Coordinates (UTC) time. Type: system information

### SYSTEM_TIMESTAMP

SYSTEM_TIMESTAMP() Returns the current system date and time in Universal Time Coordinates (UTC) time. Type: system information

#### Examples

• `TO_LOCALTIME(SYSTEM_TIMESTAMP) AS [CurrentLocalTime]`
Get the current date and time, converted to local time.
• `SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:03', 'yyyy-MM-dd HH:mm'))`
Get the local time 3 minutes ago.

### SYSTEM_UTCOFFSET

SYSTEM_UTCOFFSET() Returns the current system timezone and daylight saving offset relative to Universal Time Coordinates (UTC) time. Type: system information

### TO_DATE

TO_DATE( timestamp <TIMESTAMP> ) Converts a full timestamp into a date-only timestamp. Type: conversion

### TO_HEX

TO_HEX( argument <INTEGER | STRING> ) Returns the hexadecimal representation of an integer or of the characters in a string. Type: conversion

### TO_INT

TO_INT( argument <any type> ) Converts a value of the REAL, STRING, or TIMESTAMP data type into a value of the INTEGER data type. Type: conversion

### TO_LOCALTIME

TO_LOCALTIME( timestamp <TIMESTAMP> ) Converts a timestamp from Universal Time Coordinates (UTC) time into local time. Type: conversion

#### Examples

• `TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [LocalTime]`
Create a timestamp from the date and time of a request and convert it to local time.

### TO_LOWERCASE

TO_LOWERCASE( string <STRING> ) Returns a string where all alphabetic characters have been converted to lowercase. Type: string manipulation

### TO_REAL

TO_REAL( argument <any type> ) Converts a value of the INTEGER, STRING, or TIMESTAMP data type into a value of the REAL data type. Type: conversion

### TO_STRING

TO_STRING( argument <INTEGER | REAL> ) | ( timestamp <TIMESTAMP>, format <STRING> ) Converts a value of the REAL or INTEGER data type into a value of the STRING data type. Type: conversion

#### Examples

• `TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 'yyyy-MM-dd') AS [Day]`
Converts the date and time of a request to local time, and then outputs the day as a string (2010-03-22).

### TO_TIME

TO_TIME( timestamp <TIMESTAMP> ) Converts a full timestamp into a time-only timestamp. Type: conversion

#### Examples

• `TO_TIME(TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600))) AS [Hour]`
Create a timestamp from the date and time of a request, drops the minutes from the time, converts it to local time, and pulls just the time.

### TO_TIMESTAMP

TO_TIMESTAMP( dateTime1 <TIMESTAMP>, dateTime2 <TIMESTAMP> ) | ( string <STRING>, format <STRING> ) ( seconds <INTEGER | REAL> ) Parses a string representing a timestamp and returns a value of the TIMESTAMP data type. Type: conversion

#### Examples

• `TO_TIMESTAMP(date, time)`
Converts the date and time of a request into a timestamp, for use with other functions.
• `TO_TIMESTAMP('2009-02-06', 'yyyy-MM-dd')`
Creates a timestamp of the date February 6, 2009.
• `TO_TIMESTAMP('2010-02-15 10:15', 'yyyy-MM-dd HH:mm')`
Creates a timestamp of February 25, 2010, at 10:15 in the morning.
• `TO_TIMESTAMP('24 Jun 2011 13:22:21', 'dd MMM yyyy HH:mm:ss')`
Creates a timestamp of a date/time in ‘short’ format.

See also Microsoft Log Parser timestamp formats by James Skemp. There seems to be no difference between using `TO_TIMESTAMP()` and just `TIMESTAMP()`.

### TO_UPPERCASE

TO_UPPERCASE( string <STRING> ) Returns a string where all alphabetic characters have been converted to uppercase. Type: string manipulation

### TO_UTCTIME

TO_UTCTIME( timestamp <TIMESTAMP> ) Converts a timestamp from local time into Universal Time Coordinates (UTC) time. Type: conversion

### TRIM

TRIM( string <STRING> ) Removes whitespace characters from the beginning and end of a string. Type: string manipulation

### URLESCAPE

URLESCAPE( url <STRING> [ , codepage <INTEGER> ] ) Converts a string to the URL-encoded form suitable for transmission as an HTTP query string. Type: string manipulation

### URLUNESCAPE

URLUNESCAPE( url <STRING> [ , codepage <INTEGER> ] ) Converts a URL-encoded string into its plain, unencoded form. Type: string manipulation

### WIN32_ERROR_DESCRIPTION

WIN32_ERROR_DESCRIPTION( win32ErrorCode <INTEGER> ) Returns the text message associated with a numeric Windows error code. Type: miscellaneous