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

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

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

blog comments powered by Disqus