--- title: "Derived fields function reference | Lansweeper Platform" slug: "derived-fields-function-reference" description: "Reference for all 101 functions in Lansweeper's derived fields expression language, grouped by category, for building computed fields on your asset data." updated: 2026-06-29T15:01:37Z published: 2026-06-29T15:01:37Z canonical: "docs.lansweeper.com/derived-fields-function-reference" stale: true --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.lansweeper.com/llms.txt > Use this file to discover all available pages before exploring further. # Derived fields function reference All 101 functions in Lansweeper's expression language are listed below, grouped by category. For syntax rules, operators, and control flow (like `CASE` and `If()`), see [Derived fields expression language reference](/docs/derived-fields-expression-language-reference). ## Type notation - `Number`: integer or decimal - `String`: text value - `Date`: date or datetime value - `Bool`: `true` or `false` - `Array(T)`: array of elements of type T - `T`: any type (generic) ## Function summary | Category | Functions | Count | | --- | --- | --- | | [Arithmetic](/docs/derived-fields-function-reference#arithmetic) | `Abs`, `Round`, `Ceil`, `Floor`, `Mod`, `Max2`, `Min2` | 7 | | [Arrays](/docs/derived-fields-function-reference#arrays) | `Length`, `Empty`, `NotEmpty`, `ArraySum`, `ArrayAvg`, `ArrayMin`, `ArrayMax`, `ArrayCount`, `ArrayUniq`, `Has`, `HasAll`, `HasAny`, `IndexOf`, `ArrayExists`, `ArrayAll`, `ArrayFirst`, `ArrayLast`, `ArrayFilter`, `ArrayMap`, `ArraySort`, `ArrayReverseSort`, `ArrayDistinct`, `ArrayConcat`, `ArraySlice`, `ArrayStringConcat`, `ArrayIntersect`, `ArrayExcept` | 27 | | [Conditional](/docs/derived-fields-function-reference#conditional) | `If`, `Coalesce`, `NullIf`, `Clamp`, `Greatest`, `Least` | 6 | | [Date and time](/docs/derived-fields-function-reference#date-and-time) | `Now`, `Today`, `Yesterday`, `DateDiff`, `AddDays`, `AddMonths`, `AddYears`, `AddHours`, `AddWeeks`, `SubtractDays`, `SubtractMonths`, `SubtractYears`, `ToYear`, `ToMonth`, `ToDay`, `ToQuarter`, `ToDayOfWeek`, `ToDayOfYear`, `MonthName`, `ToStartOfDay`, `ToStartOfWeek`, `ToStartOfMonth`, `ToStartOfQuarter`, `ToStartOfYear`, `FormatDateTime`, `MakeDate` | 26 | | [Rounding](/docs/derived-fields-function-reference#rounding) | `Trunc`, `RoundDown` | 2 | | [Splitting and merging](/docs/derived-fields-function-reference#splitting-and-merging) | `SplitByChar`, `SplitByString`, `SplitByWhitespace` | 3 | | [String](/docs/derived-fields-function-reference#string) | `Length`, `Concat`, `ConcatWithSeparator`, `Lower`, `Upper`, `Initcap`, `Trim`, `TrimLeft`, `TrimRight`, `Substring`, `Left`, `Right`, `SubstringIndex`, `Basename`, `StartsWith`, `EndsWith`, `Replace`, `ReplaceOne`, `Position`, `PositionCaseInsensitive`, `CountSubstrings`, `Format`, `Extract` | 23 | | [Type conversion](/docs/derived-fields-function-reference#type-conversion) | `ToString`, `ToNumber`, `ToNumberOrNull`, `ToDate`, `ToDateOrNull`, `ParseDateTimeBestEffort`, `ToBoolean` | 7 | --- ## Arithmetic Basic math operations. Most are also available as operators (`+`, `-`, `*`, `/`), but the function form is useful inside other expressions. ### `Abs` Returns the absolute (non-negative) value of a number. ``` Abs(x: Number) → Number ``` ``` Abs(-42) -- → 42 Abs(DateDiff("day", [Last successful scan], Now())) -- → 15 (always positive, regardless of date order) ``` --- ### `Round` Rounds a number to a specified number of decimal places. Defaults to 0 (nearest integer). ``` Round(x: Number, decimals?: Number) → Number ``` ``` Round(3.7) -- → 4 Round(3.14159, 2) -- → 3.14 Round([Memory] / 1024, 1) -- → 7.8 ``` --- ### `Ceil` Rounds a number up to the nearest integer. ``` Ceil(x: Number) → Number ``` ``` Ceil(3.2) -- → 4 Ceil([Disk Drive Size] / 1073741824) -- → 500 (disk size rounded up to whole GB) ``` --- ### `Floor` Rounds a number down to the nearest integer. ``` Floor(x: Number) → Number ``` ``` Floor(3.9) -- → 3 Floor([Uptime] / 86400000) -- → 12 (complete days of uptime) ``` --- ### `Mod` Returns the remainder of dividing two numbers. ``` Mod(x: Number, y: Number) → Number ``` ``` Mod(10, 3) -- → 1 Mod([Memory], 1024) -- → 512 (leftover MB after converting to full GB) ``` --- ### `Max2` Returns the larger of two values. ``` Max2(x: Number, y: Number) → Number ``` ``` Max2([Critical Severity Vulnerabilities], [High Severity Vulnerabilities]) -- → 5 Max2(0, DateDiff("day", Now(), [Warranty end date])) -- → 0 (if warranty already expired, clamp to zero) ``` --- ### `Min2` Returns the smaller of two values. ``` Min2(x: Number, y: Number) → Number ``` ``` Min2([Number of Vulnerabilities], 100) -- → 100 (cap at 100) Min2([Memory], 32768) -- → 8192 (the actual RAM if under 32GB) ``` --- ## Arrays Functions for working with array fields. In the asset dataset, nested fields like `[Disk Drive Size]`, `[Antivirus display name]`, and `[Registry Value Data]` are arrays. Functions that accept a predicate use the `_` implicit element reference. See [Derived fields expression language reference](/docs/derived-fields-expression-language-reference) for details. ### Size and emptiness #### `Length` Returns the number of elements in an array. > `Length` also works on strings. See the [String](/docs/derived-fields-function-reference#string) section. ``` Length(arr: Array(T)) → Number ``` ``` Length([Disk Drive Size]) -- → 3 (asset has 3 disk drives) Length([Antivirus display name]) -- → 1 ``` --- #### `Empty` Returns `true` if the array has no elements. ``` Empty(arr: Array(T)) → Bool ``` ``` Empty([Antivirus display name]) -- → true (no antivirus installed) Empty([Registry Value Name]) -- → false ``` --- #### `NotEmpty` Returns `true` if the array has at least one element. ``` NotEmpty(arr: Array(T)) → Bool ``` ``` NotEmpty([Disk Drive Size]) -- → true NotEmpty([Encryptable volume drive letter]) -- → true (asset has encryptable volumes) ``` --- ### Aggregation #### `ArraySum` Returns the sum of all elements in an array. ``` ArraySum(arr: Array(Number)) → Number ``` ``` ArraySum([Disk Drive Size]) -- → 1099511627776 (total disk space in bytes) ArraySum([Disk Drive Free Space]) / 1073741824 -- → 250.5 (total free space in GB) ``` --- #### `ArrayAvg` Returns the average of all elements in an array. ``` ArrayAvg(arr: Array(Number)) → Number ``` ``` ArrayAvg([Disk Drive Usage Percentage]) -- → 62.5 (average disk usage across all drives) Round(ArrayAvg([Disk Drive Size]) / 1073741824, 1) -- → 256.0 (average disk size in GB) ``` --- #### `ArrayMin` Returns the smallest element in an array. ``` ArrayMin(arr: Array(Number)) → Number ``` ``` ArrayMin([Disk Drive Free Space]) -- → 1073741824 (least free space across drives, in bytes) ArrayMin([Disk Drive Usage Percentage]) -- → 12.3 (the drive with the most available space) ``` --- #### `ArrayMax` Returns the largest element in an array. ``` ArrayMax(arr: Array(Number)) → Number ``` ``` ArrayMax([Disk Drive Usage Percentage]) -- → 94.7 (the fullest drive) ArrayMax([Disk Drive Size]) / 1073741824 -- → 512 (largest drive in GB) ``` --- #### `ArrayCount` Counts elements that match a condition using `_`. ``` ArrayCount(predicate: Bool, arr: Array(T)) → Number ``` ``` ArrayCount(_ > 80, [Disk Drive Usage Percentage]) -- → 2 (two drives are over 80% full) ArrayCount(_ = true, [Antivirus enabled]) -- → 1 (one antivirus is enabled) ArrayCount(_ ILIKE "%chrome%", [Registry Value Name]) -- → 3 ``` --- #### `ArrayUniq` Returns the number of distinct elements in an array. ``` ArrayUniq(arr: Array(T)) → Number ``` ``` ArrayUniq([Antivirus display name]) -- → 2 (two different antivirus products) ArrayUniq([Registry Path]) -- → 5 ``` --- ### Searching #### `Has` Checks if an array contains a specific value. Returns `true` or `false`. ``` Has(arr: Array(T), value: T) → Bool ``` ``` Has([Antivirus display name], "Windows Defender") -- → true Has([AD Group Name], "Domain Controllers") -- → true Has([1, 2, 3], [Disk Drive Type]) -- → true (drive type is one of 1, 2, or 3) ``` --- #### `HasAll` Checks if an array contains all of the specified values. ``` HasAll(arr: Array(T), values: Array(T)) → Bool ``` ``` HasAll([Antivirus display name], ["Windows Defender", "CrowdStrike Falcon"]) -- → false (doesn't have both) HasAll([AD Group Name], ["Domain Users", "Remote Desktop Users"]) -- → true ``` --- #### `HasAny` Checks if an array contains at least one of the specified values. ``` HasAny(arr: Array(T), values: Array(T)) → Bool ``` ``` HasAny([Antivirus display name], ["Windows Defender", "CrowdStrike Falcon", "Norton"]) -- → true (has at least one) HasAny([Sensor Type], ["LsAgent", "OT Sensor"]) -- → true ``` --- #### `IndexOf` Returns the 1-based position of the first occurrence of a value. Returns `0` if not found. ``` IndexOf(arr: Array(T), value: T) → Number ``` ``` IndexOf([Antivirus display name], "Windows Defender") -- → 1 (first position) IndexOf([Disk Drive Caption], "C:") -- → 0 (not found) ``` --- #### `ArrayExists` Returns `true` if any element matches the condition. ``` ArrayExists(predicate: Bool, arr: Array(T)) → Bool ``` ``` ArrayExists(_ > 90, [Disk Drive Usage Percentage]) -- → true (at least one drive is over 90% full) ArrayExists(_ = false, [Antivirus enabled]) -- → true (at least one antivirus is disabled) ArrayExists(_ ILIKE "%domain controllers%", [AD Group Name]) -- → false ``` --- #### `ArrayAll` Returns `true` if all elements match the condition. ``` ArrayAll(predicate: Bool, arr: Array(T)) → Bool ``` ``` ArrayAll(_ = true, [Antivirus product up to date]) -- → true (all antivirus products are up to date) ArrayAll(_ = 1, [Encryptable volume protection status]) -- → false (not all volumes are encrypted) ArrayAll(_ < 80, [Disk Drive Usage Percentage]) -- → true (no drive is over 80% full) ``` --- #### `ArrayFirst` Returns the first element that matches the condition, or `null` if none match. ``` ArrayFirst(predicate: Bool, arr: Array(T)) → T ``` ``` ArrayFirst(_ > 90, [Disk Drive Usage Percentage]) -- → 94.7 (first drive over 90%) ArrayFirst(_ ILIKE "%defender%", [Antivirus display name]) -- → "Windows Defender" ArrayFirst(_ > 1000, [Disk Drive Size]) -- → null (no match) ``` --- #### `ArrayLast` Returns the last element that matches the condition, or `null` if none match. ``` ArrayLast(predicate: Bool, arr: Array(T)) → T ``` ``` ArrayLast(_ > 50, [Disk Drive Usage Percentage]) -- → 78.2 (last drive over 50%) ``` --- ### Transformation #### `ArrayFilter` Returns a new array containing only elements that match the condition. ``` ArrayFilter(predicate: Bool, arr: Array(T)) → Array(T) ``` ``` ArrayFilter(_ > 80, [Disk Drive Usage Percentage]) -- → [85.3, 94.7] ArrayFilter(_ = 3, [Disk Drive Type]) -- → [3, 3] (only local disks) Length(ArrayFilter(_ ILIKE "%chrome%", [Registry Value Name])) -- → 2 (count of Chrome-related registries) ``` --- #### `ArrayMap` Transforms each element using an expression with `_`. ``` ArrayMap(transform: T2, arr: Array(T)) → Array(T2) ``` ``` ArrayMap(_ / 1073741824, [Disk Drive Size]) -- → [256.0, 512.0, 128.0] (sizes converted to GB) ArrayMap(_ / 1073741824, [Disk Drive Free Space]) -- → [50.2, 200.1, 10.5] (free space in GB) ``` --- #### `ArraySort` Returns the array sorted in ascending order. ``` ArraySort(arr: Array(T)) → Array(T) ``` ``` ArraySort([Disk Drive Usage Percentage]) -- → [12.3, 45.6, 94.7] ArraySort([Antivirus display name]) -- → ["CrowdStrike Falcon", "Windows Defender"] ``` --- #### `ArrayReverseSort` Returns the array sorted in descending order. ``` ArrayReverseSort(arr: Array(T)) → Array(T) ``` ``` ArrayReverseSort([Disk Drive Usage Percentage]) -- → [94.7, 45.6, 12.3] ``` --- #### `ArrayDistinct` Returns the array with duplicate values removed. ``` ArrayDistinct(arr: Array(T)) → Array(T) ``` ``` ArrayDistinct([Disk Drive Type]) -- → [3, 5] (unique drive types) ArrayDistinct([Source Type]) -- → ["LsAgent", "CloudScanner"] ``` --- #### `ArrayConcat` Merges multiple arrays into one. ``` ArrayConcat(arr1: Array(T), arr2: Array(T), ...) → Array(T) ``` ``` ArrayConcat([Antivirus display name], ["Manual Entry"]) -- → ["Windows Defender", "Manual Entry"] ``` --- #### `ArraySlice` Extracts a portion of an array. Offset is 1-based; negative offset counts from the end. ``` ArraySlice(arr: Array(T), offset: Number, length?: Number) → Array(T) ``` ``` ArraySlice([Disk Drive Caption], 1, 2) -- → ["C:", "D:"] (first two drives) ArraySlice([Registry Value Name], -3) -- → last 3 registry entries ``` --- ### Conversion #### `ArrayStringConcat` Joins all array elements into a single string with a separator. ``` ArrayStringConcat(arr: Array(String), separator?: String) → String ``` ``` ArrayStringConcat([Antivirus display name], ", ") -- → "Windows Defender, CrowdStrike Falcon" ArrayStringConcat([AD Group Name], " | ") -- → "Domain Users | Remote Desktop Users | IT Staff" ArrayStringConcat([Disk Drive Caption], ", ") -- → "C:, D:, E:" ``` --- ### Set operations #### `ArrayIntersect` Returns elements present in all given arrays. ``` ArrayIntersect(arr1: Array(T), arr2: Array(T)) → Array(T) ``` ``` ArrayIntersect([AD Group Name], ["Domain Admins", "Schema Admins", "Enterprise Admins"]) -- → ["Domain Admins"] (which privileged groups this asset belongs to) ``` --- #### `ArrayExcept` Returns elements from the first array that are not in the second. ``` ArrayExcept(arr1: Array(T), arr2: Array(T)) → Array(T) ``` ``` ArrayExcept([AD Group Name], ["Domain Users", "Authenticated Users"]) -- → ["IT Staff", "VPN Users"] (non-default groups) ``` --- ## Conditional Functions for branching logic. The `CASE` expression (documented in [Derived fields expression language reference](/docs/derived-fields-expression-language-reference)) is often more readable for multi-branch logic. ### `If` Returns one value when the condition is true, another when false. ``` If(condition: Bool, then: T, else: T) → T ``` ``` If([Memory] > 8192, "high RAM", "low RAM") -- → "high RAM" If([Warranty end date] < Now(), "Expired", "Active") -- → "Expired" If([Origin] = "CLOUD", [Cloud Provider], [installation]) -- → "Microsoft Azure" ``` --- ### `Coalesce` Returns the first non-null value from the arguments. Useful for fallback values. ``` Coalesce(x1: T, x2: T, ...) → T ``` ``` Coalesce([Manufacturer], "Unknown") -- → "Dell" (or "Unknown" if manufacturer is null) Coalesce([Warranty end date], [Purchase date]) -- → "2023-06-15" (warranty date if available, otherwise purchase date) Coalesce([DNS name], [Asset name], "No name") -- → "server-01.corp.local" ``` --- ### `NullIf` Returns `null` if two values are equal; otherwise returns the first value. Useful for converting sentinel values to null. ``` NullIf(x: T, y: T) → T ``` ``` NullIf([Manufacturer], "") -- → null (if manufacturer is empty string, treat as null) NullIf([Serial number], "N/A") -- → null (convert "N/A" placeholder to null) Coalesce(NullIf([Manufacturer], ""), "Unknown") -- → "Unknown" (convert empty to null, then null to "Unknown") ``` --- ### `Clamp` Constrains a value within a minimum and maximum range. ``` Clamp(value: Number, min: Number, max: Number) → Number ``` ``` Clamp([Number of Vulnerabilities], 0, 100) -- → 100 (caps at 100) Clamp([Disk Drive Usage Percentage], 0, 100) -- → 95.2 (already within range, returned as-is) Clamp([Memory] / 1024, 1, 512) -- → 8 (RAM in GB, clamped between 1 and 512) ``` --- ### `Greatest` Returns the largest value from multiple arguments. Ignores `null` values. ``` Greatest(x1: T, x2: T, ...) → T ``` ``` Greatest([Critical Severity Vulnerabilities], [High Severity Vulnerabilities], [Medium Severity Vulnerabilities]) -- → 12 (the highest count across severity levels) Greatest([Last successful scan], [Last scan attempt], [Last scanned by LsAgent]) -- → "2026-02-15" (most recent activity date) ``` --- ### `Least` Returns the smallest value from multiple arguments. Ignores `null` values. ``` Least(x1: T, x2: T, ...) → T ``` ``` Least([Warranty end date], [End of support], [OS end of support]) -- → "2025-03-01" (earliest expiration across warranty and lifecycle) Least([Critical Severity Vulnerabilities], [High Severity Vulnerabilities]) -- → 2 ``` --- ## Date and time Functions for working with dates. Common date fields in the asset dataset include `[Warranty end date]`, `[Purchase date]`, `[Created at]`, `[Last successful scan]`, `[Last scan attempt]`, `[End of support]`, and `[OS end of support]`. ### Current date and time #### `Now` Returns the current date and time. ``` Now() → Date ``` ``` Now() -- → "2026-02-18 14:30:00" DateDiff("day", [Last successful scan], Now()) -- → 15 (days since last scan) ``` --- #### `Today` Returns the current date without a time component. ``` Today() → Date ``` ``` Today() -- → "2026-02-18" [Warranty end date] < Today() -- → true (warranty already expired) ``` --- #### `Yesterday` Returns yesterday's date. ``` Yesterday() → Date ``` ``` [Last successful scan] = Yesterday() -- → true (asset was scanned yesterday) ``` --- ### Date difference #### `DateDiff` Returns the number of boundaries crossed between two dates in the specified unit. ``` DateDiff(unit: String, start: Date, end: Date) → Number ``` Supported units: `"second"`, `"minute"`, `"hour"`, `"day"`, `"week"`, `"month"`, `"quarter"`, `"year"`. ``` DateDiff("day", [Last successful scan], Now()) -- → 15 (days since last scan) DateDiff("year", [Purchase date], Now()) -- → 4 (asset age in years) DateDiff("month", [Created at], [Last successful scan]) -- → 36 (months between first and last scan) DateDiff("day", Now(), [Warranty end date]) -- → -90 (negative means warranty expired 90 days ago) ``` --- ### Date arithmetic #### `AddDays` Adds a number of days to a date. ``` AddDays(date: Date, n: Number) → Date ``` ``` AddDays([Purchase date], 365) -- → "2023-06-15" (one year after purchase) AddDays(Now(), 90) -- → "2026-05-19" (90 days from now) ``` --- #### `AddMonths` Adds a number of months to a date. ``` AddMonths(date: Date, n: Number) → Date ``` ``` AddMonths([Warranty end date], -6) -- → "2025-08-01" (6 months before warranty expires) AddMonths([Purchase date], 36) -- → "2025-06-15" (3 years after purchase) ``` --- #### `AddYears` Adds a number of years to a date. ``` AddYears(date: Date, n: Number) → Date ``` ``` AddYears([Purchase date], 5) -- → "2027-06-15" (expected 5-year lifecycle end) AddYears([Created at], 3) -- → "2025-01-20" (3 years after first seen) ``` --- #### `AddHours` Adds a number of hours to a date/time. ``` AddHours(date: Date, n: Number) → Date ``` ``` AddHours([Last successful scan], 24) -- → "2026-02-04 10:30:00" (one day after last scan) ``` --- #### `AddWeeks` Adds a number of weeks to a date. ``` AddWeeks(date: Date, n: Number) → Date ``` ``` AddWeeks([Last successful scan], 2) -- → "2026-02-17" (two weeks after last scan) ``` --- #### `SubtractDays` Subtracts a number of days from a date. ``` SubtractDays(date: Date, n: Number) → Date ``` ``` SubtractDays(Now(), 30) -- → "2026-01-19" (30 days ago) [Last successful scan] < SubtractDays(Now(), 30) -- → true (asset hasn't been scanned in over 30 days) ``` --- #### `SubtractMonths` Subtracts a number of months from a date. ``` SubtractMonths(date: Date, n: Number) → Date ``` ``` SubtractMonths(Now(), 6) -- → "2025-08-18" (6 months ago) ``` --- #### `SubtractYears` Subtracts a number of years from a date. ``` SubtractYears(date: Date, n: Number) → Date ``` ``` SubtractYears(Now(), 3) -- → "2023-02-18" (3 years ago) [Purchase date] < SubtractYears(Now(), 5) -- → true (asset was purchased more than 5 years ago) ``` --- ### Date extraction #### `ToYear` Extracts the year from a date. ``` ToYear(date: Date) → Number ``` ``` ToYear([Purchase date]) -- → 2022 ToYear([Warranty end date]) -- → 2026 ``` --- #### `ToMonth` Extracts the month (1–12) from a date. ``` ToMonth(date: Date) → Number ``` ``` ToMonth([Last successful scan]) -- → 2 ToMonth([Warranty end date]) -- → 6 ``` --- #### `ToDay` Extracts the day of the month (1–31) from a date. ``` ToDay(date: Date) → Number ``` ``` ToDay([Purchase date]) -- → 15 ``` --- #### `ToQuarter` Extracts the quarter (1–4) from a date. ``` ToQuarter(date: Date) → Number ``` ``` ToQuarter([Purchase date]) -- → 2 (Q2) ToQuarter([Warranty end date]) -- → 4 (Q4) ``` --- #### `ToDayOfWeek` Returns the day of the week as a number (1 = Monday, 7 = Sunday). ``` ToDayOfWeek(date: Date) → Number ``` ``` ToDayOfWeek([Last successful scan]) -- → 3 (Wednesday) ``` --- #### `ToDayOfYear` Returns the day of the year (1–366). ``` ToDayOfYear(date: Date) → Number ``` ``` ToDayOfYear([Created at]) -- → 42 ``` --- #### `MonthName` Returns the month name as a string. ``` MonthName(date: Date) → String ``` ``` MonthName([Purchase date]) -- → "June" MonthName([Warranty end date]) -- → "December" ``` --- ### Date truncation #### `ToStartOfDay` Truncates a datetime to midnight (start of the day). ``` ToStartOfDay(date: Date) → Date ``` ``` ToStartOfDay([Last successful scan]) -- → "2026-02-03 00:00:00" ``` --- #### `ToStartOfWeek` Truncates a date to the start of the week (Sunday by default). ``` ToStartOfWeek(date: Date) → Date ``` ``` ToStartOfWeek([Last successful scan]) -- → "2026-02-01" ``` --- #### `ToStartOfMonth` Truncates a date to the first day of the month. ``` ToStartOfMonth(date: Date) → Date ``` ``` ToStartOfMonth([Purchase date]) -- → "2022-06-01" ToStartOfMonth(Now()) -- → "2026-02-01" ``` --- #### `ToStartOfQuarter` Truncates a date to the first day of the quarter. ``` ToStartOfQuarter(date: Date) → Date ``` ``` ToStartOfQuarter([Warranty end date]) -- → "2026-10-01" ``` --- #### `ToStartOfYear` Truncates a date to January 1st of that year. ``` ToStartOfYear(date: Date) → Date ``` ``` ToStartOfYear([Purchase date]) -- → "2022-01-01" ``` --- ### Date formatting #### `FormatDateTime` Formats a date as a string using a format pattern. ``` FormatDateTime(date: Date, format: String) → String ``` Common format tokens: | Token | Output | | --- | --- | | `%Y` | Year (4 digits) | | `%m` | Month (01–12) | | `%d` | Day (01–31) | | `%H` | Hour (00–23) | | `%M` | Minute (00–59) | | `%S` | Second (00–59) | ``` FormatDateTime([Purchase date], "%Y-%m-%d") -- → "2022-06-15" FormatDateTime([Last successful scan], "%d/%m/%Y") -- → "03/02/2026" Concat("Purchased in ", FormatDateTime([Purchase date], "%Y")) -- → "Purchased in 2022" ``` --- ### Date construction #### `MakeDate` Creates a date from year, month, and day components. ``` MakeDate(year: Number, month: Number, day: Number) → Date ``` ``` MakeDate(2025, 1, 1) -- → "2025-01-01" [Warranty end date] > MakeDate(2026, 12, 31) -- → false (warranty expires before end of 2026) ``` --- ## Rounding `Round`, `Ceil`, and `Floor` are documented in the [Arithmetic](/docs/derived-fields-function-reference#arithmetic) section. The following are additional rounding functions. ### `Trunc` Truncates a number toward zero to a specified number of decimal places. Unlike `Floor` (which always rounds down), `Trunc` removes decimals without changing sign. ``` Trunc(x: Number, decimals?: Number) → Number ``` ``` Trunc(3.9) -- → 3 Trunc(-3.9) -- → -3 (toward zero, not -4 like Floor) Trunc([Memory] / 1024, 2) -- → 7.99 (truncated to 2 decimals, no rounding) ``` --- ### `RoundDown` Rounds a number down to the nearest value from a given threshold array. Useful for bucketing values into predefined ranges. ``` RoundDown(x: Number, thresholds: Array(Number)) → Number ``` ``` RoundDown([Memory], [1024, 2048, 4096, 8192, 16384, 32768]) -- → 8192 (8GB bucket for an asset with 12GB RAM) RoundDown([Number of Vulnerabilities], [0, 1, 5, 10, 50, 100]) -- → 10 (bucket: 10-49 vulnerabilities) RoundDown([Disk Drive Usage Percentage], [0, 25, 50, 75, 90]) -- → 75 (disk is 75-89% full) ``` --- ## Splitting and merging Functions for splitting strings into arrays or joining them back. Useful for parsing structured text fields like IP addresses, FQDNs, file paths, and registry paths. `ArrayStringConcat` (the reverse: joining an array into a string) is documented in the [Arrays](/docs/derived-fields-function-reference#arrays) section. ### `SplitByChar` Splits a string by a single character delimiter. ``` SplitByChar(separator: String, s: String) → Array(String) ``` ``` SplitByChar(".", [FQDN]) -- → ["server-01", "corp", "local"] SplitByChar(".", [IP address]) -- → ["192", "168", "1", "100"] SplitByChar(",", [Cloud tags]) -- → ["env:production", "team:infra", "cost:shared"] ``` --- ### `SplitByString` Splits a string by a multi-character delimiter. ``` SplitByString(separator: String, s: String) → Array(String) ``` ``` SplitByString(", ", [Cloud tags]) -- → ["env:production", "team:infra", "cost:shared"] SplitByString(" - ", [Description]) -- → ["Web Server", "Production", "US-East"] ``` --- ### `SplitByWhitespace` Splits a string by whitespace (spaces and tabs). ``` SplitByWhitespace(s: String) → Array(String) ``` ``` SplitByWhitespace([OS]) -- → ["Windows", "Server", "2019", "Standard"] SplitByWhitespace([Processor]) -- → ["Intel(R)", "Core(TM)", "i7-10700", "CPU", "@", "2.90GHz"] ``` --- ## String Functions for working with text values. Fields like `[Asset name]`, `[Manufacturer]`, `[Model]`, `[OS]`, `[FQDN]`, `[IP address]`, `[Description]`, and `[Cloud tags]` are all strings. ### Basic #### `Length` Returns the length of a string in characters. > `Length` also works on arrays. See the [Arrays](/docs/derived-fields-function-reference#arrays) section. ``` Length(s: String) → Number ``` ``` Length([Asset name]) -- → 12 Length([Serial number]) -- → 10 ``` --- #### `Concat` Joins multiple values into a single string. ``` Concat(s1: String, s2: String, ...) → String ``` ``` Concat([Manufacturer], " ", [Model]) -- → "Dell OptiPlex 7090" Concat([Asset name], " (", [Type], ")") -- → "SRV-WEB-01 (Windows)" ``` --- #### `ConcatWithSeparator` Joins multiple values with a separator between each. ``` ConcatWithSeparator(separator: String, s1: String, s2: String, ...) → String ``` ``` ConcatWithSeparator(" - ", [Manufacturer], [Model], [Serial number]) -- → "Dell - OptiPlex 7090 - ABC123XYZ" ConcatWithSeparator(", ", [Asset name], [IP address], [OS]) -- → "SRV-WEB-01, 192.168.1.100, Windows Server 2019 Standard" ``` --- ### Case #### `Lower` Converts a string to lowercase. ``` Lower(s: String) → String ``` ``` Lower([Manufacturer]) -- → "dell" Lower([OS]) -- → "windows server 2019 standard" ``` --- #### `Upper` Converts a string to uppercase. ``` Upper(s: String) → String ``` ``` Upper([Asset name]) -- → "SRV-WEB-01" Upper([State]) -- → "ACTIVE" ``` --- #### `Initcap` Capitalizes the first letter of each word. ``` Initcap(s: String) → String ``` ``` Initcap("hello world") -- → "Hello World" Initcap(Lower([Department])) -- → "Information Technology" ``` --- ### Trimming #### `Trim` Removes leading and trailing whitespace from a string. ``` Trim(s: String) → String ``` ``` Trim(" hello ") -- → "hello" Trim([Description]) -- → "Web server for production" ``` --- #### `TrimLeft` Removes leading whitespace from a string. ``` TrimLeft(s: String) → String ``` ``` TrimLeft(" hello ") -- → "hello " ``` --- #### `TrimRight` Removes trailing whitespace from a string. ``` TrimRight(s: String) → String ``` ``` TrimRight(" hello ") -- → " hello" ``` --- ### Substrings #### `Substring` Extracts a portion of a string. Position is 1-based. ``` Substring(s: String, offset: Number, length?: Number) → String ``` ``` Substring([Serial number], 1, 3) -- → "ABC" (first 3 characters) Substring([IP address], 1, Position([IP address], ".") - 1) -- → "192" (first octet) ``` --- #### `Left` Returns the first N characters of a string. ``` Left(s: String, n: Number) → String ``` ``` Left([Serial number], 4) -- → "ABC1" Left([Asset name], 10) -- → "SRV-WEB-01" ``` --- #### `Right` Returns the last N characters of a string. ``` Right(s: String, n: Number) → String ``` ``` Right([IP address], 3) -- → "100" Right([LsAgent version], 5) -- → "3.2.1" ``` --- #### `SubstringIndex` Returns the part of a string before the Nth occurrence of a delimiter. A positive count reads from the left; a negative count reads from the right. ``` SubstringIndex(s: String, delimiter: String, count: Number) → String ``` ``` SubstringIndex([FQDN], ".", 1) -- → "server-01" (hostname: everything before the first dot) SubstringIndex([FQDN], ".", -2) -- → "corp.local" (domain: everything after the first dot) SubstringIndex("user@company.com", "@", 1) -- → "user" SubstringIndex("user@company.com", "@", -1) -- → "company.com" ``` --- #### `Basename` Extracts the filename from a path (everything after the last `/` or `\`). ``` Basename(path: String) → String ``` ``` Basename([File Pathfull]) -- → "chrome.exe" Basename("/var/log/syslog") -- → "syslog" Basename("C:\\Program Files\\App\\config.ini") -- → "config.ini" ``` --- ### Prefix and suffix #### `StartsWith` Returns `true` if a string starts with the specified prefix. ``` StartsWith(s: String, prefix: String) → Bool ``` ``` StartsWith([Asset name], "SRV") -- → true StartsWith([IP address], "192.168") -- → true StartsWith([OS], "Windows") -- → true ``` --- #### `EndsWith` Returns `true` if a string ends with the specified suffix. ``` EndsWith(s: String, suffix: String) → Bool ``` ``` EndsWith([FQDN], ".local") -- → true EndsWith([Asset name], "-DC") -- → true (likely a domain controller) EndsWith([File Pathfull], ".exe") -- → true ``` --- ### Replacing #### `Replace` Replaces all occurrences of a substring with another string. ``` Replace(s: String, search: String, replacement: String) → String ``` ``` Replace([OS], "Microsoft ", "") -- → "Windows Server 2019 Standard" Replace([IP address], ".", "-") -- → "192-168-1-100" Replace([Cloud tags], ",", ", ") -- → "env:production, team:infra" ``` --- #### `ReplaceOne` Replaces only the first occurrence of a substring. ``` ReplaceOne(s: String, search: String, replacement: String) → String ``` ``` ReplaceOne([FQDN], ".", " [dot] ") -- → "server-01 [dot] corp.local" (only the first dot replaced) ``` --- ### Searching #### `Position` Returns the 1-based position of the first occurrence of a substring. Returns `0` if not found. ``` Position(haystack: String, needle: String) → Number ``` ``` Position([OS], "Server") -- → 9 (found at position 9) Position([Asset name], "DC") -- → 0 (not found) Position([FQDN], ".") -- → 10 (position of first dot) ``` --- #### `PositionCaseInsensitive` Like `Position`, but ignores case. ``` PositionCaseInsensitive(haystack: String, needle: String) → Number ``` ``` PositionCaseInsensitive([OS], "windows") -- → 1 PositionCaseInsensitive([Manufacturer], "DELL") -- → 1 ``` --- #### `CountSubstrings` Counts how many times a substring appears in a string. ``` CountSubstrings(haystack: String, needle: String) → Number ``` ``` CountSubstrings([FQDN], ".") -- → 2 (two dots in "server-01.corp.local") CountSubstrings([OS], " ") -- → 3 (three spaces in "Windows Server 2019 Standard") CountSubstrings([Description], "error") -- → 0 ``` --- ### Formatting #### `Format` Formats a string template by replacing `{}` placeholders with arguments in order. ``` Format(template: String, arg1: T, arg2: T, ...) → String ``` ``` Format("{} {} ({})", [Manufacturer], [Model], [Serial number]) -- → "Dell OptiPlex 7090 (ABC123XYZ)" Format("{} has {} vulnerabilities", [Asset name], [Number of Vulnerabilities]) -- → "SRV-WEB-01 has 5 vulnerabilities" Format("RAM: {} GB | Disks: {}", [Memory] / 1024, Length([Disk Drive Size])) -- → "RAM: 8 GB | Disks: 3" ``` --- #### `Extract` Extracts the first match of a regular expression pattern from a string. ``` Extract(s: String, pattern: String) → String ``` ``` Extract([OS], "\\d+") -- → "2019" (first number found in OS name) Extract([LsAgent version], "\\d+\\.\\d+") -- → "3.2" (major.minor version) Extract([IP address], "^\\d+\\.\\d+") -- → "192.168" (first two octets) ``` --- ## Type conversion Functions for converting values between types. Useful when combining fields of different types in string labels, or when parsing string fields that contain numbers or dates. ### `ToString` Converts any value to its string representation. ``` ToString(x: T) → String ``` ``` ToString([Memory]) -- → "8192" ToString([Number of Vulnerabilities]) -- → "5" ToString([Warranty end date]) -- → "2026-06-15" Concat("RAM: ", ToString([Memory] / 1024), " GB") -- → "RAM: 8 GB" ``` --- ### `ToNumber` Converts a string to a number. Returns `0` if conversion fails. ``` ToNumber(s: String) → Number ``` ``` ToNumber("42") -- → 42 ToNumber("3.14") -- → 3.14 ToNumber("abc") -- → 0 (invalid input, returns zero) ToNumber([OS build]) * 1 -- → 19045 (parse build number string as a number) ``` --- ### `ToNumberOrNull` Converts a string to a number. Returns `null` if conversion fails. ``` ToNumberOrNull(s: String) → Number ``` ``` ToNumberOrNull("42") -- → 42 ToNumberOrNull("abc") -- → null Coalesce(ToNumberOrNull([OS build]), 0) -- → 19045 (parse build number, fallback to 0) ``` --- ### `ToDate` Converts a string to a date. Expects `YYYY-MM-DD` format. ``` ToDate(s: String) → Date ``` ``` ToDate("2025-01-15") -- → 2025-01-15 DateDiff("day", ToDate("2025-01-01"), [Warranty end date]) -- → 166 (days between a fixed date and warranty expiration) ``` --- ### `ToDateOrNull` Converts a string to a date. Returns `null` if parsing fails. ``` ToDateOrNull(s: String) → Date ``` ``` ToDateOrNull("2025-01-15") -- → 2025-01-15 ToDateOrNull("not-a-date") -- → null ``` --- ### `ParseDateTimeBestEffort` Parses a date/time string in various common formats without requiring an explicit format pattern. Handles ISO 8601, RFC 1123, and many others. ``` ParseDateTimeBestEffort(s: String) → Date ``` ``` ParseDateTimeBestEffort("2025-01-15") -- → 2025-01-15 00:00:00 ParseDateTimeBestEffort("Jan 15, 2025") -- → 2025-01-15 00:00:00 ParseDateTimeBestEffort("15/01/2025 14:30") -- → 2025-01-15 14:30:00 ParseDateTimeBestEffort("2025-01-15T14:30:00Z") -- → 2025-01-15 14:30:00 ``` --- ### `ToBoolean` Converts a value to a boolean. - Strings: `"true"` and `"1"` convert to `true`; `"false"`, `"0"`, and `""` convert to `false` - Numbers: `0` converts to `false`, anything else to `true` ``` ToBoolean(x: T) → Bool ``` ``` ToBoolean(1) -- → true ToBoolean(0) -- → false ToBoolean("true") -- → true ToBoolean([Number of Vulnerabilities]) -- → true (any non-zero count is true) ``` --- ## Next steps - [Derived fields expression language reference](/docs/derived-fields-expression-language-reference) - [Create and manage derived fields](/docs/create-and-manage-derived-fields) - [About derived fields](/docs/about-derived-fields)