Function listing

The following functions are available within Log Parser.

ADD 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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. See also Microsoft Log Parser timestamp formats by James Skemp. There seems to be no difference between using TO_TIMESTAMP() and just TIMESTAMP(). Type: conversion
  • 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.
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

blog comments powered by Disqus