How to Add User-Defined AQL Functions in QRadar
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
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
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
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.