QRadar uses Ariel Query Language (AQL), a structured query language that can be used to manipulate event and flow data from the Ariel database.

To retrieve events in QRadar, for example, you can execute the following simple AQL query in the QRadar log activity tab: Select * from events. In addition, user-defined functions are very useful when a custom function is required to perform complex calculations or specific formatting before the data is available for a report.

Importing User-Defined AQL Functions in QRadar

AQL features several built-in functions to perform calculations and formatting on data, such as MIN, MAX, AVG, SUM, REPLACEALL, SUBSTRING, LOWER, UPPER, CONCAT and more. These functions can be called within your AQL query statement. The following query, for example, will return the usernames in lowercase text:

SELECT LOWER(username) from events

For more complex endeavors, however, it can be beneficial to import user-defined AQL functions in QRadar and use them in the query statements. You can call it like this:

User/Custom Defined Functions

Stored/Local Procedures

These functions are very useful when you need to perform complex calculations or are formatting data retrieved from the Ariel databases. They can be written using JavaScript.

Below are two simple examples to show you how to import user-defined AQL functions in QRadar and how to use them within AQL queries.

Example One: A Simple Function

Let’s say I want a function to add two numbers — just a simple function. Below is the function written in JavaScript. We have to import it intro QRadar using the following XML structure:

Save the above XML as CustomFunctionAdd.xml and compress it as a ZIP file, like so: RegExTextExtractor.zip.

To add any custom AQL functions to QRadar, you can use the Extensions Management tool from the admin tab in QRadar or the Content Management Tool (CMT). From the admin tab, open the Extensions Management tool, click “add button” in the top right corner and choose the ZIP file as follows. Don’t forget to check “Install immediately.”

After that, the function will be imported into QRadar to be used with our AQL queries. We can call the function like this:

select MYCUSTOM::ADDNUMBERS(2,8,90,1) As “Add operation result” from events limit 1

Example Two: Extracting Text That Matches a RegEx

Let’s say I need a function to extract the text that matches a regular expression (RegEx) from the event payload. Below is the XML structure and the JavaScript function to extract the text with the capture group number:

To add the above function, as with the previous example, save the XML structure as RegExTextExtractor.xml, then compress it as RegExTextExtractor.zip. After that, you can import it into QRadar using the Extensions Management tool in the admin tab.

Example Three: Putting It All Together

The following is an AQL example that shows how to use the above user-defined function to extract a text that matches a RegEx. Let’s say, for example, I have the following string in an event payload: “Without data you’re just another person with an opinion 437199.” I want to extract the numbers at the end and return them using AQL query:

Select MYCUSTOM::REGEXTEXTEXTRACTOR(‘Without data youre just another person with an opinion 437199’, ‘[a-zA-Z ]+(\d*)’, ‘1’) As “ExtractedNumber” from events limit 1

To learn more, check out this video that explains how to add and use custom AQL functions, prepared by our security architect, Jose Bravo.

Special thanks to Colin Hay.