Skip to main content
Matrix42 Self-Service Help Center

ASQL

Querying database with Matrix42 query language ASQL: basic approach and important functions.

Introduction

This document provides an introduction to the Matrix42 query language ASQL and covers the most important functions in everyday use. 

Within the Matrix42 Service Store, ASQL is used for defining: 

  • Column Definitions of Data Queries (see examples outlined in red in the screenshot below):
    columns.JPG
  • Calculated fields (see example outlined in red in the screenshot below): 
    CalculatedFieldASQLExample.png
  • Display expressions in Data Definitions and Configuration Items (see example outlined in red in the screenshot  below): 
    DisplayExpressionExample.JPG
  • Filter criteria in the Dataset Views (see example outlined in red in the screenshot  below): 
    FilterExpressionASQLExample.JPG
  • Structures for the Navigation Items (normally, in structures you don't see ASQL in the UI)
  • Quick Filter 
  • Script Definition Parameter Expressions

We use ASQL expressions in two different contexts:

  1.  To define the attributes we want to query from a class (the base class) and its related classes.
  2. To restrict the result of a query to a subset of the instances (rows) of the base class (e.g. have only incidents with status "Closed" displayed for you).

We call the first a display expression and the second a WHERE clause. These two share in principle the same syntax with four differences:

  • A display expression contains, separated with commas, often more than one expression (for example, ReactionTimeEscalated as ReactionTime, IncidentSolutionTimeEscalated as SolutionTime, [...]. You will not see ASQL in the UI, such ASQL expressions will be created for you in the background), while a where clause always only contains one expression. 
  • The expressions in a display expression must define an alias if they are not direct attributes of the base class. This alias is used to name the columns in the result and must be unique in the result. Have a look at this example: lastname, firstname, T(SPSCommonClassBase).State.DisplayString as state .  In this example, lastname  and firstname are direct attributes of the base class, while T(SPSCommonClassBase).State.DisplayString  is an expression and it's not a direct attribute of the base class, so you must define an alias "state", the name of the column. 
  • The expressions in a display expression must have unique results for each instance (row) of the base class. You cannot display the children of a 1-N relation, because there may be more than one child. 
  • A WHERE clause must always be of type Boolean expression (i.e. a WHERE clause must be true or false). 

You will not see the ASQL code mentioned in the above paragraph in the UI. You are well advised to get your hands on our QueryAnalyzer tool.  

Simple Queries for display expressions 

An ASQL display expression serves to define the attributes we want to query from a class (the base class) and its related classes. It is built up of expressions containing: 

  • Identifiers of data definitions (classes) or attributes (including relation attributes) 
  • Functions 
  • Constants 
  • Operators 

Example:  

The simplest query for display expressions in ASQL is the query of attributes of an object type class base. The object type (configuration item) “Computer“ (SPSComputerType) has the class base  SPSComputerClassBase. 

Valid ASQL queries for e.g. being used for column definitions could be:  

  • ManagementTypeValue of computer attribute ManagementType 
  • ‘KA_’+ NameValue of computer attribute Name leaded by the string ‘KA’ 
  • IsNull(IPAddress, ‘IP not available’)Value of computer attribute IPAddress if not Null, else the string ‘IP not available’ 

If you are already familiar with normal SQL, in most cases you just can imagine to place these ASQL queries between “SELECT” and “from SPSComputerClassBase” and you will get the according SQL query. 

Simple Queries for WHERE clauses 

An ASQL WHERE clause serves to restrict the result of a query to a subset of the instances (rows) of the according base class. It is built up of expressions containing: 

  • Identifiers of data definitions (classes) or attributes (including relation attributes) 
  • Functions 
  • Constants 
  • Operators 

Example:  

The simplest query for WHERE clauses in ASQL is the query of attributes of an object type class base. The object type (configuration item) “Computer“ (SPSComputerType) has the class base  SPSComputerClassBase. 

Valid ASQL queries for e.g. being used for quick filters could be:  

  • ManagementType=0 All Computers where ManagementType = 0 [manually]  
  • Name like ‘KA%‘ All Computers where the name starts with KA  
  • IPAddress IS NOT NULL All computers that have an IP address  

If you are already familiar with normal SQL, in most cases you just can imagine to add “SELECT * from SPSComputerClassBase WHERE” at the beginning of these ASQL queries and you will get the according SQL query. 

Queries across several Class Bases (Navigate relations) 

By default, ASQL queries are always used in a defined context of an object type (configuration item) and one specific class base (data definition). 

The simplest relation navigation mechanism is to access related classes defined in the data model as relations from the base class is to use the relation name. For navigating relations starting from the base class you can use the simple “.”-syntax: 

<RelationName>.<AttributeName>

Examples (context SPSComputerClassBase):  

  • AssignedAccounts.AccountName like ‘XYZ’ All computers which are assigned to the account XYZ 
  • AssignedAccounts.Owner.ID IS NOT NULL All computers which have a person assigned 

But, additionally, an object type (configuration item) may have several class bases (data definitions) with attributes.  

Example:  

The object type SPSComputerType contains the following class bases:  

  • SPSComputerClassBaseClass base for computers  
  • SPSAssetClassBaseClass base for assets  
  • SPSlnventoryClassBaseClass base for inventory data  

To access classes that are declared in an object type but are not the base class in context the T-Operator is used (see the following chapter for details). 

In some special cases, like e.g. on defining Compliance Rule conditions, there is no specific class base in context, but just an object type. In these cases, you can navigate to all contained classes in the type in context using the expression: 

Related<ClassName>.<AttributeName> 

Examples (context SPSComputerType):  

  • RelatedSPSAssetClassBase.lnventoryNumber Inventory Number of the computer 
  • RelatedCommonClassBase.Location.Name Location of the computer 
  • RelatedComputerClassBase.ManagementType=0 All computers where ManagementType = 0 

T-Operator 

You can reach attributes as well as relations for different DDs in CIs with the T-Operator. 

Syntax:  

T(<ClassName>).<AttributeName>  

T(<ClassName>).<RelationName>.<AttributeName>  

Examples (context SPSComputerClassBase):  

  • T(SPSAssetClassBase).lnventoryNumber Inventory number of a computer  

  • T(SPSCommonClassBase).Location.Name Location name of a computer 

In the above examples, lnventoryNumber is an attribute of the asset class base and the assigned Location is the name of the relation from the common class base to the location class base. 

Relations only store the ID. For example, in the case of the principal user relation, the asset class base contains the ID of the user class base. The two classes [asset & user] are fully connected and by using the relation name, all attributes of the related class can be reached. 

You can either simply define the target attribute you want to display or modify the display name by an expression. 

Example:  

Display of the first name of the main user:  

T(SPSAssetClassBase).AssignedUser.FirstName 

Create a display expression:  

T(SPSAssetClassBase).AssignedUser[LastName + ISNULL(‘, ’+ FirstName,’’)]  

If you do not define a target attribute, the system will show by default the display expression defined in the target class. 

Advanced

Refer to this section to learn about advanced ASQL in Matrix42. 

Relation Brackets 

Instead of the point notation to navigate relations, it is possible to change the context completely to the relation target data definition. The syntax element for this is the “[…]” element. Between the brackets, you can use the elements of the related element without the need to provide the full path. Best explained by an example. 

You want to navigate from an account (SPSAccountClassBase) to the owner (relation attribute “owner”), that is the person that owns an account (SPSUserClassBase). And you want the lastname and the firstname of the person, combined by addition separated by a comma. (We assume both values are set).

Point Syntax 

  • owner.LastName + ', ' + owner.FirstName 

Bracket Syntax 

  • Owner[LastName + ', ' + FirstName]

Forcing Inner Join

Relations in ASQL are normally implemented by left joins on the SQL layer. An expression like

owner.LastName = 'Smith'

will produce SQL code that looks similar to this

Select ..... From ... SPSUserClassBase [T-0] Left Join SPSAccountClassBase [T-1] on [T-0].ID=[T-1].Owner .... Where [T-1].LastName='Smith'

ASQL offers an alternative syntax to force the usage of inner joins on the SQL layer.

owner.=LastName = 'Smith'

A similar syntax exists for the relation bracket

Owner[=LastName + ', ' + FirstName]

There are very special cases where forcing inner join is beneficial (see the attention box in the subquery section), but normally you should avoid it. In general, it is not recommended to use it without a good reason as the side-effects might change your results in unexpected ways.

Aliases 

The ASQL processor can inject special aliases into an expression. 

Samples are  

  • the base alias in normal ASQL expressions, used to bind subqueries to the external scope (see subquery). 
  • Inside a subquery the alias for the BaseClass. 
  • The production and staging alias in the GDI matching or relation expressions. 

An alias is used as the starting point for further expressions, in general if an alias is injected the empty scope is no longer available, all expressions must be based on some alias. 

Example: get all persons with more than one account (using subquery and count), the aliases used here are base and A

  • Subquery(SPSAccountClassBase as A, count(all, A.*), A.Owner =base.ID) > 1 

Operators 

  • Arithmetic operators: +, -,*,/ 
  • Binary operators: |, &, ^ 
  • Compare operators: =, <=, =<, =>, >=, <, >, <>, LIKE, IS [NOT] NULL 
  • Logical operators: AND, OR, NOT 
  • Subquery operators: IN, EXISTS

Literal Expressions 

You can provide constants for nearly all valid datatypes in ASQL 

  • String 
    Starts and ends with a ', if you need to escape a ' add a double '' 
    'Smith''s' 
  • Datetime 
    Starts and ends with a # and has always a date part and an optional time part. The format is always #yyyy-MM-dd# or #yyyy-MM-dd hh:mm:ss# 
    #1986-03-31 14:12:56# 
  • Guid 
    Guids are string literals of a special format. 
    'BEED92AB-5BBB-E811-CBB6-106530557A87'  
    Internally the system tries to cast guid literals to uniqueidentifiers, the corresponding data type in sql server. This is done to improve performance. But in case a guid is from an external sourcs and is stored as a string attribute (The SMS ID or the BIOS ID) you will have difficulties with compare operations between strings and uniqueidentifiers. To prevent the automatic cast add an N before the literal Guid. 
    N'BEED92AB-5BBB-E811-CBB6-106530557A87'  
  • Hexadecimal expressions 
    These are used for blob fields; a hex literal always starts with 0x and then an arbitrary number of hex digits. 
    0xafe4ed2389 
  • Numbers 
    Only simple integer or float numbers are possible. The decimal point for float numbers is always a point, independent of the cultural settings. 
    12894 
    3.14159 

Functions 

SUBQUERY Function  

You can use a subquery in different ways in Matrix42. Normally, you can use a subquery in Column Definitions, and in WHERE Conditions. Use subqueries for performance reasons in Matrix42; subqueries often perform faster when used together with NM relations. Aggregate functions normally only work together with subqueries in Column Definitions.

Example from Matrix42 UI:

subqueryExample.jpg

Below is the complete Value from the above screenshot: 

ID in  SUBQUERY([SchemaRelation-SPSSecurityClassRole2SPSUserClassBase] as roles, roles.=members.=T(SPSScRoleClassBase).=AddressedActivities.=ID, roles.MemberOf = CURRENTUSERID())

We use here the inner join syntax without any side-effects inside the subquery as it is independent of the outer scope, there is no base alias inside of the subquery.

Below is an example of how a subquery can be used in Display Expressions. 

Shows the amount of accounts for persons:

SUBQUERY(
   SPSAccountClassBase as account,
   COUNT(all, account.*),
   account.owner=base.ID)

Generally, the SUBQUERY function is used to query related classes or to limit the result set.  

Syntax:  

SUBQUERY (<BaseClass> AS <Alias>, <TargetAttribute>, <Filter>)  

In which:  

  • <BaseClass> BaseClass for the SubQuery  
  • <Alias> The alias will be used for the <TargetAttribute> and <Filter>  
  • <TargetAttribute> Defines the result set for the Subquery  
  • <Filter> Limits the result set  

The Subquery function can also be used as an argument for the in operator or an argument for the exists method.

Example:  

We want a column in the list view for persons (SPSUserType with SPSUserClassBase) to show the number of assets they have assigned. 

=SUBQUERY(SPSAssetClassBase AS ACB, Count(all, ACB.*), ACB.AssignedUser.ID=base.ID) 

When you do counting (or you are using other aggregate functions) it is the best approach to count directly the Class you want to count, in this example SPSAssetClassBase .

You might also use this

SUBQUERY(
   SPSUserClassBase as user,
   COUNT(all, user.AssignedAssets.*),
   user.ID=base.ID)

but this will always count a minimum of 1 (even if there are no assets!) as there is always a user row in the subquery that has the same ID as the base user row. To understand this as someone familiar with SQL, ASQL will translate this to a Left Join, so the user row in the subquery is always part of the result set. You may solve this by using the inner join syntax

user.=AssignedAssets.*

but it is harder to understand and maintain than directly counting the SPSAssetClassBase. What you should always avoid is relation navigation on the base alias like this

ACB.ID=base.AssignedAssets.ID  /*don't do this!*/

as it will introduce an additional join on the outer scope of the subquery and this might change the expected result and may even break your query. Best use the base alias with the ID or directly a  physical existing relation attribute, never a virtual attribute! And don't navigate relations on the base alias, always navigate on the inner alias.

CASE Function  

The CASE function allows you to discriminate between various cases depending on the values displayed.  

Syntax:  

CASE WHEN <attribute> = <value> THEN ‘Condition1 met‘ ... ELSE ‘No condition met‘ END 

In our example we will use the SQL function DateDiff(), which calculates the time difference between an attribute and the current date (getdate()) in the context of SPSContractItemClassBase.  

 =CASE WHEN DateDiff(month, getdate(), ValidUntil) < 0 THEN ‘expired’ ELSE ‘active’ END 

Special Functions 

  • CurrentUserID() 
    CurrentUserID ( ) 
    Returns the ID column of the SPSUserBaseClass for the current logged in user in Console. 
  • InteractiveUserID() 
    InteractiveUserID ( ) 
    Returns the ID column of the SPSUserBaseClass for the current logged in user in portal. 
  • Recursive() 
    RECURSIVE ( relation_identifier ) 
    Provides all object instances of an hierarchical tree structures that are part of a parent or child branch. Can be used only for the following classes (data definitions): SPSOrgUnitClassBase, SPSLocationClassBase, SPSScCategoryClassBase, SPSArticleCategoryClassBase. 
    Examples: 
    • Recursive(OUFor).T(SPSOrgUnitClassBase).Name='XYZ' 
      Context: SPSOrgUnitClassBase. Provides all Organization Units that are part of the parent branch of the Organization Unit XYZ. 
    • T(SPSCommonClassBase).Recursive(OU).
      T(SPSOrgUnitClassBase).Name='XYZ' 

      Context: SPSOrgUnitClassBase. Provides all Organization Units that are part of the child branch of the Organization Unit XYZ. 
    • Recursive(Parent).Name='Service Desk' 
      Context: SPSScCategoryClassBase. Provides all Categories that are part of the parent branch of the Category Service Desk. 
  • Join() 
    JOIN( <TargetClass>, <Predicate>) 
    Defines a custom relational join between 2 data definitions. It can be used like a relation attribute, but the join condition can be defined. In the condition you can use all direct attributes from the left side (the context) with the alias L and all direct attributes from the TargetClass with the alias R. 
    Example: 
    Join(SPSAccountClassBase, R.AccountName like L.LastName  + ’%,%’ + L.FirstName).ID IS NOT NULL 
    Context: SPSUserClassBase. A condition to find all users where an account exists with an account name like <lastname, firstname> 
  • TypeContext() 
    TYPECONTEXT( <TargetCI> ) 
    Defines an alias for a relation to the type class of a datadefinition. This is especially needed if you want to navigate from a commonclass to a type class. You cannot use the UsedInType column as the relation attribute is unspecific of the type. Normally you can use the T operator for this kind of navigation but for special cases like GDI staging classes you need to use this operator (The T operator is not supported for staging schemas) 
    Example: 
    TYPECONTEXT(SPSUserType).RelatedSPSUserClassBase.LastName 
    Context: SPSCommonClassBase. A way to navigate from the SPSCommonClassBase to the person CI and then to one of its memberclasses. 
  • Combine() 
    COMBINE(<BaseClass> AS <Alias>, <TargetExpression>, <Filter>) 
    Provides a list of all concatenated TargetExpressions from the BaseClass that match the filter condition. 
    Example: SUBSTRING(COMBINE(SPSAccountClassBase as A, ', ' + A.AccountName, A.Owner = base.ID), 3, 100000) Context: SPSUserClassBase. Provides a list of all accounts of a user with their names separated by a comma. You must remove the first comma by using a substring function. 

In case you are using COMBINE() at a N:M relation you need to use the 'Right Side of the Definition - Relation Attribute Name' in your <Filter>. For Security Roles relation from SPSUserClassBase to SPSSecurityClassRole this would be 'Members' resulting in following statement: SUBSTRING(COMBINE(SPSSecurityClassRole as SecRole, ', ' + SecRole.Name, SecRole.Members.ID = base.ID), 3, 100000)

Standard SQL functions 

ASQL supports the usage of all important functions provided natively by MS SQL Server’s Transact-SQL syntax. Below you will find the list of supported functions. Please refer to http://msdn.microsoft.com/de-de/library/bb510741.aspx for detailed information about the single functions. 

  • Count() 
    COUNT ( { [ [ ALL | DISTINCT , ] expression ] | * } ) 
    Returns the number of items in a group. COUNT always returns an int data type value. This function can be used only within the SUBQUERY function. 

  • Sum() 
    SUM ( expression ) 
    Returns the sum of all the values in the expression. SUM can be used with numeric columns only. Null values are ignored. This function can be used only within the SUBQUERY function.  

  • Min() 
    MIN ( expression ) 
    Returns the minimum of all the values in the expression. MIN  can be used with numeric and datetime columns only. Null values are ignored. This function can be used only within the SUBQUERY function. 

  • Max() 
    MAX ( expression ) 
    Returns the maximum of all the values in the expression. MAX  can be used with numeric and datetime columns only. Null values are ignored. This function can be used only within the SUBQUERY function. 

  • IsNull() 
    ISNULL ( check_expression , replacement_value ) 
    Replaces NULL with the specified replacement value. 

  • Coalesce() 
    COALESCE ( expression [ ,...n ] ) 
    Returns the first nonnull expression among its arguments. 

  • Reverse() 
    REVERSE ( string_expression ) 
    Returns the reverse of a string value. 

  • Left() 
    LEFT ( character_expression , integer_expression ) 
    Returns the left part of a character string with the specified number of characters. 

  • Right() 
    RIGHT ( character_expression ,integer_expression ) 
    Returns the right part of a character string with the specified number of characters. 

  • LTrim() 
    LTRIM ( character_expression ) 
    Returns a character expression after it removes leading blanks. 

  • RTrim() 
    RTRIM ( character_expression ) 
    Returns a character string after truncating all trailing blanks. 

  • Len() 
    LEN ( string_expression ) 
    Returns the number of characters of the specified string expression, excluding trailing blanks. 

  • SubString() 
    SUBSTRING ( value_expression ,start_expression ,length_expression ) 
    Returns part of a character, binary, text, or image expression. 

  • Replace() 
    REPLACE ( string_expression,string_pattern,string_replacement) 
    Replaces all occurrences of a specified string value with another string value. 

  • PatIndex() 
    PATINDEX ( '%pattern%' , expression ) 
    Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. 

  • GetDate() 
    GETDATE ( ) 
    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

  • DateAdd() 
    DATEADD ( datepart , number , date ) 
    Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. 

  • DateDiff() 
    DATEDIFF ( datepart ,startdate ,enddate ) 
    Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. 

  • DatePart() 
    DATEPART ( datepart , date ) 
    Returns an integer that represents the specified datepart of the specified date. 
    Important: dw(weekday) is not supported

  • NewID() 
    NEWID ( ) 
    Creates a unique value of type uniqueidentifier. 

  • Cast() 
    CAST ( expression, data_type, [date_format] ) 
    Explicitly converts an expression of one data type to another. 
    To cast Date to string, use the "date_format" to specify the date format. Like 'yyyy-MM-dd'

  • Was this article helpful?