IBM Security QRadar

Reference Tables

Reference Tables allow users to get additional information from external data through the REFERENCETABLE function. Analysts can gather contextual information through an inline AQL Advanced Search query or as an AQL based Custom Event Property using this function. However, properly formatting your custom data to POST using the /reference_data/tables/bulk_load/{name} API request or manually uploading via the Import from File in the Reference Data Management application can be a bit tricky.

Fortunately, a bit of R scripting simplifies this process, allowing administrators to quickly import large external data sources, such as your users in your Active Directory environment or tables for Request for Comment (RFC) documents. In a few lines of R code, we will import, format, and export our external data to QRadar.

Reference Table Format

Reference Tables are structured with an Outer Key Label and one or more Inner Keys. From the REFERENCETABLE documentation, we are asking QRadar to look into the user_data reference table (first parameter) and extract the values from the FullName, Location, and Manager inner keys (second parameters) where the reference table’s outer key matches the username event property. The function joins the reference table on its outer key label (third parameter) and returns whatever inner value you specify.

SELECT
  REFERENCETABLE('user_data','FullName',username) AS 'Full Name',
  REFERENCETABLE('user_data','Location',username) AS 'Location',
  REFERENCETABLE('user_data','Manager',username) AS 'Manager',
  UNIQUECOUNT(username) AS 'Userid Count',
  UNIQUECOUNT(sourceip) AS 'Source IP Count',
  COUNT(*) AS 'Event Count'
FROM events
WHERE qidname(qid)ILIKE '%logon%'
GROUP BY "Full Name", "Location", "Manager"
LAST 1 days

Build an Example

Let’s import a table of 4 users as a demonstration.

library(httr)
library(tidyverse)
library(jsonlite)
library(glue)
library(keyring)
demo <- tibble(
  sAMAccountName = c('tturmeric','jjuniper','ssalt','ppepper'),
  givenname = c('Thomas', 'Josie', 'Sally', 'Pat'),
  surname = c('Turmeric', 'Juniper', 'Salt', 'Pepper'),
  department = c('IT', 'HR', 'IT', 'Engineering'),
  title = c('Developer', 'Payroll', 'Developer', 'Engineer')
)
demo
## # A tibble: 4 × 5
##   sAMAccountName givenname surname  department  title    
##   <chr>          <chr>     <chr>    <chr>       <chr>    
## 1 tturmeric      Thomas    Turmeric IT          Developer
## 2 jjuniper       Josie     Juniper  HR          Payroll  
## 3 ssalt          Sally     Salt     IT          Developer
## 4 ppepper        Pat       Pepper   Engineering Engineer

In this case, we are manually creating the user table in our R script, however, you can easily query data from an Active Directory environment with the Get-ADUser and Export-CSV PowerShell Cmdlets and then import that exported csv file into your R script.

#PowerShell
$params = @{
    LDAPFilter ='(&(SamAccountName=*)(SamAccountType=805306368)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))' 
    Properties = 'department', 'description', 'displayName', 'distinguishedName', 'EmailAddress', 'manager', 'sAMAccountName', 'title', 'Enabled'
}
$ADusers = Get-ADUser @params | Select-Object 'department', 'description', 'displayName', 'distinguishedName', 'EmailAddress', 'manager', 'sAMAccountName', 'title', @{Name='MailNickName';Expression={$_.SamAccountName}}

$ADusers | Export-Csv -Path 'C:\data\AD-users.csv'

#R Code
data <- read.csv(file = 'C:\data\AD-users.csv')

POST data with QRadar API

In order to POST this data using the /reference_data/tables/bulk_load/{name} API, we need to format the payload Body to this data structure:

{"key1":{"col1":"Data11","col2":"Data12","col3":"Data13","col4":"Data14"},
 "key2":{"col1":"Data21","col2":"Data22","col3":"Data23","col4":"Data24"},
 "key3":{"col1":"Data31","col2":"Data32","col3":"Data33","col4":"Data34"},
 "key4":{"col1":"Data41","col2":"Data42","col3":"Data43","col4":"Data44"}}

The key represents the reference table’s outer or primary key; the col is each key’s one or more inner key; and Data is actual contextual data we would like to potentially query or use in a Custom AQL Event Property.

A few lines of R will help elucidate what the payload Body should look like in a more readable format. We first need to use the split function to “divide the data in x into the groups defined by f,” per its documentation. Our function is simply a vector of our demo sAMAccountName objects. We then pipe it to the jsonlite toJSON function with the pretty parameter set to TRUE for readability’s sake. Finally, we remove the brackets ([ and ]) using gsub.  The sAMAccountName serves as our outer key, with givenname, surname, department, and title as the inner keys.

split(x = demo[,-c(1)], f = demo$sAMAccountName) %>% 
  jsonlite::toJSON(pretty=TRUE) %>% 
  gsub(pattern = '\\[|\\]' , replacement = '') 

## {
##   "jjuniper": 
##     {
##       "givenname": "Josie",
##       "surname": "Juniper",
##       "department": "HR",
##       "title": "Payroll"
##     }
##   ,
##   "ppepper": 
##     {
##       "givenname": "Pat",
##       "surname": "Pepper",
##       "department": "Engineering",
##       "title": "Engineer"
##     }
##   ,
##   "ssalt": 
##     {
##       "givenname": "Sally",
##       "surname": "Salt",
##       "department": "IT",
##       "title": "Developer"
##     }
##   ,
##   "tturmeric": 
##     {
##       "givenname": "Thomas",
##       "surname": "Turmeric",
##       "department": "IT",
##       "title": "Developer"
##     }
##   
## }

Now we are set to POST our payload to QRadar’s Reference Table method. For this block of code, we will set the pretty parameter to FALSE so it is properly formatted for its pending POST to QRadar.

demoBody <- split(x = demo[,-c(1)], f = demo$sAMAccountName) %>% 
  jsonlite::toJSON(pretty=FALSE) %>% 
  gsub(pattern = '\\[|\\]' , replacement = '') 

demoBody
## {"jjuniper":{"givenname":"Josie","surname":"Juniper","department":"HR","title":"Payroll"},"ppepper":{"givenname":"Pat","surname":"Pepper","department":"Engineering","title":"Engineer"},"ssalt":{"givenname":"Sally","surname":"Salt","department":"IT","title":"Developer"},"tturmeric":{"givenname":"Thomas","surname":"Turmeric","department":"IT","title":"Developer"}}

Using the httr and glue packages, as well as the keyring package to manage our API credentials, we are ready to create a new Reference table (if it does not exist) and then POST our payload to it.

Create New Reference Table

Here we are creating a new reference table with a name of “demo” and its element_type of “ALN” (alphanumeric). Fortunately, our second API call will take care of the key_name_types and the other query parameters in the POST - /reference_data/tables method.

Sys.setenv('QRadar_API' = key_get(service = 'QRadar-API') )

baseURI <- 'https://qradar.demo.com/api'
referenceTableURI <- 'reference_data/tables'
elementType =  'ALN'
referenceTableName <- 'demo'

VERB(
  verb = 'POST',
  url = glue("{baseURI}/{referenceTableURI}?element_type={elementType}&name={referenceTableName}"),
  config = add_headers(
    'SEC' = Sys.getenv('QRadar_API'),
    'Accept' = 'application/json',
    'Content-Type' = 'application/json',
    'Version' = '18.0')
)

A status code of 201 indicates the successful creation of our demo reference table.

Add Data to Reference Table

Now, we can populate this table’s data with a POST request, using the demoBody as our payload.

Sys.setenv('QRadar_API' = key_get(service = 'QRadar-API') )

baseURI <- 'https://qradar.demo.com/api'
referenceTableURI <- 'reference_data/tables/bulk_load'
referenceTable <- 'demo'

VERB(
  verb = 'POST',
  url = glue("{baseURI}/{referenceTableURI}/{referenceTable}"),
  config = add_headers(
    'SEC' = Sys.getenv('QRadar_API'),
    'Accept' = 'application/json',
    'Content-Type' = 'application/json',
    'Version' = '18.0'),
  body = demoBody
)
## Response [https://qradar.demo.com/api/reference_data/tables/bulk_load/demo]
##   Date: 2024-02-08 23:22
##   Status: 200
##   Content-Type: application/json;charset=UTF-8
##   Size: 115 B

A status code of 200 indicates that data was successfully added or updated in the reference table. We can visually verify with the Reference Data Management application to see our new Reference Table.

Import from File

Another method is to upload a CSV file to our reference table with the Import from File button on the Reference Data Management application. Per the Add Entry Key-Value Separator information tip, we need to format our CSV as:

Parent Key, Inner Key, Value\n...

We can use the pivot_longer function from the tidyr to help format our data properly. Then we can export this data as a CSV file to upload to Reference Data Management application to update our reference table.

s <- lubridate::stamp("23-MAR-2023-1413", orders = "%d-$b-%Y-%H%M")
## Using: "%d-%b-%Y-%H%M"
demo_longer <- demo %>% 
  pivot_longer(cols = -sAMAccountName, names_to = "key")

demo_longer
## # A tibble: 16 × 3
##    sAMAccountName key        value      
##    <chr>          <chr>      <chr>      
##  1 tturmeric      givenname  Thomas     
##  2 tturmeric      surname    Turmeric   
##  3 tturmeric      department IT         
##  4 tturmeric      title      Developer  
##  5 jjuniper       givenname  Josie      
##  6 jjuniper       surname    Juniper    
##  7 jjuniper       department HR         
##  8 jjuniper       title      Payroll    
##  9 ssalt          givenname  Sally      
## 10 ssalt          surname    Salt       
## 11 ssalt          department IT         
## 12 ssalt          title      Developer  
## 13 ppepper        givenname  Pat        
## 14 ppepper        surname    Pepper     
## 15 ppepper        department Engineering
## 16 ppepper        title      Engineer

write_csv(x = demo_longer, file = glue(".\\export\\{s(now())} demo.csv"))

Using the REFERENCETABLE function

Now that our reference table is created and populated, we can use the REFERENCETABLE function in our AQL advanced searches to provide additional information.

I am hard coding 'jjuniper' since I don’t have that username in my actual environment but, just like the documentation above, I am asking for this user’s title and department which are both inner keys in our demo reference table.  We get back Payroll and HR respectively.

SELECT 
'jjuniper' as "User",
REFERENCETABLE('demo', 'title', 'jjuniper') as "Title",
REFERENCETABLE('demo', 'department', 'jjuniper') as "Department"
FROM events
LIMIT 1

Another benefit of this function is that it allows us to create an AQL Based Custom Event Property in our Admin page. Enabling this custom property to be used in Rules allows us to create Building Blocks and Rules based on the values in this reference table. For example, you can safe-list certain events if the Department is equal to ‘IT.’

Conclusion

This was a short demonstration on the benefits of scripting the creation, parsing, and uploading data to QRadar’s Reference Tables for additional contextualization of our event properties.  We could have accomplished this with any other scripting language such as PowerShell or Python, but I found R, particularly its Tidyverse collection of libraries quite effective in this task.  It has numerous functions that allow us to succinctly import, clean, and export our data in just a few lines of code.