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.