format

Owned by Omry Koschitzky
Last updated: Aug 05, 2024 by Eran
5 min read

Synopsis

Displays a specified column in the complex search summary table in a specified format. Can be used only with Display, Display only, and Group by commands.

Syntax

in [format_type] format)(["Input_Unit"],)(["Output_Unit"])

Required Arguments

format_type

Syntax: number, simple, time, date, volume, regexp, expression or query

Description: The format in which to display the values of a specific column in the complex search summary table. For a time format_type, if no unit appears after  time format, XpoLog assumes that the column value is in milliseconds and displays it in the maximal possible unit (for example, if the value is 2000, the output is 2 seconds; if the value is 120000, the output is 2 minutes, etc.).

Optional Arguments

"Input_Unit"

Syntax: Volume Units - B, KB, MB, GB; Time Units: microsec, ms, sec, min, hour, day

Description: The input unit of the format type.

"Output_Unit"

Syntax: Volume Units - B, KB, MB, GB; Time Units: microsec, ms, sec, min, hour, day

Description: The unit in which to convert the format type.

Note: If only one unit appears in the syntax, XpoLog assumes that it is the output unit, and that the input value is in milliseconds (for time) or bytes (for volume). If no unit appears in the syntax, XpoLog outputs the log value in milliseconds (for time) or bytes (for volume).

Description

Displays the column values in the specified format, assuming the default input and output units, if they are not specified, and converting to a specific output unit from a specific input unit, if specified. 

Text can be formatted into the following format types:

  • number  –  formats the text in the column to number format; (“#.##”) – the decimal format of the number

  • simple – displays columns in difference format: (“column.name1 – column.name2”) – replace the columns with the values from the result

  • time – displays the value in a time format of the default unit or of the indicated (“[OUTPUT_UNIT]”) , (“[INPUT_UNIT] “,“[OUTPUT_UNIT]”) – displays the column in output format and uses the input unit In case it is different from milliseconds.
    Time units: [microsec,ms,sec,min,hour,day]

  • date – displays the value in day format: (“[SIMPLE DATE FORMAT]”) – change the date format

  • volume – displays the value in volume format way: (“[OUTPUT_UNIT]”) , (“[INPUT_UNIT] “,“[OUTPUT_UNIT]”) – display the column in output format and use input unit in case it is different from bytes.
    Volume units: [B,KB,MB,GB]

  • regexp – use regexp to extract values from the data: (“[REGEXP]”) – display the first group that is found from the regular expression

  • expression – displays the column result after performing an expression on the original contents.
    Display Column_Name in regexp format("REGEXP"), where REGEXP is the regular expression to be executed on the value in Column_Name (“[EXPRESSION]”) – use an expression to calculate different result value

  • query - displays an aggregated result broke into groups based on search queries constraints.
    ...group by FIELD_NAME in query format ("SEARCH_QUERY_1","RESULT_NAME_2","SEARCH_QUERY_2","RESULT_NAME_N",..., "SEARCH_QUERY_N","RESULT_NAME_N")
    It is possible to use '*' at the end as a query to group the undefined results of the other queries: 
    status != NULL in log.access | count | group by status in query format ("status=200","VALID","*","ALL_THE_REST")

  • eventquery - provides the option to manipulate the group by columns's values with custom values based on sub search queries. There is an option to grant specific values for specific conditions, and grant also generic values for columns on which none of the conditions have been matched.
    * in log.windows-users | count | group by username in eventQuery format (username contains $, "service", username contains system, "System User", "*","ALL_THE_OTHERS")

  • exception - displays an aggregated result broke into groups based on number of lines in the stack trace.
    ...group by FIELD_NAME in exception format ("NUMBER_OF_LINES","SHOW_MESSAGE")

    error in log.log4j log | count | group by message in exception format ("1","true")

  • replace – use replace to replace a value from the data with a custom value.

    ....group by FIELD_NAME in replace format ("REGEXP_1","REPLACE_TEXT_1",”REGEXP_2","REPLACE_TEXT_2”,….,”REGEXP_N","REPLACE_TEXT_N”)

    status != NULL in log.access | count | group by status in replace format  ("200","OK","302","Resource temporarily moved to a new location","304","Not Modified")

  • UserAgentDetect – displays an aggregated result broke into groups based on types to view (browser,version,platform,os).

    ...group by FIELD_NAME in UserAgentDetect format ("TYPE_1+….+TYPE_N”)

     status != NULL in log.Access Log | count | group by user agent in useragentdetect format ("browser+version")

  • substract - displays an aggregated substraction results based on the value of two numric columns (the values can be either integers or doubles). 
    ...group by NUMBER_1, NUMBER_2, RESULT in substract format ("NUMBER1","NUMBER2")

  • TimeFromNow - displays the difference between a given timestamp object to the execution time of the query. Main usecase for usage of this format will be calculation of the elapsed time from the moment of the last event until now.
    ...group by creationdate in timefromnow format | display creationdate in time format

  • compareEpoch - receives two timestamps which are represented in full epoch format (13 digits). The function returns true in case that the first timestamp is at least from the same day as the second date, false otherwise.
    ...group by epochtime_1, epochtime_2, diff | display diff in compareEpochFormat ("epochtime_1","epochtime_2") | where diff = true

  • validateSlaFormat - receives three parameters - creation date in full epoch format (13 digits), number which represents the sla in days per a specific job and the format of business days that should be calculated ("ISR" - will represent israeli business days - Friday and Saturday will be eliminated from the calculation or "Default" - will represent world business days - Saturday and Sunday will be eliminated from the calculation). The function will return the number of days left from the creation time of the task until today, in manner to the completion due date. In case that a negative number will be returned, it means that the job exceeds its SLA of completion.
    ...jobid = 1234 | count | group by creationdate, sla, sla as days_left | display days_left in validateSla format ("creationdate","sla","ISR")

  • convertToDate - converts a timestamp object into a date object with the following structure - yyyy-MM-dd HH:mm:ss
    syntax sample - jobid = 1234 | count | group by creationdate in converttodate format

  • lowercaseFormat - receives a string and returns the same string, all in lowercase
    ...eventID = 4624 | count | group by event description in lowercase format

  • uppercaseFormat - receives a string and returns the same string, all in uppercase
    syntax sample = eventID = 4624 | count | group by event description in uppercase format

 

Examples – Volume Format: bytes sent column contains numeric values representing volume. 

 Example 1:

* in log.access | avg bytes sent | display avg in volume format

XpoLog formats avg of bytes sent in volume format, automatically assuming that the log value is in bytes.

Example 2:

* in log.access | avg bytes sent | display avg in volume format(“MB”)

XpoLog formats avg of bytes sent in volume format, automatically assuming that the log value is in bytes, and converts and outputs the value in MB.

Example 3:

* in log.access | avg bytes sent | display avg in volume format(“KB”,”MB”)

XpoLog formats avg of bytes sent in volume format, assuming that the log value is in KB, and converts and outputs the value in MB.

Examples – Time Format: time taken column contains numeric value representing time.

Example 1:

* in log.access | avg time taken | display avg in time format

XpoLog formats avg of time taken in time format, automatically assuming that the log value is in milliseconds.

Example 2:

* in log.access | avg time taken | display avg in time format(“SEC”) à format to seconds

XpoLog formats avg of time taken in time format, automatically assuming that the log value is in milliseconds, and converts and outputs the value in seconds.

Example 3:

* in log.access | avg time taken | display avg in time format(“SEC”,”MIN”) à format from seconds to minutes

XpoLog formats avg of time taken in time format, assuming that the log value is in seconds, and converts and outputs the value to minutes. 

Regular Expressions:

  1. XpoLog groups by URL field which has multiple parts divided by slashes / and then uses a regular expression to format the result to present only part of the URL based on the regular expression criteria, I.E. present only the last part after the last slash / in the URL:

    URL Example:
    [URL] /home/web-main/css/texts.css

    XpoLog Query:
    * in log.access log | count | group by url as formatted-url | order by count desc | display formatted-url in regexp format (".*/([^/]+)")

    Result:







  2. XpoLog uses a regular expression to format the Description field which contains multiple lines with different values based on the regular expression criteria, I.E. extract from the entire Description field only the value which comes after 'Account Name:' and group by it only (as if it was a pre-configured field in the log):

    Description Example:
     ...[Description] An account was logged off.
    Subject:
    Security ID: S-1-5-21-3480273402-748593870-3636473903-1144
    Account Name: xplg
    Account Domain: XPOLOG
    Logon ID: 0xa078ea24
    Logon Type: 3
    This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer.

    XpoLog Query:
    (*) in log.application | count | group by Description as UserName in regexp format ("Account Name:\s+(\w+)")


    Result:

 

Query Format:

  1. XpoLog groups by STATUS which has multiple values, and then based on query criteria it breaks the result to different pieces:
    Status values may vary from 200, 302, 404, 500, etc. but in order to break it into two groups 200 - defined as valid and not 200 as not valid the query format handles it:

    XpoLog Query:
    * in log.access | count | group by status in query format ("status=200","VALID","status != 200","NOT VALID")


    Result: