Derived fields expression language reference

Lansweeper's expression language is a row-level formula language: each expression evaluates to a single typed value per asset. This reference covers syntax, operators, and control flow.

For a full list of functions, see Derived fields function reference.

Literals

Expressions support the following literal types:

Strings use double quotes only. Single quotes are not supported. The following escape sequences work inside double-quoted strings:

Field references

Reference a dataset field by enclosing its field name in square brackets:

[Asset name]
[Manufacturer]
[Memory]

For nested fields (sub-fields of array structures), use dot notation:

[Disk Drive Size]
[Antivirus display name]
[Registry Value Data]

Field names are case-sensitive and must match the field ID exactly. If a field name uses its display name (containing spaces or special characters), enter it exactly as shown in the Fields panel of the expression editor.

Operators

Operators follow standard SQL precedence. Higher precedence operators bind more tightly.

Use parentheses to override default precedence.

Arithmetic

[Memory] / 1024
[Uptime] / 86400000
[Disk Drive Size] * 1024

String concatenation

Use || to join strings:

[Manufacturer] || " " || [Model]
[Asset name] || " - " || [Type]

Comparison

[Memory] > 8192
[Origin] = "IT"
[Number of Vulnerabilities] != 0
[Warranty end date] < Today()

Pattern matching

LIKE matches case-sensitively. ILIKE matches case-insensitively. Use % as a wildcard for any sequence of characters.

[OS] LIKE "%Windows%"
[OS] ILIKE "%windows%"
[OS] NOT LIKE "%Server%"
[OS] NOT ILIKE "%server%"

Null checks

[Manufacturer] IS NULL
[Warranty end date] IS NOT NULL

Set membership

[Origin] IN ["IT", "OT"]
[Cloud Provider] IN ["Microsoft Azure", "Amazon Web Services"]
[Origin] NOT IN ["CLOUD"]

Logical operators

[Memory] > 8192 AND [Origin] = "IT"
[OS lifecycle stage] = "End of support" OR [Number of Vulnerabilities] > 0
NOT [Scanned by asset radar]

Conditional expressions

If()

If(condition, value_if_true, value_if_false) is the simplest conditional. You can nest If() calls for multi-branch logic.

If([Memory] > 8192, "High", "Low")

If([Memory] > 16384, "High",
  If([Memory] > 8192, "Medium", "Low"))

CASE expression

CASE is more readable than nested If() when you have more than 2 branches.

Searched CASE: each WHEN has its own condition:

CASE
  WHEN [Memory] > 16384 THEN "High"
  WHEN [Memory] > 8192  THEN "Medium"
  WHEN [Memory] > 4096  THEN "Low"
  ELSE "Very low"
END

Simple CASE: compares one value against multiple options:

CASE [Origin]
  WHEN "IT"    THEN "Information Technology"
  WHEN "OT"    THEN "Operational Technology"
  WHEN "CLOUD" THEN "Cloud"
  ELSE "Unknown"
END

ELSE is optional. If no WHEN clause matches and there's no ELSE, the result is null.

CASE can appear anywhere an expression is valid, including inside function arguments:

Concat(
  [Asset name],
  " - ",
  CASE
    WHEN [State] = "Active" AND DateDiff("day", [Last successful scan], Now()) > 30 THEN "Stale"
    WHEN [State] = "Active" THEN "OK"
    ELSE [State]
  END
)

Array operations

Array fields (like [Disk Drive Size]) are handled through functions. There's no direct arithmetic on array fields. Use ArraySum(), ArrayAvg(), or ArrayMax() instead.

-- total disk space
ArraySum([Disk Drive Size]) / 1073741824

-- average disk usage
ArrayAvg([Disk Drive Usage Percentage])

-- count disk drives
Length([Disk Drive Size])

Implicit element reference (_)

Array higher-order functions (like ArrayFilter, ArrayMap, ArrayExists) apply a per-element expression. Use _ to reference the current element in that expression.

ArrayFilter(_ > 1099511627776, [Disk Drive Size])

ArrayMap(_ / 1073741824, [Disk Drive Size])

ArrayExists(_ > 90, [Disk Drive Usage Percentage])

ArrayCount(_ ILIKE "%chrome%", [Registry Value Name])

ArrayAll(_ = true, [Antivirus product up to date])

_ is only valid as the first argument of array higher-order functions. Using it elsewhere causes a parse error.

Comments

Use -- to add single-line comments. Everything from -- to the end of that line is ignored.

-- days remaining on warranty
DateDiff("day", Now(), [Warranty end date])

[Memory] / 1024  -- convert MB to GB

If(
  [OS lifecycle stage] = "End of support",  -- check lifecycle stage
  "EOL",
  "Supported"
)

Whitespace

Whitespace (spaces, tabs, and newlines) between tokens is ignored. Expressions can span multiple lines for readability.

If(
  [Number of Vulnerabilities] > 0
    AND [OS lifecycle stage] = "End of support",
  "Critical",
  "OK"
)

Keywords and case sensitivity

Keywords (AND, OR, NOT, LIKE, ILIKE, IN, IS, NULL, CASE, WHEN, THEN, ELSE, END, true, false, null) are case-insensitive. By convention, SQL keywords are written in all-uppercase.

Function names are also case-insensitive. The preferred form uses an initial uppercase letter (DateDiff, ArraySum). All-lowercase and all-uppercase forms are accepted but not recommended.

Expression examples

Simple calculations

-- RAM in GB
[Memory] / 1024

-- days since last scan
DateDiff("day", [Last successful scan], Now())

-- full label
[Manufacturer] || " " || [Model]

Conditional classification

-- classify by RAM
CASE
  WHEN [Memory] > 16384 THEN "High"
  WHEN [Memory] > 8192  THEN "Medium"
  ELSE "Low"
END

-- OS platform
CASE
  WHEN [OS] ILIKE "%windows%" THEN "Windows"
  WHEN [OS] ILIKE "%linux%"   THEN "Linux"
  WHEN [OS] ILIKE "%mac%"     THEN "macOS"
  ELSE "Other"
END

-- warranty status
If([Warranty end date] < Now(), "Expired", "Active")

Array operations

-- any disk over 90% full?
ArrayExists(_ > 90, [Disk Drive Usage Percentage])

-- all antivirus up to date?
ArrayAll(_ = true, [Antivirus product up to date])

-- comma-separated list of antivirus names
ArrayStringConcat([Antivirus display name], ", ")

Combined logic

-- asset health status
If(
  [Number of Vulnerabilities] > 10
    OR [OS lifecycle stage] = "End of support"
    OR ArrayExists(_ > 95, [Disk Drive Usage Percentage]),
  "Critical",
  If(
    [Number of Vulnerabilities] > 0
      OR DateDiff("day", [Last successful scan], Now()) > 30,
    "Warning",
    "Healthy"
  )
)

Next steps

• Derived fields function reference
• Create and manage derived fields